Drop recreate indexes in procedure or DBMS JOB?
656919Sep 3 2008 — edited Sep 5 2008I currently have some simple 10g scheduled jobs that I want to enhance by dropping/recreating the indexes. Simply putting the extra statements in the Scheduled Job do not work. Sooooo.... I thought I would write a stored procedure that does the same thing, then call that from the scheduled job. Here is what I am trying to do but I get errors when trying to compile the SP.
I would appreciate some direction on how to do these operations. Thanks in advance,
Mike
/**
SP to clean old records.
1. first disable or drop indexes
2. do the delete
3. re-enable and rebuild indexes.
*/
CREATE OR REPLACE PROCEDURE SP_JOB_EXP_DELETE_1 AS
BEGIN
--ALTER TABLE EMS.JOB_EXAMPLE DISABLE CONSTRAINT EMS.JOB_EXAMPLE_PK;
alter index EMS.JOB_EXAMPLE_PK unusable;
DELETE FROM "EMS"."JOB_EXAMPLE" where EVENT_TS <= current_date - 30;
--ALTER TABLE EMS.JOB_EXAMPLE ENABLE CONSTRAINT EMS.JOB_EXAMPLE_PK;
alter index EMS.JOB_EXAMPLE_PK rebuild;
END;