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: ...

Say you define a DP job from the API (DBMS_DATAPUMP) you may end up with jobs with status NOT RUNNING until you get it right. Verify if you have such a job with select job_name,state from user_datapump_jobs; Then you may try to remove it with: declare l_h number; begin l_h:=dbms_datapump.attach('YOUR_JOB'); dbms_datapump.stop_job(l_h,immediate=>1); commit; end; / If the query above still reports the job with the same status it can be removed by dropping the master table, according to Note 336014.1: ...