Attach and deattach the expdp/impdp datapump job

Attach and deattach the expdp/impdp datapump job

EXPDP and IMPDP is used to take the logical backup of complete Database and schema.

Attach the Datapump job

1. Check the running job.

--Check running job
select owner_name, job_name from dba_datapump_jobs where state='EXECUTING';

2. Connect to the Data Pump job.

expdp user/pwd attach=

3. Attached and continue the work

--continue the job.
Export> CONTINUE_CLIENT

Deattach the Datapump job

In the Data Pump client session, stop the Data Pump job, then exit the Data Pump client, then re-attaching.
Export> STOP_JOB=IMMEDIATE
Export> EXIT_CLIENT

Example of ATTACHING & DEATTACHING Datapump job

Deattached the job
-- Start the job of EXPDP
EXPDP system/manager directory=dbpump dumpfile=full.dmp logfile=full.log parallel=4 job_name=full_system

-- Ctrl+c for export prompt which will break session
Ctrl+C

--Stop the current job
export> STOP_JOB
0r
Export> STOP_JOB=IMMEDIATE

-- come out from export prompt(deattached)
export> Exit

Attached the job
-- Attach with another session
expdp system/manager ATTACH=full_system

-- Start the job
export> START_JOB

Check other detail

-- Check the parameter involved during starting of datapump job
select owner_name, job_name ,operation, job_mode ,state , degree ,attached_sessions from dba_datapump_jobs;

--Check the osuser for job
select a.owner,a.job_name,b.osuser from dba_datapump_sessions a, v$session b where b.saddr = a.saddr;

---Check the Data Pump Worker process information.
set lines 150 pages 100 numwidth 7
col program for a38
col username for a10
col spid for a7
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid, s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
from v$session s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.