How to remove all the objects of Schema before import in oracle database

 Sometimes, we get a reqeust to export and import the schema from PROD to QA, although we can use table_exists_action parameter to replace the table which exists in QA, but there are other objects also like sequences, procedures, functions etc which get skipped if exists in QA already. 

There are two ways now:

1. Take the grants/privileges(DDL) assigned to schema ,drop it and recreate the user using DDL generated and import into the schema.

 2. Delete all the objects under the schema , it should clear the schema and we can now import into the schema. We can use below script to delete all the objects

DECLARE

    PROCEDURE dropAll (inUser VARCHAR2)

    IS

    lSQL VARCHAR2(200);

        CURSOR constr_cur IS

           SELECT table_name, constraint_name FROM DBA_CONSTRAINTS

           WHERE owner = inUser

           AND constraint_type ='R';

       CURSOR obj_cur IS

          SELECT object_type, object_name FROM DBA_OBJECTS

          WHERE owner = inUser;

       CURSOR pubsyn_cur IS

          SELECT synonym_name

          FROM DBA_SYNONYMS

          WHERE OWNER = 'PUBLIC' AND TABLE_OWNER = inUser;

BEGIN

FOR l IN constr_cur

LOOP

    EXECUTE IMMEDIATE 'ALTER TABLE ' || inUser || '.' || l.table_name

                      || ' DROP CONSTRAINT ' || l.constraint_name;

END LOOP;

FOR l IN obj_cur

LOOP

 IF l.object_type = 'SYNONYM' THEN

    EXECUTE IMMEDIATE 'DROP SYNONYM ' || inUser || '.' || l.object_name;

 ELSIF l.object_type = 'TABLE' THEN

    EXECUTE IMMEDIATE 'DROP TABLE ' || inUser || '.' || l.object_name;

 ELSIF l.object_type = 'SEQUENCE' THEN

    EXECUTE IMMEDIATE 'DROP SEQUENCE ' || inUser || '.' || l.object_name;

 ELSIF l.object_type = 'FUNCTION' THEN

    EXECUTE IMMEDIATE 'DROP FUNCTION ' || inUser || '.' || l.object_name;

 ELSIF l.object_type = 'PROCEDURE' THEN

    EXECUTE IMMEDIATE 'DROP PROCEDURE ' || inUser || '.' || l.object_name;

 ELSIF l.object_type = 'PACKAGE' THEN

    EXECUTE IMMEDIATE 'DROP PACKAGE ' || inUser || '.' || l.object_name;

 ELSIF l.object_type = 'VIEW' THEN

    EXECUTE IMMEDIATE 'DROP VIEW ' || inUser || '.' || l.object_name;

 ELSIF l.object_type = 'TYPE' THEN

  BEGIN

    EXECUTE IMMEDIATE 'DROP TYPE ' || inUser || '.' || l.object_name;

 EXCEPTION

  WHEN OTHERS THEN NULL;

 END;

 END IF;

END LOOP;

FOR l IN pubsyn_cur

LOOP

   EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM ' || l.synonym_name;

END LOOP;

END;

BEGIN

 dropAll('AGENT_102');

 dropAll('CORE_102');

 dropAll('STG_102');

 dropAll('PUB_102');

 dropAll('SWITCH_A_102');

 dropAll('SWITCH_B_102');

END;

/

Note: you need to provide the schema name in dropAll('put schema name here'), we can put multiple schema names as above if we want to drop objects of multiple schemas in one go.