19/04/2014

[Oracle] Get DB tables size in MB

To have an idea of how much space your Oracle DB tables occupy, you may run this query on ALL_TABLES:

SELECT owner, table_name, num_rows, ROUND((num_rows*avg_row_len)/(1024*1024)) MB 
FROM all_tables;

Remember that it is accurate only if you gathered statistics before:

DBMS_STATS.GATHER_SCHEMA_STATS('your_schema_name');

Usually you could have the grants to query it but a more precise query, would be to query DBA_SEGMENTS, which would require you to have additional grants:

SELECT segment_name, segment_type, ROUND(bytes/1024/1024) MB
FROM dba_segments
WHERE segment_type='TABLE'
AND segment_name='your_table_name';

and will include also the data currently in the bin. If you want to ignore it, query DBA_EXTENTS instead


No comments:

Post a Comment

With great power comes great responsibility.

Da grandi poteri derivano grandi responsabilità.