Alter table Shrink space command in Oracle

The table is fragmented because of many insert, update, and delete operations. To reduce the fragmentation, we followed these steps: we released unused space from the segments table and index. These steps help analyze and remove fragmentation at the table level.

Remove fragmentation from tables in Oracle

1. Check the size of tables.

Select table_name, round((blocks*8)/1024,2)||'MB' "size" from user_tables where table_name = 'EMP';

Select table_name, round((blocks*8)/1024,2)||'MB' "size" from dba_tables where table_name = 'TRAN' and owner='SCOTT';

2. Check the actual size of tables.

select table_name, round((num_rows*avg_row_len/1024/1024),2)||'MB' "size" from user_tables  where table_name = 'EMP';

select table_name, round((num_rows*avg_row_len/1024/1024),2)||'MB' "size" from dba_tables where table_name = 'TRAN' and owner='SCOTT';

3. Enable the row movement for the table which need to shrink.
For this we must first allow ORACLE to change the ROWIDs of these rows by issuing. ROWIDs are normally assigned to a row for the life time of the row at insert time.

4. Now start Shrink Space.We have three Statements for Shrink the table:

-- Recover space and amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE;

-- Recover space, but don't amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE COMPACT;

-- Recover space for the object and all dependent objects.
ALTER TABLE scott.emp SHRINK SPACE CASCADE;

This process occurs in two stages:
The first stage compacts the segment by shifting rows down to free up blocks at the start. The second stage adjusts the high watermark, requiring a brief exclusive table lock in Oracle.

Table shrinking advantage:
-will adjust the high watermark
-Can be done online
-Will cause only rowlocks during the operation and just a very short full table lock at the end of the operation
-Indexes will be maintained and remain usable
-Can be made in one go
-Can be made in two steps

5. Disable the row movement.

Script to generate script for shrink all the tables in a schema

SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF

-- Change this to your schema/owner
DEFINE OWNER = 'YOUR_SCHEMA';

SELECT
    'ALTER TABLE ' || owner || '.' || table_name || ' ENABLE ROW MOVEMENT;' || CHR(10) ||
    'ALTER TABLE ' || owner || '.' || table_name || ' SHRINK SPACE CASCADE;' || CHR(10) ||
    'ALTER TABLE ' || owner || '.' || table_name || ' DISABLE ROW MOVEMENT;' || CHR(10)
FROM dba_tables
WHERE owner = UPPER('&OWNER')
ORDER BY table_name;

SET FEEDBACK ON VERIFY ON

Note: This can help if you can’t lock the entire table during some hours. You can start with the first step and adjust the high watermark later when it’s easier.

The following restrictions apply to table shrinking:

1. It is only possible in table spaces with ASSM.

2. You cannot shrink:
– UNDO segments
– temporary segments
– clustered tables
– tables with a column of datatype LONG
– LOB indexes
– IOT mapping tables and IOT overflow segments
– tables with MVIEWS with ON COMMIT
– tables with MVIEWS which are based on ROWIDs

Refer:

Optimizing Database disk space using Alter table shrink space/move compress