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.