Schema Refresh in QA/DEV step by step

 Schema Refresh step by step 

Below are the details step for schema refresh . The below steps are very helpful when you want to refresh schemas in QA/DEV and want to keep all the previous grants and privileges after refresh .

1. Take the backup of the schema in target database.

expdp directory=<directory_name> dumpfile=backup_%U.dmp logfile=Backup.log parllel=<n> compression=all

schemas=Schema_name

2. sys_privs.sql :

--------------- 

set echo off

set head off

set pagesize 0

set feedback off

set linesize 200

spool sys_privs.sql

select 'grant '||privilege||' to '||grantee||' ; '  from dba_sys_privs where grantee in ('schema_name');

spool off

3.owner.sql :

-----------

spool owner.sql

select 'grant '||privilege||' on '||OWNER||'.'||table_name||' to  '||grantee||' ; ' from dba_tab_privs WHERE OWNER in ('schema_name');

spool off

4.pre_refresh_role_privs.sql :

----------------------------

set echo off

set head off

set pagesize 0

set feedback off

set linesize 200

spool pre_refresh_role_privs.sql

select 'grant '||granted_role||' to '||grantee||' ; '  from dba_role_privs where grantee in ('schema_name');

spool off

5.Check the object count in the database under that schema

 SELECT object_type,COUNT(*) FROM DBA_OBJECTS WHERE OWNER in ('schema_name') group by object_type ;

6. check the table count

SELECT COUNT(*) FROM DBA_tables WHERE OWNER in ('schema_name');

7.drop all the object under that schema

select 'drop '||object_type||' '||owner||'."'||object_name||'";' from dba_objects where owner in ('schema_names');

8.drop all the tables under that schema

select 'drop table  '||owner||'.'||table_name||' cascade constraints;' from dba_tables where owner in('schema_names');

9.purge the recyclebin

select 'purge table '||owner||'."'||object_name||'";' FROM dba_RECYCLEBIN where owner='schema_name' ;

10.now import data into target database from source dumpfile.

 impdp dumpfile=<dumpfile_name> logfile=import.log directory=<directory_name> schemas=schema_name parallel=<n>

11.check the count of objects after refresh for each schema

SELECT COUNT(*) FROM DBA_OBJECTS WHERE OWNER in ('schema_name');

12.check the table count

SELECT COUNT(*) FROM DBA_tables WHERE OWNER='schema_name';

13.run the spool file

@sys_privs.sql

14. run the spool file

@owner.sql

15.run the spool file

pre_refresh_role_privs.sql