How to find out table fragmentation and fix it in oracle 11g

When you start doing DML operations such as deletes and updates constantly in a table,
you may end up having a fragmented table in your database.As we know the high water mark of
table actually defines the border line between used and unused space for tables. While performing
full table scan, oracle will always read the data up to the high water mark (used block).
HWM is an indicator of USED BLOCKS in the database .
The following tests have been done in oracle 11g Rhel 6.

An example follows how to make a table full of holes and start wasting space and of course
performance when doing full table scans on it.

1. Create the table OBJECTS.
[oracle@server1 ~]$ sqlplus appuser/appuser
SQL> create table test as select * from all_objects;
Table created
SQL> create index IDXT1 on test(owner);
Index created

SQL> select count(*) from test;

  COUNT(*)
----------
     57995

Gather table stats :-
SQL>Begin DBMS_STATS.GATHER_TABLE_STATS (
     ownname => 'APPUSER',
     tabname => 'TEST',
     degree => 2,
     cascade => TRUE,
     METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
     estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
     END;
     /

Check high water mark on db block:-
SQL>select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"
From user_tables where table_name='TEST';
 Ever Used Never Used Total rows
---------- ---------- ----------
       854          0      57986

2. Lets check the size of the table( with fragmented):-
SQL> select table_name,round((blocks*8),2)||'kb' "size"
 From user_tables where table_name = 'TEST';

TABLE_NAME                     size
------------------------------ ------------------------------------------
TEST                           6832kb

3. Make a huge hole deleting all the rows and leaving just one!
SQL>DELETE FROM TEST WHERE ROWNUM < 57985;
SQL> commit;

Now again check the table size after deletion:-
SQL> select table_name,round((blocks*8),2)||'kb' "size"
 From user_tables where table_name = 'TEST';

TABLE_NAME                     size
------------------------------ ------------------------------------------
TEST                           6832kb

We see after the deletions of the rows the tablesize is same.

4.But the actual data size is:-
SQL>select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
from user_tables where table_name = 'TEST';

TABLE_NAME                     size
------------------------------ ------------------------------------------
TEST                           .17kb


So the difference in size is like (6832-.17)KB = 6829.83KB


5.The solution to win back the wasted table space is reseting HighWaterMark.
For that we need to reorganize the fragmented table. We have 4 options to reorganize
fragmented tables:
1. alter table ... move + rebuild indexes
2. create table as select ( CTAS)
3. export / truncate / import
4. By shrinking the  Table


Option 1:-
Before going ahead with this option first take a fresh statistics for the table
SQL>Begin DBMS_STATS.GATHER_TABLE_STATS (
     ownname => 'APPUSER',
     tabname => 'TEST',
     degree => 2,
     cascade => TRUE,
     METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
     estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
     END;
     /


[oracle@server1 ~]$ sqlplus /  as sysdba
SQL> alter table appuser.test move;
Table altered.

SQL> select status,index_name from dba_indexes
where table_name like 'TEST%';

STATUS   INDEX_NAME
-------- ------------------------------
UNUSABLE IDXT1

SQL> ALTER INDEX APPUSER.IDXT1 REBUILD online;
SQL> select status,index_name from dba_indexes
where table_name like 'TEST%';;

STATUS   INDEX_NAME
-------- ------------------------------
VALID    IDXT1

SQL> Begin DBMS_STATS.GATHER_TABLE_STATS (
     ownname => 'APPUSER',
     tabname => 'TEST',
     degree => 2,
     cascade => TRUE,
     METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
     estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
     END;
     /


Now check the original table size after moving the table:-
SQL> select table_name,owner,round((blocks*8),2)||'kb' "size"
from dba_tables where table_name like 'TEST%';
TABLE_NAME                     OWNER                          size
------------------------------ -----------------              ----------------
TEST                           APPUSER                        32kb

We see the table size has reduces and we recovered the wasted space.

Option 2:-create table as select ( CTAS)

P.S. in this procedure keep a track of created index on the table.Cause after dropping the
table we need to recreate those indexes.

[oracle@server1 ~]$ sqlplus /  as sysdba
SQL> select index_name,table_owner ,table_name from dba_ind_columns where table_name like 'TEST%';

INDEX_NAME                     TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------
IDXT1                          APPUSER                        TEST
SQL> create table test1 as select * from test;

Table created.

SQL> drop table test purge;

Table dropped.

SQL> rename test1 to test;(This query if you are the table owner)
OR
SQL> alter table appuser.test1 rename to test;(Use this query if you are doing the activity as sys user)

Table renamed.

SQL> Begin DBMS_STATS.GATHER_TABLE_STATS (
     ownname => 'APPUSER',
     tabname => 'TEST',
     degree => 2,
     cascade => TRUE,
     METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
     estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
     END;
     /

PL/SQL procedure successfully completed.

SQL> select table_name,round((blocks*8),2)||'kb' "size"
 from dba_tables
 where table_name = 'TEST';

TABLE_NAME size
------------------------------ ------------------------------------------
TEST 32kb

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
 from user_tables
 where table_name = 'TEST';

TABLE_NAME size
------------------------------ ------------------------------------------
TEST .17kb

SQL> select status from DBA_INDEXES
 where table_name = 'TEST';

no rows selected

SQL>create index IDXT1 on APPUSER.TEST ( OWNER ) ;




Option 3.
SQL> select table_name,round((blocks*8),2)||'kb' "size"
  2   from user_tables
  3   where table_name = 'TEST';

TABLE_NAME size
---------- ------------------------------------------
TEST       6832kb


SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
  2   from user_tables
  3   where table_name = 'TEST';

TABLE_NAME size
---------- ------------------------------------------
TEST       .17kb

SQL>  select status from user_indexes where table_name = 'TEST';

STATUS
--------
VALID

Now take an export backup of the Test table:-
[oracle@server1 ~]$ expdp system/system dumpfile=test.dmp directory=DATA_PUMP_DIR tables=appuser.test
Now truncate the table
[oracle@server1 ~]$ sqlplus /  as sysdba
SQL> truncate table appuser.test;

Now lets import the table from the expdp dump.
[oracle@server1 ~]$ impdp system/system dumpfile=test.dmp tables=appuser.test directory=DATA_PUMP_DIR ignore=y

SQL>EXEC dbms_redefinition.can_redef_table('APPUSER','TEST', dbms_redefinition.cons_use_pk);

Option 4.By shrinking the  Table

SQL>Begin DBMS_STATS.GATHER_TABLE_STATS (
     ownname => 'APPUSER',
     tabname => 'TEST',
     degree => 2,
     cascade => TRUE,
     METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
     estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
     END;
     /

SQL> alter table TEST enable row movement;
Table altered.
#In first part re-arrange rows and in second part reset the HWM.
SQL> alter table TEST shrink space compact;
Table altered.
SQL> alter table TEST shrink space;
Table altered.

For this method the whole tablespace has to be in ASSM(automatic segment space manament).

Benefit for using Shrink command Method:-
Unlike "alter table move", indexes are not in UNUSABLE state. After shrink command, indexes are updated also.
It is an online operation, so you do not need downtime to do re-organization.
It does not require any extra space for the process to complete.