Segment Availability during Shrink

The segment shrink is done online. During shrink operation, the conventional DML operations can coexist but parallel DMLs cannot.

During segment shrink, data will be moved as part of the compaction phase.
During compaction locks will be held on individual rows and/or blocks containing the data. This will cause the concurrent DMLs like updates and deletes to serialize on the locks. The compaction will be done in units of smaller transactions, so the availability of the object will not be impacted significantly.

However during certain phases of segment shrink (when the HWM is adjusted), the segment will have to be locked in exclusive mode.

This phase is for a very short duration and should impact the availability of the object less significantly.

NOTE: Shrinking space of a large segment can take a lot of time, e.g. tens of hours, and can generate lots of redo. Therefore, it is not advised to interrupt a shrink statement to prevent a possibly massive rollback.


Thanks

Segment Shrink Restrictions

Init.ora Parameter compatible >= 10.0

The tablespace must be Locally Managed with Automatic Segment Space Management (ASSM) enabled.

You cannot shrink:

• UNDO segments
• Temporary segments
• Clustered tables and Compressed tables
• Tables with a colmn of datatype LONG
• LOB segments (belwo 10.2)
• Tables with Function based Indexes or Bitmap Join Indexes
• IOT mapping tables and IOT overflow segments
• Tables with domain Indexes (below 11.1)
• Tables with MVIEWS with ON COMMIT
• Tables with MVIEWS which are based on ROWIDs

Incase of tables with function based indexes or bitmap join indexes, drop the index the shrink and then recreate the index.

The following query will identify the segments that are shrinkable and non-shrinkable...


SELECT dt.owner, dt.table_name,
(CASE WHEN NVL(ind.cnt, 0) < 1 THEN 'Y' ELSE 'N'END) AS can_shrink
FROM dba_tables dt,
(SELECT table_name, COUNT(*) cnt
FROM dba_indexes di
WHERE index_type in ( 'FUNCTION-BASED NORMAL','FUNCTION-BASED DOMAIN','DOMAIN')
GROUP BY table_name) ind
WHERE dt.table_name = ind.table_name(+)
AND dt.table_name NOT LIKE 'AQ$%'
AND dt.table_name NOT LIKE 'BIN$%'
AND dt.owner = '&owner'
ORDER BY 1, 2;

Note: Change the values of index type according to the version you are running...

Thanks

Segment Shrinking

Starting with ORACLE 10gR1 we can use a new feature for adjusting the high watermark, it is called segment shrinking and is only possible for segments which use Automatic Segment Space Management and locally managed.

Steps to perform segment shrink.

Step 1: Enable Row movement for the segment

SQL> ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

Step 2: Recover space and amend the high water mark (HWM).

SQL> ALTER TABLE scott.emp SHRINK SPACE;

Step 2a: Recover space, but don't amend the high water mark (HWM).

SQL> ALTER TABLE scott.emp SHRINK SPACE COMPACT;

Step 3: Recover space for the object and all dependant objects.

SQL> ALTER TABLE scott.emp SHRINK SPACE CASCADE;

Note: The COMPACT option allows the shrink operation to be broken into two stages. First the rows are moved using the COMPACT option but the HWM is not adjusted so no parsed SQL statements are invalidated. The HWM can be adjusted at a later date by reissuing the statement without the COMPACT option. At this point any dependant SQL statements will need to be reparsed.

Thanks