Solution : https://service.sap.com/sap/support/notes/910389 (SAP Service marketplace login required)
Summary :
Segment shrinking is a method to recover unused space in Oracle databases without reorganizing tables by compacting the data within a segment. This process, suitable for Oracle 10g or higher versions, is designed for tables in ASSM tablespaces, excluding those with LONG or LONG RAW types, mapping tables, overflow segments of IOTs, and compressed tables. Shrinking is advantageous over reorganization as it does not require additional space, causes less system load due to reduced redo log data generation, and can be executed online without downtime. The procedure involves enabling ROW MOVEMENT on the table, executing the SHRINK SPACE command, and optionally CASCADE for associated indexes. Common issues during this operation include potential table locks and LOB corruptions, which require particular precautionary measures outlined in specific Oracle notes. A successful shrink might demand a repeated process to optimize the reclaimed space effectively.
Key words :
alter table <table_name> shrink space [compact] [cascade], alter table <table_name> disable row movement, alter table <table_name> enable row movement, terms frequently asked questions solution 1, set serveroutput ondeclare vunf number, online reorganization temporarily requires double, enabled activate row movement, row-specific tx enqueue, involves moving table entries, 10 mb table lasted 51 seconds
Related Notes :
| 1121838 | SELECT on partition hangs if a shrink compact is running |
| 1021454 | Oracle Segment Shrinking may cause LOB corrupt data |
| 927813 | Oracle 10g: Using Oracle Segment Advisor to optimize space |
| 832343 | FAQ: Clustering factor |
| 828268 | Oracle Database 10g: New functions |
| 825653 | Oracle: Common misconceptions |
| 821687 | FAQ: Space utilization and fragmentation in Oracle |
| 771929 | FAQ: Index fragmentation |
| 745639 | FAQ: Oracle enqueues |
| 541538 | FAQ: Reorganization |