Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Table size plus Index Size Combined

The_Cute_DBAMay 26 2022 — edited May 26 2022

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

This post has been answered by Jonathan Lewis on May 26 2022
Jump to Answer

Comments

Processing

Post Details

Added on May 26 2022
4 comments
3,069 views