Table De-Fragmentation to Reclaim the Space and Different Methods of De-Fragmentation

ENVIRONMENT

=============

There are many ways to reclaim the space from the table. Some of the Traditional ways are below.,

1. Imp/exp
2. Alter tablespace move
3. Truncate and Insert
4. user dbms_redefinition package to copy the table

Oracle New Feature is

1. Using shrink feature of Oracle 10g
2. Shrink compact
3. Shrink cascade

2. Redefinition of Tables using DBMS_REDIFINITION

A Brief about SHRINK tables

=======================

SHRINK CLAUSE

             Alter table table_name shrink space;

The shrink clause lets you manually shrink space in a table, index-organized table or its overflow segment, index, partition, sub partition, LOB segment, materialized view, or materialized view log. This clause is valid only for segments in tablespaces with automatic segment management. By default, Oracle Database compacts the segment, adjusts the high water mark, and releases the recuperated space immediately.

Compacting the segment requires row movement. Therefore, you must enable row movement for the object you want to shrink before specifying this clause. Further, if your application has any rowid-based triggers, you should disable them before issuing this clause.

Note:

Do not attempt to enable row movement for an index-organized table before specifying the shrink_clause. The ROWID of an index-organized table is its primary key, which never changes. Therefore, row movement is neither relevant nor valid for such tables.

COMPACT

Alter table table_name shrink space compact;

If you specify COMPACT, then Oracle Database only defragments the segment space and compacts the table rows for subsequent release. The database does not readjust the high water mark and does not release the space immediately. You must issue another ALTER TABLE … SHRINK SPACE statement later to complete the operation. This clause is useful if you want to accomplish the shrink operation in two shorter steps rather than one longer step.

Alter table table_name shrink space compact;

        Alter table table_name shrink space;   —-Row level locking happen. This                                                                                                     command can be executed in OFF                                                                                                 Peak Hours.                                                                                                                                

The COMPACT clause lets you divide the shrink segment operation into two phases. When you specify COMPACT, Oracle Database defragments the segment space and compacts the table rows but postpones the resetting of the high water mark and the deallocation of the space until a future time. This option is useful if you have long-running queries that might span the operation and attempt to read from blocks that have been reclaimed. The defragmentation and compaction results are saved to disk, so the data movement does not have to be redone during the second phase. You can reissue the SHRINK SPACE clause without the COMPACT clause during off-peak hours to complete the second phase.

CASCADE

alter table table_name shrink space cascade;

The CASCADE clause extends the segment shrink operation to all dependent segments of the object. For example, if you specify CASCADE when shrinking a table segment, all indexes of the table will also be shrunk. (You need not specify CASCADE to shrink the partitions of a partitioned table.) To see a list of dependent segments of a given object, you can run the OBJECT_DEPENDENT_SEGMENTS procedure of the DBMS_SPACE package

To shrink a basicfile LOB:

alter table <table_name> MODIFY LOB <column_name> shrink space
To shrink a single partition of a partition table:

alter table <table_name> MODIFY PARTITION <partition_name> shrink                   space;

Oracle split this process as below.,

Oracle move the rows which are located in the middle or at the end of a segment further more down to the beginning of the segment and make the segment more compact. This process is moving the data row by row. It acquires a row level lock when the row is moved down to the beginning of the segment. The corresponding index data will be handled like any other row level DML. So we do not need to worry about rebuilding the indexes for the row. Also row level lock will happen for very short moment. Before we start this phase, we need to enable row movement.

Here we have used the 1st Method – Shrink feature to reclaim the space from the table.

1-a. Shrink compact feature

Firstly, Created a table “MYTABLE” using the below Procedure which also populates the table with 10000000 records. The steps

SET ECHO ON

SET SERVEROUTPUT ON SIZE 1000000

CREATE TABLE MYTABLE

(id                NUMBER              NOT NULL

,CONSTRAINT id_pk  PRIMARY KEY (id));

DECLARE

TYPE number_table IS TABLE OF MYTABLE.id%TYPE INDEX BY BINARY_INTEGER;

number_list NUMBER_TABLE;

BEGIN

FOR i IN 1..10000000 LOOP

number_list(i) := i;

END LOOP;

FORALL i IN 1..number_list.COUNT

INSERT INTO MYTABLE VALUES (number_list(i));

COMMIT;

END;

/

**********************************************************************

Created the table “MYTABLE”

=======================

CREATE TABLE mytable

(id                NUMBER              NOT NULL

,CONSTRAINT id_pk  PRIMARY KEY (id));

SQL> SQL>   2    3

