Sharing Oracle Database Learnings

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;

Continue reading →