Systems Engineering and RDBMS

DBMS_SPACE.VERIFY_SHRINK_CANDIDATE

Posted by decipherinfosys on June 12, 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:

SET SERVEROUTPUT ON

DECLARE X VARCHAR(3);
BEGIN

X := CASE
WHEN dbms_space.verify_shrink_candidate(‘DECIPHER’,’TEST’,’TABLE’,200000) THEN ‘Yes’
ELSE ‘No’
END;

dbms_output.put_line(‘ X = ‘|| X);

END;
/
X = No
PL/SQL procedure successfully completed.

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.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: