shrink.sql

REM -----------------------------------------------------------------------------------------------
REM Name                : shrinksegs.sql
REM Compatible versions : 10.x and above
REM Description         : This script will initiate segment shrink for the TOP-5 Tables in reverse
REM                       order.
REM Note                : For some 10.x versions, If the UNDO Tablespace is not present in view
REM                       dba_tablespace_usage_metrics, it will calculate on dba_data_files and
REM                       dba_free_space
REM -----------------------------------------------------------------------------------------------
REM
------------------
-- Set SQL prompt:
------------------
whenever sqlerror exit;
set pagesize 60 lines 1000 serveroutput on feedback off echo off verify off
execute dbms_output.enable(1000000);
------------------------
-- Start segment shrink:
------------------------
DECLARE
  v_starttime   number := dbms_utility.get_time;
  v_owner       dba_segments.owner%TYPE := 'PERFSTAT';
  v_usercnt     NUMBER;
  v_maxundo     NUMBER := Nvl(Trim(&&maxundo),90);
  v_undo_pct    NUMBER;
  v_objcnt      NUMBER;
  v_tbscnt      NUMBER;
  v_noundo      EXCEPTION;
  v_nouser      EXCEPTION;
  v_invalidpct  EXCEPTION;
BEGIN
  SELECT Count(* )
  INTO   v_usercnt
  FROM   dba_users
  WHERE  username = v_owner;
  IF (v_usercnt = 0) THEN
    RAISE v_nouser;
  END IF;
  IF (v_maxundo < 0
       OR v_maxundo > 100) THEN
    RAISE v_invalidpct;
  END IF;
  SELECT Count(*)
  INTO   v_tbscnt
  FROM   dba_tablespace_usage_metrics
  WHERE  tablespace_name = (SELECT VALUE
                            FROM   v$parameter
                            WHERE  NAME = 'undo_tablespace');
  dbms_output.Put_line(Chr(10));
  FOR i IN (SELECT   owner,
                     segment_name
            FROM     (SELECT   owner,
                               segment_name,
                               Sum(bytes) tabsize
                      FROM     dba_segments
                      WHERE    owner = v_owner
                               AND segment_type = 'TABLE'
                      GROUP BY owner,
                               segment_name,
                               segment_type
                      ORDER BY Sum(bytes) DESC)
            WHERE    ROWNUM <= 5
            ORDER BY tabsize)
  LOOP
    IF (v_tbscnt <> 0) THEN
      SELECT Nvl(Round(used_percent),0)
      INTO   v_undo_pct
      FROM   dba_tablespace_usage_metrics
      WHERE  tablespace_name = (SELECT VALUE
                                FROM   v$parameter
                                WHERE  NAME = 'undo_tablespace');
    ELSE
      SELECT Decode(Nvl(a.maxbytes,0),0,0,Round(((Nvl(a.bytes,0) - Nvl(b.bytes,0)) / Nvl(a.maxbytes,0)) * 100,2)) pct_on_max
      INTO   v_undo_pct
      FROM   (SELECT   tablespace_name   tbs,
                       Nvl(Sum(bytes),0) bytes,
                       Nvl(Sum(CASE
                                 WHEN (Decode(maxbytes,0,bytes,maxbytes) >= bytes)
                                 THEN Decode(maxbytes,0,bytes,maxbytes)
                                 ELSE bytes
                               END),0) maxbytes
              FROM     dba_data_files
              GROUP BY tablespace_name) a,
             (SELECT   tablespace_name   tbs,
                       Nvl(Sum(bytes),0) bytes
              FROM     dba_free_space
              GROUP BY tablespace_name) b
      WHERE  a.tbs = b.tbs (+)
             AND a.tbs = (SELECT UPPER(VALUE)
                          FROM   v$parameter
                          WHERE  NAME = 'undo_tablespace');
    END IF;
    IF (v_undo_pct < v_maxundo) THEN
      dbms_output.Put_line('INFO : Current UNDO usage : '
                           ||v_undo_pct
                           ||'%');
      EXECUTE IMMEDIATE 'alter table '
                        ||i.owner
                        ||'.'
                        ||i.segment_name
                        ||' enable row movement';
      EXECUTE IMMEDIATE 'alter table '
                        ||i.owner
                        ||'.'
                        ||i.segment_name
                        ||' shrink space cascade';
      EXECUTE IMMEDIATE 'alter table '
                        ||i.owner
                        ||'.'
                        ||i.segment_name
                        ||' disable row movement';
      dbms_output.Put_line(Chr(10)
                           ||'INFO : Table '
                           ||i.owner
                           ||'.'
                           ||i.segment_name
                           ||' shrinked successfully'
                           ||Chr(10));
    ELSE
      RAISE v_noundo;
    END IF;
  END LOOP;
  dbms_output.Put_line(Chr(10));
  dbms_utility.Compile_schema(SCHEMA => v_owner,compile_all => false);
  dbms_output.Put_line(rpad('-',length('INFO : Compilation successfully completed for '||v_owner),'-'));
  dbms_output.Put_line('INFO : Compilation successfully completed for '||v_owner);
  SELECT Count(object_name)
  INTO   v_objcnt
  FROM   dba_objects
  WHERE  owner = v_owner
         AND status <> 'VALID';
  dbms_output.Put_line('INFO : Invalid object count in '
                       ||v_owner
                       ||' schema : '
                       ||v_objcnt);
  dbms_output.Put_line(rpad('-',length('INFO : Compilation successfully completed for '||v_owner),'-'));
  dbms_output.Put_line(Chr(10));
  dbms_output.put_line('INFO : Execution Time : '||round((dbms_utility.get_time-v_starttime)/100,2)||' seconds');
  dbms_output.Put_line(Chr(10));
EXCEPTION
  WHEN v_nouser THEN
    Raise_application_error(-20002,'ERROR! : Invalid user');
  WHEN v_invalidpct THEN
    Raise_application_error(-20003,'ERROR! : Percentage should be in between 0 - 100');
  WHEN v_noundo THEN
    Raise_application_error(-20004,'WARNING! : default UNDO Tablespace usage is '
                                   ||v_undo_pct
                                   ||'% Please try later..');
  WHEN OTHERS THEN
    Raise_application_error(-20050,'Error while executing PL/SQL block !'
                                   ||SQLCODE
                                   ||'-Error-'
                                   ||sqlerrm);
END;
/