Oracle script for cleaning a database during a Bambo datapump process

Question

I'm going to prepare an automated oracle script for cleaning a database during a datapump process via Bambo.

Until now, I did these queries manually into sql developer in order to clean the database before to import a datapump file.

select 'drop TABLE ' || object_name || ';' from user_objects where object_type = 'TABLE';

select 'drop view ' || object_name || ';' from user_objects where object_type = 'VIEW';

select 'drop INDEX ' || object_name || ';' from user_objects where object_type = 'INDEX';

select 'drop PACKAGE ' || object_name || ';' from user_objects where object_type = 'PACKAGE';

select 'drop TYPE ' || object_name || ';' from user_objects where object_type = 'TYPE';

select 'drop sequence ' || object_name || ';' from user_objects where object_type = 'SEQUENCE';

select 'drop synonym ' || object_name || ';' from user_objects where object_type = 'SYNONYM';

select 'drop PROCEDURE ' || object_name || ';' from user_objects where object_type = 'PROCEDURE';

select 'drop FUNCTION ' || object_name || ';' from user_objects where object_type = 'FUNCTION';

select 'drop DATABASE LINK ' || object_name || ';' from user_objects where object_type = 'DATABASE LINK';

select 'drop JOB ' || object_name || ';' from user_objects where object_type = 'JOB';

select 'drop MATERIALIZED VIEW ' || object_name || ';' from user_objects where object_type = 'MATERIALIZED VIEW';

I need now to execute these drop automatically without any manual action. Thus I need to create the sql script for droping these objects: TABLE, VIEW, MATERIALIZED VIEW, INDEX, PACKAGE, TYPE, SEQUENCE, SYNONYM, PROCEDURE, FUNCTION, DATABASE LINK and JOB

I done this script:

BEGIN
   FOR cur_rec IN (SELECT object_name, object_type
                   FROM user_objects
                   WHERE object_type IN
                             ('TABLE',
                              'VIEW',
                              'MATERIALIZED VIEW',
                              'PACKAGE',
                              'TYPE',
                              'PROCEDURE',
                              'FUNCTION',
                              'SEQUENCE',
                              'SYNONYM',
                              'INDEX',
                              'DATABASE LINK',
                              'JOB',
                              'PACKAGE BODY'
                             ))
   LOOP
      BEGIN
         IF cur_rec.object_type = 'TABLE'
         THEN
            EXECUTE IMMEDIATE 'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '" CASCADE CONSTRAINTS';
         ELSE
            EXECUTE IMMEDIATE 'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '"';
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('FAILED: DROP '
                                  || cur_rec.object_type
                                  || ' "'
                                  || cur_rec.object_name
                                  || '"'
                                 );
      END;
   END LOOP;
   FOR cur_rec IN (SELECT * 
                   FROM all_synonyms 
                   WHERE table_owner IN (SELECT USER FROM dual))
   LOOP
      BEGIN
         EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM ' || cur_rec.synonym_name;
      END;
   END LOOP;
END;

I'm not sure if it will correspond exactly to my needs and if it will be optimized (without any possible issue).

Could you please help me with that ?

Thanks in advance for your help

Answer 1

I suggest DROP SCHEMA schema_name CASCADE as a DBA.

Then recreate the user with CREATE USER... Perfectly clean and a lot easier to manage as you don't have to worry about object types you haven't coded for.

The problem with dropping individual items is you'd need to walk the dependency chain recursively in order to drop things in the right order or it won't let you. Also, there's no need to drop package bodies if you drop packages, or to drop indexes if you drop tables, etc. You can code this, but it's rather a bit complicated and IMO not worth the trouble.

If you are not a DBA, ask your DBA to create a procedure owned by a privileged account that drops and recreates your user for you, and give you execute privileges on that procedure. You will have to be logged in as a different user in order to call it, however, as you can't drop a user you're connected with. But it's still worth going this route rather than coding your own schema clearing. Oracle's already done the work for you.