Significant amount of data (more than 80%) has been deleted from a database schema. However size of schema has not come down proportionately with the volume of data deleted.
1. Perform Online Segment Shrink
The Online Segment Shrink process consolidates fragmented free space below the high water mark and compacts the segment. After compaction, the high water mark is moved, resulting in new free space above the high water mark. That space above the high water mark is then deallocated.
- Compaction of data leads to better cache utilization, which in turn leads to better online transaction processing (OLTP) performance.
- The compacted data requires fewer blocks to be scanned in full table scans, which in turns leads to better decision support system (DSS) performance.
Segment shrink is an online, in-place operation. DML operations and queries can be issued during the data movement phase of segment shrink. Concurrent DML operation are blocked for a short time at the end of the shrink operation, when the space is deallocated. Indexes are maintained during the shrink operation and remain usable after the operation is complete. Segment shrink does not require extra disk space to be allocated.
- Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM). Within an ASSM tablespace, all segment types are eligible for online segment shrink except these:
- IOT mapping tables
- Tables with rowid based materialized views
- Tables with function-based indexes
- Tables with LONG column
- As with other DDL operations, segment shrink causes subsequent SQL statements to be reparsed because of invalidation of cursors unless you specify the
1.4 Shrink Operation Types
Two optional clauses let you control how the shrink operation proceeds:
COMPACTclause lets you divide the shrink segment operation into two phases. When you specify
COMPACT, Oracle Database defragments the segment space and compacts the table rows but postpones the resetting of the high water mark and the deallocation of the space until a future time. This option is useful if you have long-running queries that might span the operation and attempt to read from blocks that have been reclaimed. The defragmentation and compaction results are saved to disk, so the data movement does not have to be redone during the second phase. You can reissue the
SHRINK SPACEclause without the
COMPACTclause during off-peak hours to complete the second phase.
CASCADEclause extends the segment shrink operation to all dependent segments of the object. For example, if you specify
CASCADEwhen shrinking a table segment, all indexes of the table will also be shrunk. (You need not specify
CASCADEto shrink the partitions of a partitioned table.) To see a list of dependent segments of a given object, you can run the
OBJECT_DEPENDENT_SEGMENTSprocedure of the
<span style="background-color: #ccffff;">ALTER TABLE employees SHRINK SPACE CASCADE;</span>
BASICFILE LOB segment only:
ALTER TABLE employees MODIFY LOB (perf_review) (SHRINK SPACE);
Shrink a single partition of a partitioned table:
<span style="background-color: #ccffff;">ALTER TABLE customers MODIFY PARTITION cust_P1 SHRINK SPACE;</span>
Shrink an IOT index segment and the overflow segment:
<span style="background-color: #ccffff;">ALTER TABLE cities SHRINK SPACE CASCADE;</span>
Shrink an IOT overflow segment only:
<span style="background-color: #ccffff;">ALTER TABLE cities OVERFLOW SHRINK SPACE;</span>
1.6 Space not fully freed after Shrink Operation
It has been observed that in some scenario space is not fully freed from the objects of the schema after shrink is performed.
Also the expected size of the tables and indexes seem to be very high with respect to the amount of data in the tables and indexes.
Check using the following query:
select segment_name,sum(bytes)/1024/1024 size_mb from dba_segments where owner='<owner of the schema/schema name>’ order by sum(bytes)/1024/1024 desc;
To reclaim the space further we need to perform the following:
1.6.1. Perform Index Re-Build
1.6.2. Perform Deallocate unused
It has been observed that the storage clause INITIAL in table definition remains high although space has been reclaimed using shrink and rebuild, which in turn keeps more space than required for tables and indexes of a schema resulting in drastic space usage by the schema.All the delete,row movement, shrink and index rebuild operation seems to have no effect in reducing the size of the schema.
Suppose if a table requires only 2 MB of space after delete,shrink etc. but in the table create definition the storage initial is set to 4 GB
STORAGE(INITIAL 4294967296 ………………………………)
This need to set to some lower value like STORAGE(INITIAL 65536 ……………….) so that space useage by the table, index,schema is reduced.
Also Restriction on INITIAL is that You cannot specify
INITIAL in an
In this scenario we need to use the deallocate unused space feature
When you deallocate unused space, the database frees the unused space at the unused (high water mark) end of the database segment and makes the space available for other segments in the tablespace.
Prior to deallocation, you can run the UNUSED_SPACE procedure of the DBMS_SPACE package, which returns information about the position of the high water mark and the amount of unused space in a segment. For segments in locally managed tablespaces with automatic segment space management, use the SPACE_USAGE procedure for more accurate information on unused space.
The following statements deallocate unused space in a segment (table, index or cluster):
ALTER TABLE table DEALLOCATE UNUSED KEEP integer;
ALTER INDEX index DEALLOCATE UNUSED KEEP integer;
ALTER CLUSTER cluster DEALLOCATE UNUSED KEEP integer;
The KEEP clause is optional and lets you specify the amount of space retained in the segment. You can verify that the deallocated space is freed by examining the DBA_FREE_SPACE view.
- Shrink the table with cascade clause
- Rebuild Index
- Deallocate unused space with deallocate command which in turn resets the INITIAL clause in Storage clause of Table Definition
Oracle. (2013). Altering Indexes. Available: http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes004.htm#i1006864. Last accessed 08-Apr-2013.
Oracle. (2013). Reclaiming Wasted Space. Available: http://docs.oracle.com/cd/B28359_01/server.111/b28310/schema003.htm. Last accessed 8-Apr-2013.
Oracle. (2013). storage_clause. Available: http://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses009.htm. Last accessed 08-Apr-2013.