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