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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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;

------------------ ---------- --------------
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.

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
@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 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.