Table created.

SQL>

SQL> DECLARE

2

3      TYPE number_table IS TABLE OF mytable.id%TYPE INDEX BY BINARY_INTEGER;

4    5

6      number_list NUMBER_TABLE;

7

8    BEGIN

9

10

11      FOR i IN 1..10000000 LOOP

12

13        number_list(i) := i;

14

15      END LOOP;

16

17

18      FORALL i IN 1..number_list.COUNT

19        INSERT INTO mytable VALUES (number_list(i));

20

21      COMMIT;

22

23    END;

24    /

PL/SQL procedure successfully completed.

Populated the table with 10000000 records

==================================

SQL> select count(*) from vsridhar.mytable;

COUNT(*)

————–

10000000

Size and Blocks of the table “MYTABLE”

==============================

SQL> select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 “SIZE_MB”,MAX_SIZE/1024/1024 “MAX_SIZE_MB” from dba_segments where SEGMENT_NAME=’MYTABLE’;

SEGMENT_NAME         SEGMENT_TYPE                                              SIZE_MB     MAX_SIZE_MB

——————–       —————————————————— ———-        ———–

MYTABLE              TABLE                                                                          120               2048

SQL> select blocks from dba_tables where table_name=’MYTABLE’;

BLOCKS

———-

15197

**********************************************************************

Fragmentation Method 1-a:

======================

Step 1:

======

Analyze the Table.,

analyze table vsridhar.MYTABLE compute statistics;

SQL> analyze table vsridhar.MYTABLE compute statistics;

Table analyzed.

Step 2:

=====

Check the Size and Blocks of the table.,

select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 “SIZE_MB”,MAX_SIZE/1024/1024 “MAX_SIZE_MB” from dba_segments where SEGMENT_NAME=’MYTABLE’;

select blocks from dba_tables where table_name=’MYTABLE’;

SQL> select blocks from dba_tables where table_name=’MYTABLE’;

BLOCKS

———-

15197

SQL> select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 “SIZE_MB”,MAX_SIZE/1024/1024 “MAX_SIZE_MB” from dba_segments where SEGMENT_NAME=’MYTABLE’;

SEGMENT_NAME         SEGMENT_TYPE           SIZE_MB    MAX_SIZE_MB

——————–          —————————     ————  ——————-

MYTABLE                  TABLE                             120           2048

Step 3:

=====

Delete some rows from table and analyze the table again.,

analyze table vsridhar.MYTABLE compute statistics;

SQL> delete from vsridhar.mytable where id like ‘%9%’;

5217031 rows deleted.

SQL> select count(*) from vsridhar.mytable;

COUNT(*)

———-

4782969

SQL> analyze table vsridhar.MYTABLE compute statistics;

Table analyzed.

Step 4:

=====

Again check the blocks and size of the table whether the blocks are reduced or not.,

select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 “SIZE_MB”,MAX_SIZE/1024/1024 “MAX_SIZE_MB” from dba_segments where SEGMENT_NAME=’MYTABLE’;

select blocks from dba_tables where table_name=’MYTABLE’;

SQL> select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 “SIZE_MB”,MAX_SIZE/1024/1024 “MAX_SIZE_MB” from dba_segments where SEGMENT_NAME=’MYTABLE’;

SEGMENT_NAME       SEGMENT_TYPE       SIZE_MB      MAX_SIZE_MB

————————    ————————-  ————— ——————–

MYTABLE                TABLE                        120              2048

SQL> select blocks from dba_tables where table_name=’MYTABLE’;

BLOCKS

———-

15197

Step 5:

=====

Before shrinking the table, one should enable the row movement in that table. After enabling the row movement, Shrinking contains of two simple steps.,

alter table vsridhar.MYTABLE enable row movement;

alter table vsridhar.MYTABLE shrink space compact;

SQL> alter table vsridhar.MYTABLE shrink space compact;

Table altered.

Step 6:

======

Now just for checking purpose, check the size and blocks of the table.,

select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 “SIZE_MB”,MAX_SIZE/1024/1024 “MAX_SIZE_MB” from dba_segments where SEGMENT_NAME=’MYTABLE’;

select blocks from dba_tables where table_name=’MYTABLE’;

SQL> analyze table vsridhar.MYTABLE compute statistics;

Table analyzed.

SQL> select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 “SIZE_MB”,MAX_SIZE/1024/1024 “MAX_SIZE_MB” from dba_segments where SEGMENT_NAME=’MYTABLE’;

SEGMENT_NAME         SEGMENT_TYPE                                              SIZE_MB MAX_SIZE_MB

