How to check which database objects are using certain tablespace in Oracle DB

oracle

How can I check which database objects are using certain tablespace in Oracle DB? Any query or somehow through SQL Developer GUI?

Best Answer

I'm using the following SQL quite often:

SELECT * FROM dba_segments WHERE TABLESPACE_NAME='USERS' ORDER BY bytes DESC;

It will find all the objects which consume some space in given tablespace and sorts the output by used space.

TEMP objects are always related to user session. So you need to query another view:

SELECT * FROM v$tempseg_usage;