For one of our projects we store large amounts of timeseries data in an Oracle database. Sometimes we want to get an overview of how big the tables and related indexes are. Some database client tools like Toad for Oracle can show this information directly in their user interface, but if you use other tools like the SQuirreL SQL Client or JetBrains DataGrip you have to gather this information yourself via SQL queries.
DBA_SEGMENTS and DBA_INDEXES
For Oracle databases this meta information is available via the DBA_SEGMENTS and DBA_INDEXES tables. To query the sizes of several tables in MB use the following query:
SELECT segment_name, segment_type, bytes/1024/1024 MB FROM dba_segments WHERE segment_type = 'TABLE' AND segment_name IN ('TABLE_NAME_1', 'TABLE_NAME_2');
This SQL query returns the sizes of TABLE_NAME_1 and TABLE_NAME_2.
If you want to see the sizes of all the indexes that are associated with a table or a set of tables you can use the following query:
SELECT idx.table_name, idx.index_name, SUM(bytes)/1024/1024 MB FROM dba_segments seg, dba_indexes idx WHERE idx.table_owner = 'SCHEMA_NAME' AND idx.table_name IN ('TABLE_NAME_1', 'TABLE_NAME_2') AND idx.owner = seg.owner AND idx.index_name = seg.segment_name GROUP BY idx.index_name, idx.table_name;
Of course, you have to replace SCHEMA_NAME, and TABLE_NAME_x with the names in your database.
Unfortunately, access to this kind of meta information is different for each database system, and the queries above only work for Oracle databases.
One thought on “Determining the sizes of Oracle database tables and indexes”
The GROUP BY in index query does nothing unless you remove the INDEX_NAME from the query and group by, then you’d get sum of bytes for all indexes associated to a table.