How To: Clean Your Schema I'm going to be testing my migration script in my personal sandbox. I just want to make sure I have the exact order of execution for all the objects before sending it on to the DBA.

In that regard, I had to clean up my existing schemas. By "clean up" I mean remove all of the objects. Laurent Schneider posted something very similar last year, but it didn't handle scheduling objects (there is a reference to scheduled jobs in the comments though).

So here's my version which handles TYPES, JOBS (Scheduler), PROGRAMs, CHAINs and TABLEs. There are probably more cases that I did not catch, but this is the most I have come across so far.

DECLARE
  l_string VARCHAR2(4000);
  l_execute BOOLEAN := TRUE;
BEGIN
  FOR i IN ( SELECT DISTINCT
               owner, 
               ( CASE
                   WHEN object_type = 'PACKAGE BODY' THEN 'PACKAGE'
                   ELSE object_type
                 END ) object_type, 
               object_name
             FROM dba_objects
             WHERE owner IN ( 'MY_SCHEMA' )
              AND object_type IN ( 'SEQUENCE', 'VIEW', 'SYNONYM', 
                                   'PROCEDURE', 'FUNCTION', 'PACKAGE', 
                                   'JAVA SOURCE', 'JAVA CLASS', 'TYPE',
                                   'JOB', 'SCHEDULE', 'PROGRAM',
                                   'JAVA RESOURCE', 'CHAIN', 'TABLE' )
             ORDER BY owner, object_name )
  LOOP
    l_string := 'DROP ';
    l_string := i.object_type || ' ';
    l_string := i.owner || '."';
    l_string := i.object_name || '"';
    CASE
      WHEN i.object_type = 'TABLE' THEN
        l_string := l_string || ' CASCADE CONSTRAINTS';
      WHEN i.object_type = 'TYPE' THEN
        l_string := l_string || ' FORCE';
      WHEN i.object_type = 'JOB' THEN
        l_execute := FALSE;

        dbms_scheduler.drop_job
          ( job_name => i.owner || '."' || i.object_name || '"',
            force => TRUE );
      WHEN i.object_type = 'SCHEDULE' THEN
        l_execute := FALSE;
        
        dbms_scheduler.drop_schedule
          ( schedule_name => i.owner || '."' || i.object_name || '"',
            force => TRUE );
      WHEN i.object_type = 'PROGRAM' THEN
        l_execute := FALSE;
        
        dbms_scheduler.drop_program
          ( program_name => i.owner || '."' || i.object_name || '"',
            force => TRUE );
      WHEN i.object_type = 'CHAIN' THEN
        l_execute := FALSE;
        
        dbms_scheduler.drop_chain
          ( chain_name => i.owner || '."' || i.object_name || '"',
            force => TRUE );
      ELSE
        NULL;
    END CASE;
    
    IF l_execute THEN
      EXECUTE IMMEDIATE l_string;
    ELSE
      l_execute := TRUE;
    END IF;
  END LOOP;
END;
/

Enjoy.

Labels: development, howto, testing

  ¶ 9/17/2009 01:47:00 PM