Support for online segment shrinkage: LOB segment and IOT overflow segment;
The benefits of CIDR Block shrinkage are as follows:
Better Cache Usage and improved OLTP performance;
Reduce the number of full table scans and improve the performance of DSS;
CIDR blocks are online and indexes are maintained during CIDR blocks. No additional disk space is required.
Segment shrinkage to reclaim unused space below HWM and above, while space release only releases unused space above high water level. By default, CIDR blocks are compressed, and the high water level is adjusted to free up space.
Before using segment contraction, you must use alter table... enable row movement to enable row movement, because segment contraction requires rows to be moved to a new position.
Segment shrinkage can only be performed on the local ASSM tablespace. All segments in ASSM can be shrunk except the following:
IOT ing table;
Materialized view is based on ROWID;
Tables with function indexes;
Call segment Shrinkage
Check the discovery and recommendation of the consultant before the call segment shrinks. Use the shrink space clause of alter table, alter index, alter materialized view, and alter materialized view log statements;
Two optional clauses control the processing of the contraction operation:
COMPACT: Oracle removes fragments and compresses data, but suspends HWM resetting and releases space. The shrink space can be executed without COMPACT during off-peak hours to complete the second stage.
CASCADE: use this clause to scale the CIDR block to all dependent objects. You can run OBJECT_DEPENDENT_SEGMENTS of DBMS_SPACE to view all the segments dependent on the given object.
Alter table employees shrink space cascade;
Alter table employees modify lob (perf_review) (shrink space );
Alter table MERs modify partition cust_P1 shrink space;
Alter table cities shrink space cascade;
Alter table cities overflow shrink space;
Oracle 10g
Syntax:
Alter table <table_name> shrink space [<null> | compact | cascade];
Alter table <table_name>Shrink space compcat;
Shrink the table, but keep the high water mark; (How do you understand this? Is it equivalent to not shrinking back ?)
It is equivalent to harden the data in the block. No hwm changes
Alter table <table_name>Shrink space;
Shrink the table to lower the high water mark;
Alter table <table_name>Shrink space cascade;
Scale down the table to lower the high water mark, and the related indexes must also contract down.