——————–          ————————————————– ————   ——————-

MYTABLE              TABLE                                                                          120          2048

SQL> select blocks from dba_tables where table_name=’MYTABLE’;

BLOCKS

———-

15197

Step 7:

======

Now Execute the next statement to get the accurate result i.e., to shrink the table completely.

alter table vsridhar.MYTABLE shrink space;

analyze table vsridhar.MYTABLE compute statistics;

SQL> alter table vsridhar.MYTABLE shrink space;

Table altered.

SQL> analyze table vsridhar.MYTABLE compute statistics;

Table analyzed.

Step 8:

=====

Now check the blocks and size using the below query and we can clearly see the difference in space.,

select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 “SIZE_MB”,MAX_SIZE/1024/1024 “MAX_SIZE_MB” from dba_segments where SEGMENT_NAME=’MYTABLE’;

select blocks from dba_tables where table_name=’MYTABLE’;

SQL> select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 “SIZE_MB”,MAX_SIZE/1024/1024 “MAX_SIZE_MB” from dba_segments where SEGMENT_NAME=’MYTABLE’;

SEGMENT_NAME         SEGMENT_TYPE                  SIZE_MB      MAX_SIZE_MB

——————–            ——————————-    —————  ——————-

MYTABLE                       TABLE                                 59.8125        2048

SQL> select blocks from dba_tables where table_name=’MYTABLE’;

BLOCKS

———-

7521

Note:

=====

When shrinking was in process, simultaneously we have also tested some DML operations on the same table. It worked fine. So this process can be carried out ONLINE.

SQL> select count(*) from mytable where id like ‘%1%’;

COUNT(*)

———-

2685818

SQL> update mytable set id=9 where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from mytable where id=9;

  ID

———-

   9

SQL> select * from mytable where id=1;

no rows selected

**********************************************************************

Fragmentation Method 1-b – Using “shrink cascade” command

1. Checked the Size of the segments.,

SQL> select segment_name,bytes/1024/1024 size_mb from dba_segments where owner=’VSRIDHAR’;

SEGMENT_NAME            SIZE_MB

——————–                  ———-

MYTABLE                           58

ID_PK2                                 88

2. Checked the count of the table “MYTABLE”

SQL> select count(*) from vsridhar.mytable;

COUNT(*)

———-

4782969

3. Deleted some rows from the table “MYTABLE”.

SQL> delete from vsridhar.mytable where id like ‘%2%’;

2685817 rows deleted.

4. Checked the size again after deleting but the size of the segment is not reduced.,

SQL> select segment_name,bytes/1024/1024 size_mb from dba_segments where owner=’VSRIDHAR’;

SEGMENT_NAME            SIZE_MB

——————–                  ———-

MYTABLE                           58

ID_PK2                                 88

5. Checked the status of the index and analyzed the table.,

SQL> select index_name,table_name,owner,status from dba_indexes where table_name=’MYTABLE’;

INDEX_NAME           TABLE_NAME           OWNER                STATUS

——————–       ——————–        ——————–   ——————-

ID_PK2                        MYTABLE               VSRIDHAR             VALID

SQL> analyze table vsridhar.mytable compute statistics;

Table analyzed.

SQL> select segment_name,bytes/1024/1024 size_mb from dba_segments where owner=’VSRIDHAR’;

SEGMENT_NAME            SIZE_MB

——————–                  ———-

MYTABLE                           58

ID_PK2                                 88

Even after analyzing the table the size of the table did not reduce.

STEP 1:

======

Enable the row movement for that table by using the below command.,

alter table vsridhar.mytable enable row movement;

SQL> alter table vsridhar.mytable enable row movement;

Table altered.

STEP 2:

======

Shrink the table using the below command to reclaim the space.,

alter table vsridhar.mytable shrink space cascade;

SQL> alter table vsridhar.mytable shrink space cascade;

Table altered.

STEP 4:

======

After shrinking the table check the size of the table and also check whether the index to the corresponding table is in the VALID state or not.

select segment_name,bytes/1024/1024 size_mb from dba_segments where owner=’VSRIDHAR’;

select index_name,table_name,owner,status from dba_indexes where table_name=’MYTABLE’;

SQL> select segment_name,bytes/1024/1024 size_mb from dba_segments where owner=’VSRIDHAR’;

SEGMENT_NAME            SIZE_MB

——————–                  ———-

MYTABLE                           25.4375

ID_PK2                                 37.125

SQL> select index_name,table_name,owner,status from dba_indexes where table_name=’MYTABLE’;

