CLEANING FRAGMENTED TABLES USING MOVE COMMAND.

Script for checking the highest fragmented table.

Set lines 200 pages 200;

SELECT * FROM (SELECT SUBSTR(TABLE_NAME, 1, 21) TABLE_NAME,NUM_ROWS,AVG_ROW_LEN ROWLEN,BLOCKS,ROUND((AVG_ROW_LEN + 1) * NUM_ROWS / 1000000, 0) NET_MB,ROUND(BLOCKS * (8000 - 23 * INI_TRANS) *(1 - PCT_FREE / 100) / 1000000, 0) GROSS_MB,ROUND((BLOCKS * (8000 - 23 * INI_TRANS) * (1 - PCT_FREE / 100) -(AVG_ROW_LEN + 1) * NUM_ROWS) / 1000000) "WASTED_MB" FROM DBA_TABLES WHERE NUM_ROWS IS NOT NULL AND OWNER LIKE 'FINANCE%' AND PARTITIONED = 'NO' AND (IOT_TYPE != 'IOT' OR IOT_TYPE IS NULL) ORDER BY 7 DESC) WHERE ROWNUM <=20;

TABLE_NAME              NUM_ROWS     ROWLEN     BLOCKS     NET_MB   GROSS_MB  WASTED_MB

--------------------- ---------- ---------- ---------- ---------- ---------- ----------

EMP_RECORDS              68208       2159     341618        147       2453       2305

SALES                         1         26      74467          0        535        535

VENDOR_RECORDS            607008       2089     204515       1269       1468       200

We have 3 tables which are fragmented the most. I am taking EM_RECORDS table for clearing fragmentation. We can do the same for other tables.

STEP 1) Check the Table size:

SQL> select bytes/1024/1024 from dba_segments where segment_name='EMP_RECORDS';

BYTES/1024/1024

---------------

            265

STEP 2) GATHER THE STATS OF TABLE ‘EMP_RECORDS’

SQL> exec dbms_stats.gather_table_stats (ownname=>'FINANCE',tabname=>'EMP_RECORDS',estimate_percent=>100);

STEP 3) CHECK THE FRAGMENTATION:

set lines 300 pages 300 ;

col owner form a15

col table_name form a15

select a.owner, a.table_name, b.bytes/1024/1024 "Size(MB)",  (a.avg_row_len*a.num_rows/1024/1024) "Actual(MB)",

to_char(last_analyzed,'DD-MON-YY') "LAST ANAL",

trunc((b.bytes/1024/1024) - (a.avg_row_len*a.num_rows/1024/1024)) "Diff(MB)" ,

(b.bytes-a.avg_row_len*a.num_rows)*100/ b.bytes "% Frag"

from dba_tables a,

dba_segments b

where a.table_name = b.segment_name

and a.owner = b.owner

and a.table_name = '&TABLE_NAME'

order by 6

;

This will prompt table name type the name of table you want to check for fragmentation.

Enter value for table_name: EMP_RECORDS

old   9: and a.table_name = '&TABLE_NAME'

new   9: and a.table_name = 'EMP_RECORDS'

OWNER           TABLE_NAME         Size(G)  Actual(G) LAST ANAL    Diff(G)     % Frag

--------------- --------------- ---------- ---------- --------- ---------- ----------

FINANCE         EMP_RECORDS            265   194.0979 29-MAY-18         70 26.7555093

STEP 4) Connect to user ‘FINANCE’ and delete some data from this table:

SQL>conn FINANCE/finance

SQL> delete from FINANCE.EMP_RECORDS  where EMP_ID > 'E125412';

49250 rows deleted.

STEP 5) GATHER THE STATS OF TABLE ‘EMP_RECORDS’

SQL> exec dbms_stats.gather_table_stats (ownname=>'FINANCE',tabname=>'EMP_RECORDS',estimate_percent=>100);

