
14.3 Capacity Planning 419
Chapter 14
SELECT TABLE_NAME
,BLOCKS*8192 AS BYTES
,ROUND(BLOCKS*8192/1024/1024) AS MB
,ROUND(BLOCKS*8192/1024/1024/1024,1) AS GB
FROM USER_TABLES;
This query shows sizes for all indexes in a schema for each table:
SELECT TABLE_NAME
,SUM((LEAF_BLOCKS
+DECODE(BLEVEL,0,1,1,1,LEAF_BLOCKS/BLEVEL))*8192) AS BYTES
,ROUND(SUM((LEAF_BLOCKS
+DECODE(BLEVEL,0,1,1,1,LEAF_BLOCKS/BLEVEL))*8192)/1024/1024)
AS MB
,ROUND(SUM((LEAF_BLOCKS
+DECODE(BLEVEL,0,1,1,1,LEAF_BLOCKS/BLEVEL))*8192)
/1024/1024/1024,1) AS GB
FROM USER_INDEXES GROUP BY TABLE_NAME;
Note: Obviously, the table and index queries could be joined.
14.3.5 Exact Column Data Lengths
This method ...