Welcome to our guide on finding the top 10 largest tables in Oracle. You’ll find valuable insights here whether you’re a beginner or an expert. Let’s open VirtualBox and get started!
To find the top 10 largest tables in an Oracle database while excluding the system-maintained users, you can query the following:
Table of Contents
1. CHECK THE SIZE IN GB:
column OWNER format A25
column TABLE_NAME format A20
COLUMN SIZE_GB FORMAT 99999999.00
SELECT a.OWNER,a.SEGMENT_NAME AS TABLE_NAME,SUM(a.BYTES)/1024/1024/1024 AS SIZE_GB FROM DBA_SEGMENTS a JOIN DBA_USERS b ON a.OWNER = b.USERNAME
WHERE a.SEGMENT_TYPE='TABLE' AND b.ORACLE_MAINTAINED='N' GROUP BY a.OWNER,a.SEGMENT_NAME ORDER BY SIZE_GB DESC FETCH FIRST 10 ROWS ONLY;
2. CHECK THE SIZE IN MB:
column OWNER format A25
column TABLE_NAME format A20
COLUMN SIZE_MB FORMAT 99999999.00
SELECT a.OWNER,a.SEGMENT_NAME AS TABLE_NAME,SUM(a.BYTES)/1024/1024 AS SIZE_MB FROM DBA_SEGMENTS a JOIN DBA_USERS b ON a.OWNER = b.USERNAME
WHERE a.SEGMENT_TYPE='TABLE' AND b.ORACLE_MAINTAINED='N' GROUP BY a.OWNER,a.SEGMENT_NAME ORDER BY SIZE_MB DESC FETCH FIRST 10 ROWS ONLY;
If you want to exclude any particular users then you can run the following:
column owner format A25
column segment_name format A20
COLUMN SIZE_GB FORMAT 99999999.00
select * from (select owner,segment_name, bytes/1024/1024/1024 SIZE_GB from dba_segments where segment_type = 'TABLE' and owner not in('SYS','SYSTEM','MDSYS','DBSNMP')
order by bytes/1024/1024/1024 desc) where rownum <= 10;
Here you can mention the users/schemas within the “owner not in” section that you do not want to include while finding top 10 tables in Oracle.
I hope you found this article helpful. Feel free to reach out if you have any questions.
