Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 238 Big Data Appliance
- 1.9K Data Science
- 450.2K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 436 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Table size plus Index Size Combined

We have this DW test environment, one request is to list all table sizes for specific schemas. The table size in the report should already include the size of all it's index. We only need to report tables having 1Million rows or more based on num_rows estimate. (These tables will then be reviewed for size usage and are candidate for rows purging).
I have made two separate queries, one for table size and one for index sizes.
--Table Size select seg.owner, seg.segment_name, seg.bytes/1024/1024 as table_size_mb, tab.num_rows from dba_segments seg join dba_tables tab on (seg.segment_name = tab.table_name) where seg.owner in ( 'JAMES', 'JOHN', 'MARIA' ) and NVL(tab.NUM_ROWS,0) > 1000000;
---Index Size SELECT seg.owner, idx.table_name, idx.index_name, SUM(seg.bytes)/1024/1024 as index_size_mb FROM dba_segments seg, dba_indexes idx WHERE idx.table_owner in ( 'JAMES', 'JOHN', 'MARIA' ) AND idx.table_name IN ( select table_name from dba_tables where nvl(num_rows,0) > 1000000 ) AND idx.owner = seg.owner AND idx.index_name = seg.segment_name GROUP BY seg.owner, idx.index_name, idx.table_name;
I cannot figure out a way of combining the query results so that I can have a report that is something like this:
TABLE_OWNER TABLE_NAME SIZE_MB NUM_ROWS James Customer_Tb1 120 2000000 James Customer_Tb2 100 1100000 James Customer_Tb3 300 3200000 John Orders_Tb1 545 1300000 John Orders_Tb2 900 1500000 Maria Recept_Tb1 750 6700000
SIZE_ MB is the table size plus the size of all its indexes
NUM_ROWS is the row estimate of the table that should be 1M or more in order to be qualified in the report
Best Answer
-
There's a thread on this topic at https://community.oracle.com/tech/developers/discussion/4299814/find-largest-table-including-indexes-and-lobs but I wrote a note based on the requirement explaining the steps that you might need to get to the right answer here: https://jonathanlewis.wordpress.com/2019/11/06/table-space/
The answer may be more complex than you need, but there may be components (IOTs, LOBs, nested tables) that you have in your system that you've overlooked.
For a very straightforward case, if you've produced statement for the tables and indexes, and you've included the table name in the query for indexes, you can align the two sets of results (by adding a NULL column to the query for tables and labelling it index_name) and doing a UNION ALL.
Regards
Jonathan Lewis
Answers
-
Better use sys.dba_objects instead. There you have object_type.
Something like:
select sum(sg.bytes), sum(sg.blocks),ob.owner, ob.object_name, ob.object_type from sys.dba_objects ob left join sys.dba_segments sg on ob.owner=sg.owner and ob.object_name=sg.segment_name and ob.object_type=sg.segment_type where object_type in ('TABLE','INDEX') and ob.owner not in ('SYS','SYSTEM','XDB') group by ob.owner, ob.object_name, ob.object_type ;
Still, until now, whenever I have looked for the space used I've queried only sys.dba_sagments or all_segments, without joining it with anything else, seeing that sys_dba_segments tells about the object type in the segment_type column. For tables you have segment_type like 'TABLE%', while for indexes you have segment_type like 'INDEX%'.
-
There's a thread on this topic at https://community.oracle.com/tech/developers/discussion/4299814/find-largest-table-including-indexes-and-lobs but I wrote a note based on the requirement explaining the steps that you might need to get to the right answer here: https://jonathanlewis.wordpress.com/2019/11/06/table-space/
The answer may be more complex than you need, but there may be components (IOTs, LOBs, nested tables) that you have in your system that you've overlooked.
For a very straightforward case, if you've produced statement for the tables and indexes, and you've included the table name in the query for indexes, you can align the two sets of results (by adding a NULL column to the query for tables and labelling it index_name) and doing a UNION ALL.
Regards
Jonathan Lewis
-
Thank you, the link is very helpful.
I was able to use the reference code to come up with a better solution.
-
Thanks for the acknowledgement, glad it helped.
Regards
Jonathan Lewis