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

Somadder Abhijit picture Somadder Abhijit · Jul 23, 2017 · Viewed 16.5k times · Source

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?

Answer

Kaushik Nayak picture Kaushik Nayak · Jul 23, 2017
 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>';