Tuesday, 8 December 2015
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 ----------------------
