Archive for June 12th, 2007

Yesterday, we had posted on online segment shrinking feature in Oracle 10g. After learning about that feature, the next logical question is to identify which segments are available for shrinking. This typically requires familiarity with the application since the segments with a lot of DML operations are going to be candidates for this operation. DBAs typically write their SQL or PL/SQL scripts to help identify such segments. One easy way of doing it is to use the Oracle supplied PL/SQL package: DBMS_SPACE. This package contains many sub-programs (procedures and functions) and one of those is VERIFY_SHRINK_CANDIDATE which tells us whether we can shrink the segment to specific size (in bytes) or not.

DBMS_SPACE.VERIFY_SHRINK_CANDIDATE takes 5 arguments and returns Boolean. Following is the signature of the function.

dbms_space.verify_shrink_candidate
(
segment_owner IN VARCHAR2, — Schema name in which segment resides.
segment_name IN VARCHAR2, — Name of the segment.
segment_type IN VARCHAR2, — Type of the segment(Table, Index etc.)
shrink_target_bytes IN NUMBER, — Size in bytes
partition_name IN VARCHAR2 DEFAULT NULL
)
RETURN BOOLEAN;

If the function returns true, then the specified segment is shrinkable otherwise it is not. Example:

Here we are verifying whether we can shrink the segment ‘TEST’ to 20000 bytes or not and answer is “No”. One can further enhance this script by putting the logic in a stored procedure and looping through the code for each and every table by passing in the target bytes to help identify the tables that are candidates for shrinking.