When DBA refreshes an Oracle schema or objects, privileges should be taken care carefully , specially for long time running complex environment.
After dropping a schema, all the granted privileges will be lost. So before refresh, DBA should record all the privileges granted to users, roles, etc.
Export a Schema from Source Database
$ expdp Username/Password directory= dumpfile= logfile= SCHEMAS= FLASHBACK_TIME=systimestamp
Record / Extract the Granted Privileges
Granted Column Privileges
SQL> select * from dba_col_privs where GRANTOR='SCHEMANAME';
Here ‘SCHEMANAME’ is the schema to be refreshed.
Granted Objects Privileges
SQL>select GRANTEE,OWNER||'.'||TABLE_NAME,GRANTOR,PRIVILEGE,GRANTABLE from dba_tab_privs where GRANTOR ='SCHEMANAME';
Other privileges you may be interested from following views:
- User_role_privs, dba_role_privs ( Users granted with with roles )
- User_sys_privs, dba_sys_privs
- Role_role_privs, role_sys_privs, role_tab_privs
- Session_privs, session_roles
Refresh Schema
Drop Schema Objects
SQL> select 'drop '||object_type|| ' '||owner||'.'||object_name ||' ;' from dba_objects where owner='SCHEMANAME';
Import Schema by Data Pump
$ impdp username/password directory= dumpfile= logfile= schemas=sourceschemaname remap_schema= remap_tablespace=
Fix Invalid Objects
Fix all invalid objects, check all synonyms, etc
Collect Stats
SQL>EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname =>'SCHEMANAME');
Make DBA Life Easy 