INDEX_NAME           TABLE_NAME           OWNER                STATUS

——————–      ——————–       ——————–   ——————–

ID_PK2                     MYTABLE                 VSRIDHAR                VALID

**********************************************************************

Fragmentation Method 2:

====================

ONLINE TABLE REDEFINITION

The Oracle online table reorganization package, (dbms_redefinition) is used to reorganize tables while they are accepting updates.  See here for details on using the dbms_redefinition package for reorganizing Oracle tables online.  The online reorganization packages does this by creating a snapshot on the target table and applying all table changes after the table has been reorganized with the “Create table as select” command:

Before going to the steps, as we did in the earlier stage create a table “MYTABLE”.

SQL> SET ECHO ON

SQL> SET SERVEROUTPUT ON SIZE 1000000

CREATE TABLE mytable

(id                NUMBER              NOT NULL

,CONSTRAINT id_pk  PRIMARY KEY (id));

SQL> SQL>   2    3

DECLARE

TYPE number_table IS TABLE OF mytable.id%TYPE INDEX BY BINARY_INTEGER;

number_list NUMBER_TABLE;

BEGIN

FOR i IN 1..10000000 LOOP

number_list(i) := i;

END LOOP;

FORALL i IN 1..number_list.COUNT

INSERT INTO mytable VALUES (number_list(i));

COMMIT;

END;

Table created.

SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24    /

PL/SQL procedure successfully completed.

SQL> select count(*) from vsridhar.mytable;

COUNT(*)

———-

10000000

SQL> analyze table vsridhar.mytable compute statistics;

Table analyzed.

Elapsed: 00:01:08.66

SQL> select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 “SIZE_MB”,MAX_SIZE/1024/1024 “MAX_SIZE_MB” from dba_segments where SEGMENT_NAME=’MYTABLE’;

SEGMENT_NAME         SEGMENT_TYPE                                              SIZE_MB MAX_SIZE_MB

——————– —————————————————— ———- ———–

MYTABLE              TABLE                                                         120        2048

Elapsed: 00:00:00.18

SQL> select blocks from dba_tables where table_name=’MYTABLE’;

BLOCKS

———-

15197

Elapsed: 00:00:00.03

Delete some rows from the table “MYTABLE”:

=====================================

delete from vsridhar.mytable where id like ‘%9%’

/

SQL> delete from vsridhar.mytable where id like ‘%9%’

/  2

5217031 rows deleted.

Elapsed: 00:03:06.87

SQL> select count(*) from vsridhar.mytable;

COUNT(*)

———-

4782969

Elapsed: 00:00:00.23

SQL>

Analyze the Table again and check the size and blocks of the Table:

=====================================================

analyze table vsridhar.MYTABLE compute statistics;

select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 “SIZE_MB”,MAX_SIZE/1024/1024 “MAX_SIZE_MB” from dba_segments where SEGMENT_NAME=’MYTABLE’;

select blocks from dba_tables where table_name=’MYTABLE’;

SQL> analyze table vsridhar.MYTABLE compute statistics;

Table analyzed.

Elapsed: 00:00:37.25

SQL> select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 “SIZE_MB”,MAX_SIZE/1024/1024 “MAX_SIZE_MB” from dba_segments where SEGMENT_NAME=’MYTABLE’;

SEGMENT_NAME         SEGMENT_TYPE                                              SIZE_MB MAX_SIZE_MB

——————– —————————————————— ———- ———–

MYTABLE              TABLE                                                         120        2048

Elapsed: 00:00:00.23

SQL> select blocks from dba_tables where table_name=’MYTABLE’;

BLOCKS

———-

15197

Elapsed: 00:00:00.02

From the above we can clearly see there was no difference in the size and blocks of the table even after the deletion of rows. The space can be reclaimed using the ONLINE TABLE REDEFINITION Feature in Oracle.

Please follow the below steps for Online Table Redefinition., – Method 2

STEP 1:

======

Check whether the table can be redefined or not.

EXEC Dbms_Redefinition.Can_Redef_Table(‘VSRIDHAR’, ‘MYTABLE’);

SQL> EXEC Dbms_Redefinition.Can_Redef_Table(‘VSRIDHAR’, ‘MYTABLE’);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.53

STEP 2:

======

Create new table with CTAS – “Creating Table As Select”

CREATE TABLE VSRIDHAR.MYTABLE2

TABLESPACE mis AS

SELECT * FROM VSRIDHAR.MYTABLE

/

SQL> CREATE TABLE VSRIDHAR.MYTABLE2

TABLESPACE mis AS

