The Segment Shrink Functionality in Oracle 10g

The Segment Shrink Functionality in Oracle 10g

Rating:

Oracle 10g is a revolutionary product in Oracle's grid computing world. Oracle 10g is a DBMS, which can automate many database tasks. It follows the Oracle's 9i platform; the letter 'g' in Oracle 10g signifies grid computing.

The DBA has the most critical and responsible task to be performed in any enterprise. Earlier, creating a database and maintaining the information in it occupied most of the time of a DBA. But now, he is more engrossed with managing the space for a database.

One of the most challenging tasks of a DBA is to manage the space allocated to segments for a database. It is very difficult to predict the space requirement for any object; the segment space may get overloaded. The overloading of the space may result in a lot of issues, out of which the most critical one is performance degradation of a database and the wastage of space. However, Oracle 10g has taken away the headache of a DBA to some extent by managing the data and metadata overhead. The Oracle 10g software is also capable of predicting the size of the schema objects before they have been created. A new feature called Segment Shrink Functionality has been introduced in Oracle 10g. It helps in managing the unused space in a table.

Introduction to the Segment Shrink Functionality

The Segment Shrink Functionality is a feature available in Oracle 10g. It gives the DBA the ability to reclaim space from a segment by shrinking it. The article further discusses the Segment Shrink Functionality in detail.

Some of the key points about the segment shrink operation covered in the article are given below:

• The segment shrink operation is called the in-place reorganization of data.
• Segment shrink is an online operation.
• Segment shrink is a restartable operation.

A segment shrink operation allows a database user to use the unused space assigned to some other database objects. Therefore, it improves the performance of the queries and DML operations. Segments that have undergone a lot of data manipulation language activities, such as UPDATE and DELETE, become sparsely populated with chunks of free space within the data blocks. The Segment Advisor goes along with the segment shrink operation.

Definition of Segment Advisor

Segment Advisor is a built-in mechanism to incrementally track space utilization in every segment in a database. In other words, Segment Advisor is an ADDM diagnostic advisor that determines whether an object needs a shrink operation. The space released after a shrink operation is then returned to the database for use by other database objects. As a result, the total number of I/O operations to access these database objects are reduced, thereby improving the performance of SQL statements in this manner.

Segment Advisor analyzes the specified tablespace or a database object that requires the shrink operation. The segment shrink operation is also known as in-place reorganization of data. The segment shrink operation is performed in two steps as mentioned below:

1. Compact the space used in a segment, which will push down the High Water Mark (HWM).
2. Deallocate the space from the segment.

The deallocated space is returned to the tablespace and is therefore available to other objects in the tablepsace.

Definition of High Water Mark

The High water mark acts as an indicator within a data segment. It demarcates the used and the unused space, or space that has been formatted for user data. The space below this mark cannot be released even if there is no user data in it.

Segment shrink is an online operation, which means that the table will be available to the database user to fire queries and issue DML statements.

Note: Segment shrink can be performed only on tables with row movement available.
The database objects that are applicable for a segment shrink operation are given below:

• Heaps
• Materialized views
• Indexes with row movement

The syntactical construction to perform a segment shrink is mentioned below:

ALTER TABLE/INDEX......... ENABLE ROW MOVEMENT;

ALTER TABLE/INDEX.........SHRINK SPACE [CASCADE]/[COMPACT];

Here, the dots have been left for either the table name or the index name. CASCADE and COMPACT are the options used with the shrink operation.

Note: If the COMPACT option is not used with the ALTER TABLE command, then the shrink operation compacts the space and returns the excess space to the tablespace. On the other hand, if the COMPACT option is used, then the shrink operation will compact only the space.

The image given below helps you understand the shrink operation of tablespaces owned by different users. The image shows the segment name, the space allocated to the segment, the used space, the reclaimable space, and recommendations such as "Perform shrink, estimated savings is 14284326 bytes."



Special Case

If a database user interrupts the compaction of space done by the shrink operation, then whatever compaction has been performed is saved in the database. However, if the segment shrink operation is resumed, then the operation will pick up from where it left off. Therefore, a segment shrink operation is a restartable operation.

Enterprise Management Database Control can also be used to perform a segment shrink operation either by directly selecting a table/index or by following the recommendations provided by the Segment Advisor.

Disadvantage of the Traditional Method of Reclaiming the Segment Space

Another alternative to reclaim the unused segment space is to drop the table, recreate it, and load the data again. The ALTER TABLE MOVE command can also be used to move the table to a different tablespace. However, the disadvantage is that the entire operation is done by taking the table offline. The segment shrink functionality has overcome the drawback of traditionally reclaiming the space.

This article helps you in understanding the fundamental functionality of a segment shrink operation.

Rating: