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