SELECT * FROM VSRIDHAR.MYTABLE

/  2    3    4

Table created.

Elapsed: 00:00:02.69

STEP 3:

=======

Start Redefinition

EXEC Dbms_Redefinition.Start_Redef_Table( –

  ‘VSRIDHAR’, –

  ‘MYTABLE’, –

  ‘MYTABLE2’, –

  ‘ID ID’);

SQL> EXEC Dbms_Redefinition.Start_Redef_Table( –

‘VSRIDHAR’, –

‘MYTABLE’, –

‘MYTABLE2’, –

‘ID ID’);> > > >

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.32

STEP 4:

======

Optionally synchronize new table with interim data

EXEC dbms_redefinition.sync_interim_table( –

  ‘VSRIDHAR’, ‘MYTABLE’, ‘MYTABLE2’);

SQL> EXEC dbms_redefinition.sync_interim_table( –

‘VSRIDHAR’, ‘MYTABLE’, ‘MYTABLE2’);>

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06

STEP 5:

=======

Add new keys, FKs and triggers

ALTER TABLE VSRIDHAR.MYTABLE2 ADD (CONSTRAINT id_pk2 PRIMARY KEY (id))

/

SQL> ALTER TABLE VSRIDHAR.MYTABLE2 ADD (CONSTRAINT id_pk2 PRIMARY KEY (id))

/  2

Table altered.

Elapsed: 00:00:08.35

STEP 6:

=======

Complete redefinition

EXEC Dbms_Redefinition.Finish_Redef_Table( –

  ‘VSRIDHAR’, ‘MYTABLE’, ‘MYTABLE2’);

SQL> EXEC Dbms_Redefinition.Finish_Redef_Table( –

‘VSRIDHAR’, ‘MYTABLE’, ‘MYTABLE2’);>

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.07

STEP 7:

=======

Remove original table which now has the name of the new table

DROP TABLE VSRIDHAR.MYTABLE2;

SQL> DROP TABLE VSRIDHAR.MYTABLE2;

Table dropped.

Elapsed: 00:00:00.49

AFTER TABLE ONLINE REDEFINITION SIZE AND BLOCKS OF THE TABLE:

========================================================

SQL> select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 “SIZE_MB”,MAX_SIZE/1024/1024 “MAX_SIZE_MB” from dba_segments where SEGMENT_NAME=’MYTABLE’;

SEGMENT_NAME         SEGMENT_TYPE    SIZE_MB     MAX_SIZE_MB

——————–            ———————-  ————-  ——————-

MYTABLE              TABLE                            58               2048

Elapsed: 00:00:00.66

SQL> select blocks from dba_tables where table_name=’MYTABLE’;

BLOCKS

———-

Elapsed: 00:00:00.23

Note: Since the table is not analyzed the result shows the blocks as empty.

SQL> analyze table vsridhar.mytable compute statistics;

Table analyzed.

Elapsed: 00:00:34.30

SQL> select blocks from dba_tables where table_name=’MYTABLE’;

BLOCKS

———-

7405

Elapsed: 00:00:00.01

select status,constraint_name from DBA_constraints where table_name = ‘MYTABLE’;

SQL> select status,constraint_name from DBA_constraints where table_name = ‘MYTABLE’;

STATUS                   CONSTRAINT_NAME

———————— ——————————————————————————————

ENABLED                  SYS_C009785

ENABLED                  ID_PK2

Elapsed: 00:00:00.26

There are several advantages over traditional methods.  The advantages are below.,

1. It can be done in online. There is a table level lock for very short moment. Traditional methods are not supporting to reset the HWM in online.

2. It does not take extra space while resetting the HWM. If we use traditional method, DBMS_REDEFINITION package uses double the amount of space.

3. It does acquire only row level lock while performing majority of the shrinking (moving rows) work. It acquires table level lock only when it resets the HWM which is in phase II. But traditional methods requires table down time for resetting the HWM except using dbms_redefinition package.

4. Index will be maintained and remain usable. But in traditional methods, we need to rebuild the index. Especially when we use ALTER TABLESPACE MOVE command.

5. It can be made in one command (alter table EMP shrink space). In traditional method, there are multiple steps.

6. If you are not sure that you can afford table level lock at specific time, then you can do the majority of the shrinking work and later we can reset the HWM. Since table level lock is required only while resetting the HWM. The whole process can be done in two steps. This advantage is not available in traditional methods.

Some of the Restrictions are.,

1. It is only possible in ASSM tablespace

2. Not supporting for clustered tables, tables with column data type           LONG