STEP 6) Now check again tables fragmentation and the size using steps 3 and step 1 query:

OWNER           TABLE_NAME        Size(MB) Actual(MB) LAST ANAL   Diff(MB)     % Frag

--------------- --------------- ---------- ---------- --------- ---------- ----------

FINANCE         EMP_RECORDS            265 75.5155849 29-MAY-18        189 71.5035529

We can see the table still have the same size even after deleting bulk records. Actual size of the table is only 75 MB but it occupies the 265 MB of tablespace. But you can see more fragmentation as compared before deleting records.

We have to clear the fragmentation only than we can get the desired size of table and for that we have to move the data of the table to same or any other tablespace. But this makes the index unusable which are associated with the table.

Let’s check the status of indexes related to table ‘EMP_RECORDS’

SQL> SELECT INDEX_NAME, TABLE_OWNER,TABLESPACE_NAME,STATUS FROM DBA_INDEXES WHERE TABLE_NAME='EMP_RECORDS';

INDEX_NAME                TABLE_OWNER          TABLESPACE_NAME STATUS

------------------------- -------------------- --------------- ------------

SYS_IL0000134180C00005$$  FINANCE              FINANCEIDX       VALID

SYS_IL0000134180C00006$$  FINANCE              FINANCEIDX       VALID

MAT_BD                    FINANCE              FINANCE          VALID

MAT_RL                    FINANCE              FINANCE          VALID

MAT_COUNTRY               FINANCE              FINANCE          VALID

MAT_UNIQ                  FINANCE              FINANCE          VALID

PK_REC                    FINANCE              FINANCE          VALID

SYS_IL0000134180C00004$$  FINANCE              FINANCEIDX       VALID

STEP 7) Move the table to same tablespace or another this will clear the fragmentation and gives you actual size of table.

SQL> alter table FINANCE.EMP_RECORDS move ;

Table altered.

STEP8) Let’s Check the fragmentation again.

set lines 300 pages 300 ;

col owner form a15

col table_name form a15

select a.owner, a.table_name, b.bytes/1024/1024 "Size(MB)",  (a.avg_row_len*a.num_rows/1024/1024) "Actual(MB)",

to_char(last_analyzed,'DD-MON-YY') "LAST ANAL",

trunc((b.bytes/1024/1024) - (a.avg_row_len*a.num_rows/1024/1024)) "Diff(MB)" ,

(b.bytes-a.avg_row_len*a.num_rows)*100/ b.bytes "% Frag"

from dba_tables a,

dba_segments b

where a.table_name = b.segment_name

and a.owner = b.owner

and a.table_name = '&TABLE_NAME'

order by 6;

OWNER           TABLE_NAME        Size(MB) Actual(MB) LAST ANAL   Diff(MB)     % Frag

--------------- --------------- ---------- ---------- --------- ---------- ----------

FINANCE         EMP_RECORDS            104 75.5155849 29-MAY-18         28 27.3888606

Now the table size reduced to 104 MB.

But this makes the indexes unusable. We can check the same as below:

SQL> SELECT INDEX_NAME, TABLE_OWNER,TABLESPACE_NAME,STATUS FROM DBA_INDEXES WHERE TABLE_NAME='EMP_RECORDS';

INDEX_NAME                TABLE_OWNER          TABLESPACE_NAME STATUS

------------------------- -------------------- --------------- ------------

SYS_IL0000134180C00005$$  FINANCE              FINANCEIDX       VALID

SYS_IL0000134180C00006$$  FINANCE              FINANCEIDX       VALID

MAT_BD                    FINANCE              FINANCE          UNUSABLE

MAT_RL                    FINANCE              FINANCE          UNUSABLE

MAT_COUNTRY               FINANCE              FINANCE          UNUSABLE

MAT_UNIQ                  FINANCE              FINANCE          UNUSABLE

PK_REC                    FINANCE              FINANCE          UNUSABLE

