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.
