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;