SYS_IL0000134180C00004$$  FINANCE              FINANCEIDX       VALID

We have to rebuild the indexes to make them valid again. We have the dynamic script to get the rebuild command.

SQL> select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where status in ('UNUSABLE','INVALID') and table_name='EMP_RECORDS' AND OWNER='FINANCE';

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILD;'

----------------------------------------------------------------------------------

alter index FINANCE.MAT_BD rebuild;

alter index FINANCE.MAT_RL rebuild;

alter index FINANCE.MAT_COUNTRY rebuild;

alter index FINANCE.MAT_UNIQ rebuild;

alter index FINANCE.PK_REC rebuild;

Execute the result of above script.

SQL> alter index FINANCE.MAT_BD rebuild;

SQL> alter index FINANCE.MAT_RL rebuild;

SQL> alter index FINANCE.MAT_COUNTRY rebuild;

SQL> alter index FINANCE.MAT_UNIQ rebuild;

SQL> alter index FINANCE.PK_REC rebuild;

Now we can check the status of index as below. We can see all become valid.

SQL> SELECT INDEX_NAME, TABLE_OWNER,TABLESPACE_NAME,STATUS FROM DBA_INDEXES WHERE TABLE_NAME='EMP_RECORDS';

INDEX_NAME                TABLE_OWNER          TABLESPACE_NAME STATUS

------------------------- -------------------- --------------- ------------

SYS_IL0000134180C00005$$  FINANCE              FINANCEIDX       VALID

SYS_IL0000134180C00006$$  FINANCE              FINANCEIDX       VALID

MAT_BD                    FINANCE              FINANCE          VALID

MAT_RL                    FINANCE              FINANCE          VALID

MAT_COUNTRY               FINANCE              FINANCE          VALID

MAT_UNIQ                  FINANCE              FINANCE          VALID

PK_REC                    FINANCE              FINANCE          VALID

SYS_IL0000134180C00004$$  FINANCE              FINANCEIDX       VALID

HOW TO SHRINK TABLE AND RECREATE THE INDEX

- Check the space of table that we need to shrink:

SQL> select bytes/1024/1024 from dba_segments where segment_name='EMPLOYEE_RECORDS';

BYTES/1024/1024

------------------------

         200

SQL>

set lines 300 pages 300 ;

select a.owner, a.table_name, b.bytes/1024/1024 "Size(G)",  (a.avg_row_len*a.num_rows/1024/1024) "Actual(G)",

to_char(last_analyzed,'DD-MON-YY') "LAST ANAL",

trunc((b.bytes/1024/1024) - (a.avg_row_len*a.num_rows/1024/1024)) "Diff(G)" ,

(b.bytes-a.avg_row_len*a.num_rows)*100/ b.bytes "% Frag"

from dba_tables a,

dba_segments b

where a.table_name = b.segment_name

and a.owner = b.owner

and a.table_name = 'EMPLOYEE_RECORDS'

order by 6

;

OWNER                          TABLE_NAME                        Size(G)  Actual(G)     LAST ANAL          Diff(G)      % Frag

------------------------------ ------------------------------  ----------    ----------------   --------------      ----------        ----------

FINANCE                        EMPLOYEE_RECORDS               200   155.357003  23-MAY-18         44            22.3214984

We can see this particular table is only 22.32% fragmented. Let’s gather the table stats to get the exact fragmentation.

SQL> exec dbms_stats.gather_table_stats (ownname=>'FINANCE',tabname=>'EMPLOYEE_RECORDS',estimate_percent=>100);

PL/SQL procedure successfully completed.

Now again we gather the table  stats

SQL> set lines 300 pages 300 ;

select a.owner, a.table_name, b.bytes/1024/1024 "Size(G)",  (a.avg_row_len*a.num_rows/1024/1024) "Actual(G)",

to_char(last_analyzed,'DD-MON-YY') "LAST ANAL",

