10

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

12

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;
|improve this answer|||||
  • Some additional question about TEMP tablespace. Does TEMP tablespace somehow different from all the others, your query doesn't show anything for TEMP tablespace - does it mean that there is no TEMP tablespace in my DB or it is required to have special permissions/another query to check TEMP tablespace? – Mikhail Oct 30 '13 at 12:48
  • @Mikhail: you can't "store" data in temporary tablespaces. Oracle global temporary tables don't have permanent storage, they won't show up in dba_segments AFAIK. – Mat Oct 30 '13 at 13:13
  • @Mat So there is no way to check which objects use/"overuse" this tablespace? – Mikhail Oct 30 '13 at 13:18
  • 1
    Updated answer about TEMP. – Mindaugas Riauba Oct 30 '13 at 13:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.