ORACLE DBA - Get segment size, compare table and index size

Creating simple function we can get table`s size.

CREATE OR REPLACE FUNCTION ULFET.CHECK_TABLE_SIZE(TAB_NAME VARCHAR) RETURN varchar
IS
   tab_size number;
   str varchar2(50);
BEGIN  
     SELECT  SUM (bytes) / (1024 * 1024)  INTO tab_size
     FROM sys.dba_extents WHERE segment_type = 'TABLE' AND segment_name = TAB_NAME GROUP BY segment_name;
str := tab_name||' size is '||tab_size||' MB';
RETURN str;
END;
/

P.S: If compilation will error grant select on sys.dba_extents to desired user and repeat creation.

SQL> conn /as sysdba

SQL> grant select on sys.dba_extents to ulfet;

Grant succeeded.

SQL>



--call
select ULFET.CHECK_TABLE_SIZE('TEST_TAB') from dual;

or

set serveroutput on
declare
l_answvarchar2(100);
begin
l_answ:=ULFET.CHECK_TABLE_SIZE('TEST_TAB');
dbms_output.put_line(l_answ);
end;
select ULFET.CHECK_TABLE_SIZE('TEST_TAB') from dual;

P.S: Please note that table_name should be entered with upper case.

Above function will return for tables which gathered statistic.
To get statistics you can use several methods : ANALYZE or DBMS_STATS.GATHER_TABLE_STATS

Example:


exec ANALYZE TABLE employees COMPUTE STATISTICS; --it computes actual statistics
exec ANALYZE TABLE employees ESTIMATE STATISTICS; --estimates the stats
exec DBMS_STATS.GATHER_TABLE_STATS ('HR','EMPLOYEES');


DBMS_STATS package available on Oracle 10g and later versions.

Sometimes we getting request something like that, is it possible index size bigger than table`s size?
Yes, possible, let`s simulate.

SQL> create table test_tbl (id number, col1 varchar2(2000));

Table created.

SQL> create index idx_test_tbl on test_tbl(col1);

Index created.

SQL> begin
        for i in 1..10000 loop
                insert into test_tbl values (i, lpad(i,2000, 'z'));
        end loop;
        commit;
end;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.


SQL> delete from test_tbl;

10000 rows deleted.

SQL> commit;

Check table and index`s size


SQL> col segment_name format a20
SQL> SELECT segment_name, SUM (bytes) / (1024 * 1024) size_mb
FROM sys.dba_extents
WHERE segment_type in('TABLE','INDEX')
AND segment_name in('TEST_TBL', 'IDX_TEST_TBL') and owner='ULFET'
group by segment_name;

SEGMENT_NAME            SIZE_MB
--------------------              ----------
TEST_TBL                          27
IDX_TEST_TBL                 46

SQL>

SQL> with tabs as (select segment_name tablename,bytes tablesize from user_segments where segment_type='TABLE'),

inds as (select i.index_name indexname,i.table_name tablename,s.bytes indexsize from user_indexes i join user_segments s on (i.index_name=s.segment_name))

select * from tabs natural join inds where indexsize > tablesize;  2    3  

 TABLENAME    TABLESIZE  INDEXNAME     INDEXSIZE  
 ---------    ---------  ------------  ---------  
 TEST_TBL     28311552   IDX_TEST_TBL  48234496  
 SQL> 

List of indexes which size bigger than tables


SQL> col tablename format a23
SQL> WITH tabs AS (SELECT segment_name tablename, bytes tablesize FROM dba_segments

  WHERE segment_type = 'TABLE' AND owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'PUBLIC')),
        inds AS (SELECT i.index_name indexname, i.table_name tablename, s.bytes indexsize FROM dba_indexes i  JOIN dba_segments s ON (i.index_name = s.segment_name)
AND s.owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'PUBLIC')
)
SELECT * FROM tabs NATURAL JOIN inds WHERE indexsize > tablesize;

For performance it is good to check table`s which size more than 2 GB and not partitioned.

Get list of tables which size more than 2 GB and not partitioned.

SELECT A.OWNER,
       A.SEGMENT_NAME,
       BYTES,
       ROUND (BYTES / 1024 / 1024 / 1024, 2) SIZE_GB,
       B.OBJECT_TYPE,
       PARTITIONED
  FROM DBA_SEGMENTS A, DBA_OBJECTS B, DBA_TABLES C
 WHERE     A.SEGMENT_NAME = B.OBJECT_NAME
       AND A.OWNER NOT IN ('SYS', 'SYSTEM')
       AND BYTES > 2147483648
       AND OBJECT_TYPE = 'TABLE'
       AND C.TABLE_NAME = A.SEGMENT_NAME
       --AND PARTITIONED = 'YES'
       ORDER by size_gb,  segment_name