Include roles and synonyms in DataPump export

October 8, 2014

DataPump

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%';