Fragmented Table details

Fragmented Table in Oracle 


Below query will display all the highly fragmented table:

SQL> set lines 999
SQL> set pages 999
SQL> col table_name format a30
SQL> select *
  from (select table_name,
  2    3                 round((blocks * 8), 2) "size (kb)",
               round((num_rows * avg_row_len / 1024), 2) "actual_data (kb)",
  4    5                 (round((blocks * 8), 2) -
               round((num_rows * avg_row_len / 1024), 2)) "wasted_space (kb)"
          from dba_tables
         where (round((blocks * 8), 2) >
               round((num_rows * avg_row_len / 1024), 2))
         order by 4 desc)
 WHERE ROWNUM <= 10;  6    7    8    9   10   11

TABLE_NAME                      size (kb) actual_data (kb) wasted_space (kb)
------------------------------ ---------- ---------------- -----------------
ASO_ORDER_FEEDBACK_T              6130376       1315620.42        4814755.58
XLA_DIAG_SOURCES                 26622880       22002509.8        4620370.23

10 rows selected.

SQL> select sum(bytes)/1024/1024/1024 from dba_segments where segment_name='ASO_ORDER_FEEDBACK_T';

SUM(BYTES)/1024/1024/1024
-------------------------
               5.88623047

SQL> select table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",
  2  round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
  3  round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
(round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100 "percentage"
  4    5  from all_tables WHERE table_name='ASO_ORDER_FEEDBACK_T';

TABLE_NAME                     AVG_ROW_LEN TOTAL_SIZE                                 ACTUAL_SIZE                                FRAGMENTED_SPACE                           percentage
------------------------------ ----------- ------------------------------------------ ------------------------------------------ ------------------------------------------ ----------
ASO_ORDER_FEEDBACK_T                   449 11973.39MB                                 1284.79Mb                                  10688.61MB                                 89.2697056


If above percentage is above 20 percent then we can think of these tables.

All the index details in that table:

SQL> select index_name from dba_indexes where table_name='ASO_ORDER_FEEDBACK_T'

INDEX_NAME
--------------------------------------------------------------------------------------------------------------------------------
SYS_IL0000081743C00333$$
SYS_IL0000081743C00334$$
SYS_IL0000081743C00335$$
SYS_IL0000081743C00336$$

SYS_IL0000081743C00337$$

We can take many measure to take care this fragmented table.

Regards,