Automated Rebuild Indexes, Analyze Objects and Gather Statistics | Oracle

Here, I am going to share a way to automate Index Rebuild, Analyze Objects and Gather Statistics jobs.
Stored Procedure to Rebuild All indexes in a specific Schema:


CREATE OR REPLACE PROCEDURE SYS.REBUILD_TABLE_INDEX (SCHEMANAME VARCHAR2)
AS
SQL_STR VARCHAR2(1000);
BEGIN

    FOR CUROSR_INDEX IN (SELECT OWNER,SEGMENT_NAME from SYS.DBA_SEGMENTS
        WHERE SEGMENT_TYPE='INDEX'
        AND OWNER = SCHEMANAME
        ORDER BY SEGMENT_NAME ) LOOP

        BEGIN
        SQL_STR:='ALTER INDEX '||CUROSR_INDEX.OWNER||'.'||CUROSR_INDEX.SEGMENT_NAME ||' REBUILD ONLINE NOLOGGING PARALLEL 8';        
        EXECUTE IMMEDIATE SQL_STR;
        DBMS_OUTPUT.PUT_LINE('SUCCESS : '|| CUROSR_INDEX.OWNER||'.'||CUROSR_INDEX.SEGMENT_NAME);
        EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERROR : '||CUROSR_INDEX.OWNER||'.'||CUROSR_INDEX.SEGMENT_NAME||':'||SQLERRM);
        END;
 
END LOOP;
END;


Following one is the SQL script (test.sql) to get all those jobs done.


conn / as sysdba
select name from v$database;
set serveroutput on;
set timing on;
set time on;
exec REBUILD_TABLE_INDEX('APR23FE');
exec dbms_utility.analyze_schema('APR23FE','ESTIMATE', NULL, 50, 'FOR TABLE');
exec dbms_utility.analyze_schema('APR23FE','ESTIMATE', 50, NULL,'FOR ALL INDEXES');
exec dbms_stats.gather_schema_stats('APR23FE', estimate_percent => 50, method_opt => 'for all columns size auto', degree => 12, granularity => 'auto', cascade => true, options=>'gather');
exit


Use this shell Script (test.sh) to execute above SQL Command Set and get the output in another file as job log.


#!/usr/bin/bash
export ORACLE_SID=testdbdp
export ORACLE_BASE=/oracle/app2
export ORACLE_HOME=/oracle/app2/11gr2/db_1
export Date=`date +%d%b%Y`.`date +%I`.`date +%M%p`
sqlplus /nolog @test.sql >> DBMaintenace_$Date.txt


You can create a corn job using above shell script (here test.sh) to get this maintenance job done without any manual intervention.

Thanks
A Rahim Khan

Advertisements
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: