Check the top big-size table/index/segment in Oracle

Check the larger segment in Oracle

The following query will return the top 10 largest tables in the Oracle database

SET LINESIZE 200
COLUMN owner FORMAT A10
COLUMN segment_name FORMAT A30
COLUMN tablespace_name FORMAT A15
COLUMN size_mb FORMAT 99999.00

SELECT *
FROM   (SELECT owner,
               segment_name,
               segment_type,
               tablespace_name,
               ROUND(bytes/1024/1024,2) size_mb
        FROM   dba_segments where owner not in ('SYS','SYSTEM') and segment_type = 'TABLE'
        ORDER BY 5 DESC)
WHERE  ROWNUM <= 10;

Check the top size indexes in the Oracle Database

SET LINESIZE 200
COLUMN owner FORMAT A10
COLUMN segment_name FORMAT A30
COLUMN tablespace_name FORMAT A15
COLUMN size_mb FORMAT 99999.00

SELECT *
FROM   (SELECT owner,
               segment_name,
               segment_type,
               tablespace_name,
               ROUND(bytes/1024/1024,2) size_mb
        FROM   dba_segments where owner not in ('SYS','SYSTEM') and segment_type = 'INDEX'
        ORDER BY 5 DESC)
WHERE  ROWNUM <= 10;

Check the top 10 object consuming most of the space in Oracle Database

SET LINESIZE 200
COLUMN owner FORMAT A10
COLUMN segment_name FORMAT A30
COLUMN tablespace_name FORMAT A15
COLUMN size_mb FORMAT 99999.00

SELECT *
FROM   (SELECT owner,
               segment_name,
               segment_type,
               tablespace_name,
               ROUND(bytes/1024/1024,2) size_mb
        FROM   dba_segments where owner not in ('SYS','SYSTEM')
        ORDER BY 5 DESC)
WHERE  ROWNUM <= 20;