trunc((b.bytes/1024/1024) - (a.avg_row_len*a.num_rows/1024/1024)) "Diff(G)" ,

(b.bytes-a.avg_row_len*a.num_rows)*100/ b.bytes "% Frag"

from dba_tables a,

dba_segments b

where a.table_name = b.segment_name

and a.owner = b.owner

and a.table_name = 'EMPLOYEE_RECORDS'

order by 6

;

OWNER                          TABLE_NAME                        Size(G)  Actual(G)     LAST ANAL          Diff(G)      % Frag

------------------------------ ------------------------------  ----------    ----------------   --------------      ----------        ----------

FINANCE                        EMPLOYEE_RECORDS               200   155.357003  23-MAY-18         44            22.3214984

No change observed as table has latest stats. Let try to fragment the table by multiple deleting/updating the records

SQL> delete from finance.EMPLOYEE_RECORDS where ili_material_number > 'A11111';

65348 rows deleted.

Now let’s gather table stats again and check the difference;

SQL> exec dbms_stats.gather_table_stats (ownname=>'FINANCE',tabname=>'EMPLOYEE_RECORDS',estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL> set lines 300 pages 300 ;

select a.owner, a.table_name, b.bytes/1024/1024 "Size(G)",  (a.avg_row_len*a.num_rows/1024/1024) "Actual(G)",

to_char(last_analyzed,'DD-MON-YY') "LAST ANAL",

trunc((b.bytes/1024/1024) - (a.avg_row_len*a.num_rows/1024/1024)) "Diff(G)" ,

(b.bytes-a.avg_row_len*a.num_rows)*100/ b.bytes "% Frag"

from dba_tables a,

dba_segments b

where a.table_name = b.segment_name

and a.owner = b.owner

and a.table_name = 'EMPLOYEE_RECORDS'

order by 6

;

OWNER                           TABLE_NAME                        Size(G)      Actual(G)        LAST ANAL          Diff(G)      % Frag

--------------------------  ------------------------------             ----------    ----------------   --------------           ----------        ----------

FINANCE                        EMPLOYEE_RECORDS               264         75.5155849    24-MAY-18        188         71.3956118

Now after deletion we observe the table fragmented to around 71%.  Let’s shrink the table to reclaim space.

- We have to enable row movement first before shrink

SQL> alter table FINANCE.EMPLOYEE_RECORDS enable row movement;

Table altered.

- Below command will check the possibility of shrinking table.

SQL>

SQL>  ALTER TABLE finance.EMPLOYEE_RECORDS SHRINK SPACE CHECK;

 ALTER TABLE finance.EMPLOYEE_RECORDS SHRINK SPACE CHECK

*

ERROR at line 1:

ORA-10655: Segment can be shrunk

NOTE: This is not an error message, but a confirmation message about the possibility of shrinking the segment.

ALTER TABLE TABLE_NAME SHRINK is implemented in two phases
1). Segment data is compacted. Through a series of INSERT and DELETE statements.
2). High-water mark (HWM) is adjusted and unused space is deallocated from the segment.
It is optional to run the command in two steps: first with COMPACT option, and later on without, to adjust the HWM.
1. ALTER TABLE MY_TABLE SHRINK SPACE
This is the default - compact rows, and adjust HWM.

2. ALTER TABLE MY_TABLE SHRINK SPACE COMPACT
Compact rows, without adjusting HWM.

3. ALTER TABLE MY_TABLE SHRINK SPACE CASCADE
When you specify CASCADE, Oracle performs the same operations on all dependent objects of table, including secondary indexes on index-organized tables.
Without specifying the CASCADE option, need to manually rebuild the indexes on the segment.

4. ALTER TABLE MY_TABLE SHRINK SPACE CHECK
This is Oracle internal option.
It is used to check for proper segment type and segment attributes
 (e.g. row movement enabled) to allow shrink.
