Search code examples
oracle-databasedatabase-administrationtablespace

How to find users in a particular tablespace in oracle 11g?


I have created a new tablespace and also some users. I have assigned those users to the tablespace. What is the query for finding out the users for the tablespace?


Solution

  •  SELECT TABLESPACE_NAME,USERNAME 
     FROM  DBA_TS_QUOTAS 
     WHERE TABLESPACE_NAME = '<your_tablespace>';
    

    If Tablespace quotas are not available, then:

    select USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE
    from DBA_USERS
    where DEFAULT_TABLESPACE='<your_tablespace>';