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

-------------- Schema refresh is complete ----------------------