snippetModerate
How can I check which database objects are using certain tablespace in Oracle DB?
Viewed 0 times
oraclecantablespaceobjectsarecertaindatabaseusinghowwhich
Problem
How can I check which database objects are using certain tablespace in Oracle DB? Any query or somehow through SQL Developer GUI?
Solution
I'm using the following SQL quite often:
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 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;Code Snippets
SELECT * FROM dba_segments WHERE TABLESPACE_NAME='USERS' ORDER BY bytes DESC;SELECT * FROM v$tempseg_usage;Context
StackExchange Database Administrators Q#52485, answer score: 11
Revisions (0)
No revisions yet.