Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer 1

up vote 5 down vote accepted

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>
share|improve this answer
    
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
    
Thanks very much :) –  Phil_Charly Dec 13 '13 at 14:22
    
@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
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
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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