Thursday, September 17, 2009

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.

No comments: