Reclaiming a 1.6TB LOB Segment Under a Six-Hour Window
A 4.2TB OLTP schema contains a 1.6TB BASICFILE LOB segment in a dedicated tablespace with 8KB blocks and 1MB uniform extents. Over a year, 60% of the parent rows were deleted, but the LOB segment is still 1.6TB and a SHRINK SPACE attempt on the parent table reclaimed almost nothing for the LOB. The tablespace datafile is autoextend-enabled with MAXSIZE 32GB and has just hit the limit, so inserts now fail with ORA-01653. The filesystem has 300GB free. The parent table's indexes show 40% deleted leaf blocks. The maintenance window is six hours, and the LOB must remain online and readable throughout.
1. Why did SHRINK SPACE fail to reclaim the LOB, and what online strategy would you use?
Read the worked answer
ALTER TABLE ... SHRINK SPACE shrinks the table segment; it does not automatically shrink an out-of-line LOB segment. The LOB has its own segment and LOB index, so the table shrink can succeed while the LOB remains at 1.6TB.
Check the LOB's retention behavior first. For BasicFile LOBs, PCTVERSION or RETENTION can keep old LOB versions, preventing space reuse. Query DBA_LOBS and the LOB's retention settings. If retention is the cause, changing it may allow space to be reused, but it can break readers that depend on older versions.
Online strategy: use DBMS_REDEFINITION to create an interim table with a SecureFile LOB, copy data with online redefinition, and keep the original readable throughout. SecureFiles provides better space management, compression, and deduplication. This requires free space roughly equal to the new LOB segment plus undo and redo, so 300GB may not be enough. If space is insufficient, move the LOB to a new tablespace in batches or schedule a longer window.
Do not start with an offline move; it violates the online requirement and a 1.6TB copy will not finish in six hours. Validate the result with DBMS_SPACE and check the LOB index after redefinition.
2. How do you resolve the ORA-01653 immediately, and what is wrong with the current autoextend configuration?
Read the worked answer
ORA-01653 means the tablespace cannot allocate another extent. The datafile has autoextend enabled but MAXSIZE 32GB, and it has reached that ceiling, so autoextend cannot help.
Immediate fix: add a new datafile to the tablespace, or resize the existing one if the filesystem has room. With 300GB free, adding a datafile of, say, 20GB with autoextend and a sensible maximum restores inserts quickly. Use a size that leaves headroom for the six-hour operation and for normal growth.
What is wrong: a 32GB cap on a 1.6TB segment is arbitrary and too low for this workload; it turns a space problem into an outage. Also, a single datafile with a hard maximum creates a single point of failure for growth. The tablespace should have multiple datafiles, or be a bigfile tablespace with a large maximum and monitoring.
Verify: after adding the file, confirm DBA_DATA_FILES shows the new file and DBA_FREE_SPACE shows usable extents. Then set a tablespace alert at 80% and 90% so the next growth event is caught before inserts fail. Do not set MAXSIZE UNLIMITED without confirming the filesystem and backup capacity.
3. The indexes show 40% deleted leaf blocks. How do you decide whether to rebuild, coalesce, or shrink, and how does that fit the six-hour plan?
Read the worked answer
Deleted leaf blocks do not automatically justify a rebuild. First validate the index with ANALYZE INDEX ... VALIDATE STRUCTURE and inspect INDEX_STATS for DEL_LF_ROWS, LF_ROWS, and BLEVEL. If the index is still efficient and the deleted blocks will be reused, a shrink or coalesce may be enough.
Options:
ALTER INDEX ... SHRINK SPACEcompacts the index and releases space at the end; it requires ASSM and takes an exclusive lock briefly, so it is not fully online.ALTER INDEX ... COALESCEmerges adjacent free blocks but does not release space to the tablespace.ALTER INDEX ... REBUILD ONLINEcreates a new segment and needs temporary space roughly equal to the final index size; it is online for DML but can be expensive.
Fit to the window: prioritize the LOB redefinition and the datafile fix. If index rebuilds are needed, do them after the LOB work and only for indexes with proven inefficiency. Rebuilding every index because 40% of leaf blocks are deleted is a waste of the window and increases risk. Validate performance after the window with representative queries.