Wednesday, October 8, 2014

Include roles and synonyms in DataPump export

For schema level exports it may be useful to include roles and public synonyms relevant to the schemas exported. Instead of generating them manually they can be included in the DataPump export. The following example of a parameter file shows how this can be done:

FULL=YES
INCLUDE=SCHEMA:"IN (SELECT 'PUBLIC' FROM DUAL UNION SELECT username FROM dba_users WHERE username in ('APEX','SCOTT'))"
INCLUDE=PUBLIC_SYNONYM/SYNONYM:"IN (SELECT synonym_name FROM dba_synonyms WHERE owner = 'PUBLIC' AND table_owner in ('APEX','SCOTT'))"
DIRECTORY=DB_EXPORT
DUMPFILE=apex_expdp.dmp
LOGFILE=apex_expdp.log
INCLUDE=ROLE:"IN (select role from dba_roles where role in (select grantee from dba_tab_privs where grantor in ('APEX','SCOTT')))"  

When deciding what to include in an export you may want to check the table DATABASE_EXPORT_OBJECTS to find the correct path as in this example:

select * 
from DATABASE_EXPORT_OBJECTS 
where object_path like '%ROLE%';

1 comment :

Anonymous said...

Very useful, just what I needed, thank yoo