Data_Pump_setup.txt

create or replace directory dir_exp as '/ora_db/prdapdw1/exp';
CREATE or REPLACE DIRECTORY dir_exp_log as '/ora_db/tsthyp1/exp.log';
grant read,write on directory dir_exp to oracle;
grant read,write on directory dir_exp_log to oracle;


expdp / DIRECTORY=dir_exp dumpfile=full_vcstst3_%U.dmp LOGFILE=dir_exp_log:vcstst3_expdp.log 

expdp / directory=dir_exp dumpfile=full_vcstst3_%U.dmp parallel=2 job_name=exp_vcstst3  COMPRESSION=ALL full=y logfile=test

expdp CONTENT=METADATA_ONLY DIRECTORY=DIR_EXP DUMPFILE=ps_stg_aukd.dmp LOGFILE=ps_stg_aud.log SCHEMAS=PSOFDW,STAGING,AUDITING 



select sid, serial#
from v$session s, dba_datapump_sessions d
where s.saddr = d.saddr;

select sid, serial#, sofar, totalwork
from v$session_longops
where opname = 'CASES_EXPORT'
and sofar != totalwork;

--Following parameter needs to be set in init.ora to write dmp file to a NFS mount

set event="10298 trace name context forever, level 32" in init.ora to allow dp to write into nfs mount


----Data pump job monitoring:
select NAME,STATUS, TIMEOUT, ERROR_NUMBER, ERROR_MSG from DBA_RESUMABLE;
select OBJECT_NAME,OBJECT_TYPE,REASON,SUGGESTED_ACTIONfrom DBA_OUTSTANDING_ALERTS;
select username,opname,target_desc,sofar,totalwork,message from V$SESSION_LONGOPS;

SELECT * FROM DBA_DATAPUMP_SESSIONS;