Script: Empty schema

This PL/SQL script can be used to remove all objects of one ore more schemas. This might be used in preparation of an DataPump import.

Features

• The script removes all objects in a schema
• Multiple schemas can be configured
• A dryrun option shows the statements without running them

Limitations

• None yet

Usage

• Connect as a database user with enough privileges to remove the objects (schema owner or sysdba)
• Adjust the schema name(s) in the script
• Maybe: Run the script with l_dryrun_on := true to just show the commands without performing any removal of objects
• Run the script (e.g. in SQL*Plus, SQLcl or SQL Developer)
• If not all objects are removed in the first run, just run it again.
• As always: run the script on a test system first to verify if it works as intended (and let me know if it doesn’t)

Script

set serverout on
declare
  type array_t is table of varchar2(128);
  l_schemas array_t := array_t('SCHEMA1','SCHEMA2');  -- Enter schemas here
  l_dryrun boolean := false;                          -- Set to true to show SQL commands without removing objects
  l_param varchar2(16) := '';
  object_does_not_exist exception;
  pragma exception_init(object_does_not_exist, -4043);
  table_or_view_does_not_exist exception;
  pragma exception_init(table_or_view_does_not_exist, -942);
  
procedure print_stats(i_schema varchar2, i_text varchar2) is
begin
  if (not l_dryrun or (l_dryrun and i_text = ' before'))
  then
    dbms_output.put_line('');
    dbms_output.put_line('===> Number and type of objects in schema '||i_schema||i_text||' removal:');
    for r1 in (select object_type,count(*) cnt  from all_objects where owner = i_schema group by object_type)
    loop
      dbms_output.put_line(rpad(r1.cnt, 7)||r1.object_type);
    end loop;
    dbms_output.put_line('');
  end if;
end;

procedure run_statement(i_stmt varchar2) is
begin
  if l_dryrun then
    dbms_output.put_line(i_stmt||';');
  else
    execute immediate i_stmt;
  end if;
  exception
    when object_does_not_exist or table_or_view_does_not_exist then
      null;
    when others then
      dbms_output.put_line('Failed to run statement: '||i_stmt||'   Error: '||SQLERRM);
end;

procedure remove_schema_objects(i_schema varchar2) is
begin
  for queue in (select name qname from all_queues where owner = i_schema)
  loop
    run_statement('begin dbms_aqadm.stop_queue(''"'||i_schema||'"."'||queue.qname||'"''); end;');
    run_statement('begin dbms_aqadm.drop_queue(''"'||i_schema||'"."'||queue.qname||'"''); end;');
  end loop;
  
for qtab in (select queue_table from all_queue_tables where owner = i_schema)
  loop
    run_statement('begin dbms_aqadm.drop_queue_table(''"'||i_schema||'"."'||qtab.queue_table||'"''); end;');
  end loop;

  for mv in (select object_name from all_objects where owner = i_schema and object_type = 'MATERIALIZED VIEW')
  loop
    run_statement('drop materialized view "'||i_schema||'"."'||mv.object_name||'"');
  end loop;
  
  for tab in (select object_name from all_objects where owner = i_schema and object_type = 'TABLE'
    and object_name not in (select mview_name from all_mviews where owner = i_schema)
    and object_name not in (select table_name from all_tables where owner = i_schema and (iot_type = 'IOT_OVERFLOW' or nested = 'YES')))
  loop
    run_statement('drop table "'||i_schema||'"."'||tab.object_name||'" cascade constraints purge');
  end loop;
  
  for obj in (select object_name, object_type from all_objects where owner = i_schema
    and object_type not in ('MATERIALIZED VIEW','TABLE','PACKAGE BODY','INDEX','TRIGGER','LOB','JOB','SCHEDULE')
    and object_type not like '%PARTITION%'
    order by decode (object_type, -- specifies the order of removal
      'TYPE', 2,
      1))
  loop
    if obj.object_type in ('OPERATOR','TYPE','INDEXTYPE')
    then
      l_param := 'force';
    else
      l_param := '';
    end if;
    run_statement('drop '||obj.object_type||' "'||i_schema||'"."'||obj.object_name||'" '||l_param);
  end loop;
  
  for trg in (select object_name, object_type from all_objects where owner = i_schema and object_type = 'TRIGGER')
  loop
    run_statement('drop trigger "'||i_schema||'"."'||trg.object_name||'"');
  end loop;
  
  for job in (select object_name from all_objects where owner = i_schema and object_type = 'JOB')
  loop
    run_statement('begin dbms_scheduler.drop_job(''"'||i_schema||'"."'||job.object_name||'"''); end;');
  end loop;
  
  for scheduler in (select object_name from all_objects where owner = i_schema and object_type = 'SCHEDULE')
  loop
    run_statement('begin dbms_scheduler.drop_schedule(''"'||i_schema||'"."'||scheduler.object_name||'"''); end;');
  end loop;
  
end;

begin
  for i in 1..l_schemas.count
  loop
    l_schemas(i) := upper(l_schemas(i));
    print_stats(l_schemas(i), ' before');
    remove_schema_objects(l_schemas(i));
    print_stats(l_schemas(i), ' after');
  end loop;
exception
  when others then
    dbms_output.put_line('An unexpected error occured: '||SQLERRM);
    raise;
end;
/

===> Number and type of objects in schema ORDSYS before removal:
7      PROCEDURE
2      LIBRARY
30     PACKAGE
22     PACKAGE BODY
15     TYPE BODY
99     JAVA RESOURCE
5      TABLE
4      INDEX
32     FUNCTION
5      VIEW
2490   JAVA CLASS
446    TYPE


===> Number and type of objects in schema ORDSYS after removal:


===> Number and type of objects in schema APEX_040200 before removal:
3      SEQUENCE
16     PROCEDURE
271    PACKAGE
263    PACKAGE BODY
457    TRIGGER
11     SYNONYM
11     FUNCTION
211    VIEW
452    TABLE
1320   INDEX
6      TYPE
4      JOB


===> Number and type of objects in schema APEX_040200 after removal:



PL/SQL procedure successfully completed.