4. Cleanup Test Database
This procedure SCHEMA_REFRESH.CLEANUP, recompiles invalid objects that may have become invalid do to the import. It also drops scheduled jobs because they cannot be excluded from the export; therefore, it must be dropped after import; aee Listing 4.
procedure cleanup as begin drop_jobs; validate_objects; end cleanup; procedure drop_jobs as begin -- -- just add a new entry for each schema -- v_schema.delete(1); v_schema(1) := 'R11'; FOR i IN v_schema.FIRST..v_schema.LAST LOOP FOR t IN (SELECT owner, job_name FROM all_SCHEDULER_JOBS WHERE owner = upper( v_schema(i) ) ) LOOP DBMS_SCHEDULER.DROP_JOB (job_name => t.owner|| '.' || t.job_name); END LOOP; end loop; end drop_jobs; procedure validate_objects as v_stmt varchar2(200); begin FOR t IN (select owner, object_name, object_type from all_objects where owner in ('R11','WEB_USER') and status = 'INVALID' AND OBJECT_TYPE != 'SYNONYM') LOOP do_compile( t.owner, t.object_name, t.object_type); END LOOP; end validate_objects; CREATE OR REPLACE procedure do_compile( p_owner varchar2, p_object_name varchar2, p_object_type varchar2) as begin EXECUTE IMMEDIATE 'alter '||p_object_type||' '|| p_owner || '.' || p_object_name||' compile'; exception when others then null; end do_compile;
5. Send Email
The process is handled directly from: DBMS_SCHEDULER.CREATE_PROGRAM. This step will be explained later.
According to Oracle "The DBMS_SCEDULER package provides a collection of scheduling functions and procedures that are callable from any PL/SQL program." Go here for details on DBMS_SCEDULER.
Note: To administer job scheduling you need the privileges belonging to the SCHEDULER_ADMIN role. To create and run jobs in your own schema, you need the 'CREATE JOB' privilege.