3

How can I write a query using Oracle SQL Developer to find how many blocks my database is using? (tables & indexes)

5

This is what you're looking for, if I've read your question correctly:

SQL> select segment_type, sum(bytes)/1024/1024 as size_in_mb, sum(blocks) as size_in_blocks
  2  from dba_extents
  3  group by segment_type;

SEGMENT_TYPE       SIZE_IN_MB SIZE_IN_BLOCKS
------------------ ---------- --------------
LOBINDEX              43.4375           5560
INDEX PARTITION         61.75           7904
TABLE SUBPARTITION          2            256
TABLE PARTITION       70.6875           9048
NESTED TABLE           1.8125            232
ROLLBACK                .4375             56
LOB PARTITION         21.1875           2712
LOBSEGMENT              331.5          42432
INDEX                338.8125          43368
TABLE                   713.5          91328
CLUSTER                 60.25           7712
TYPE2 UNDO            14.3125           1832

12 rows selected.

SQL>
  • That's what I was looking for ,thx, but my sql-developer says that dba_extents doesn't exist,what I have to do? – Phil_Charly Dec 13 '13 at 14:10
  • Use USER_EXTENTS or ALL_EXTENTS instead – Philᵀᴹ Dec 13 '13 at 14:19
  • @Phil sorry one more question if you might, how can I see the MB&blocks of a specific table?deeply appreciated – Phil_Charly Dec 13 '13 at 14:39
  • where segment_name = 'YOURTABLENAME' – Philᵀᴹ Dec 13 '13 at 14:40
  • 1
    @Phil maybe add a note that user_extents only shows data related to the "active schema user", not the entire database ;) – Izzy Dec 13 '13 at 19:15

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.