The statement performs the exact same validation as the "ALTER TABLE ... SHRINK SPACE", but it does not perform any actual shrinking on the segment.  

Here I am using First method. This will lock the table till the process end. This command is used to adjust the High Water Mark (HWM) as well as shrink the table in one go.

SQL> ALTER TABLE finance.EMPLOYEE_RECORDS SHRINK SPACE;

Table altered.

SQL> set lines 300 pages 300 ;

set lines 300 pages 300 ;

select a.owner, a.table_name, b.bytes/1024/1024 "Size(G)",  (a.avg_row_len*a.num_rows/1024/1024) "Actual(G)",

to_char(last_analyzed,'DD-MON-YY') "LAST ANAL",

trunc((b.bytes/1024/1024) - (a.avg_row_len*a.num_rows/1024/1024)) "Diff(G)" ,

(b.bytes-a.avg_row_len*a.num_rows)*100/ b.bytes "% Frag"

from dba_tables a,

dba_segments b

where a.table_name = b.segment_name

and a.owner = b.owner

and a.table_name = 'EMPLOYEE_RECORDS'

order by 6

;

OWNER                          TABLE_NAME                        Size(G)  Actual(G)       LAST ANAL      Diff(G)       % Frag

------------------------------ ------------------------------      ----------  ----------         ---------              ----------     ----------

FINANCE                        EMPLOYEE_RECORDS           98.625   75.5155849 24-MAY-18         23           23.4315995

We can see we successfully reclaim the space from the table EMPLOYEE_RECORDS. Now fragmentation reduced to 23% from 71% after shrinking.

          

Also check the status of index associated with the table EMPLOYEE_RECORDS

SQL> SELECT INDEX_NAME, TABLE_OWNER,TABLESPACE_NAME,STATUS FROM DBA_INDEXES WHERE TABLE_NAME='EMPLOYEE_RECORDS';

INDEX_NAME                     TABLE_OWNER                    TABLESPACE_NAME                STATUS

------------------------------ ------------------------------ ------------------------------                   --------

SYS_IL0000133894C00004$$       FINANCE                        METALSIDX                       VALID

SYS_IL0000133894C00005$$       FINANCE                        METALSIDX                       VALID

SYS_IL0000133894C00006$$       FINANCE                        METALSIDX                       VALID

MAT_VDA                                      FINANCE                        METALS                            VALID

MAT_DESIG                                  FINANCE                        METALS                            VALID

MAT_COUNTRY                             FINANCE                        METALS                            VALID

MAT_UNIQ                                    FINANCE                        METALS                            VALID

PK_MATREC                                  FINANCE                        METALS                            VALID

8 rows selected.

Check the size of index and fetch the ddl if we plan to recreate the index. (Here I am doing this for only one Index ‘MAT_VDA’ other index can recreate in same way.

SQL> select bytes/1024/1024 from dba_segments where segment_name='MAT_VDA';

BYTES/1024/1024

---------------

              4

SQL> SET LONG 999999999

select dbms_metadata.get_ddl('INDEX','MAT_VDA','FINANCE') from dual;

DBMS_METADATA.GET_DDL('INDEX','MAT_VDA','FINANCE')

--------------------------------------------------------------------------------

  CREATE INDEX "FINANCE"."MAT_VDA" ON "FINANCE"."EMPLOYEE_RECORDS" ("VDA")

  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "METALS"

DROP the INDEX and Recreate the same. This will save the ample amount of space.

SQL> drop index finance.MAT_VDA;

Index dropped.

SQL>

CREATE INDEX "FINANCE"."MAT_VDA" ON "FINANCE"."EMPLOYEE_RECORDS" ("VDA")

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "METALS"

Index created.

Now we can verify the size same of index. It is reduced from 4 MB to

 2 MB

SQL>

select bytes/1024/1024 from dba_segments where segment_name='MAT_VDA';

BYTES/1024/1024

---------------

              2