Shrink segment reduces high water level

Source: Internet
Author: User

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.

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.