Wednesday, December 31, 2008

Interact with the executing data pump jobs in oracle

Oracle data pump export jobs run on server machine. Sometimes from remote machine you run data pump jobs on server or in server machine you do it. If you close your window then still data pump export operation runs background in the server machine. You may wish to control your data pump jobs that is running in the background and wish to know the status of it like which objects now it is processing or may wish to kill the job.

In order to do that first be sure which jobs are running now and see the state by querying from dba_datapump_jobs view.

SQL> select job_name,state from dba_datapump_jobs;

JOB_NAME STATE
------------------------------ ------------------------------
SYS_EXPORT_SCHEMA_01 EXECUTING

You may also wish to see other fields of the view like owner_name, operation etc.

Now in order to go to the interactive mode of the executing jobs just ATTCH the executing jobs like below,

SQL> $expdp maximsg/a attach=SYS_EXPORT_SCHEMA_01

Export: Release 10.2.0.1.0 - Production on Thursday, 01 January, 2009 11:43:49

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options

Job: SYS_EXPORT_SCHEMA_01
Owner: MAXIMSG
Operation: EXPORT
Creator Privs: FALSE
GUID: BF0614AD15254C7B964B78333B7D9E1A
Start Time: Thursday, 01 January, 2009 11:38:30
Mode: SCHEMA
Instance: tiger
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND maximsg/******** dumpfile=d:part1.dmp,e:part2.dmp,dat
a_pump_dir%U.dmp
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: E:\oracle\product\10.2.0\admin\tiger\dpdump\data_pump_dir%u.dmp
Dump File: D:\PART1.DMP
bytes written: 704,512
Dump File: E:\PART2.DMP
bytes written: 4,096
Dump File: E:\ORACLE\PRODUCT\10.2.0\ADMIN\TIGER\DPDUMP\DATA_PUMP_DIR01.DMP
bytes written: 4,096

Worker 1 Status:
State: EXECUTING
Object Schema: MAXIMSG
Object Name: ACCOUNTING
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 171
Completed Rows: 8,169,569
Worker Parallelism: 1

Now you have reached in interactive mode. In order to back to the command line mode just write continue_client.
Export> continue_client

In order to kill the job that is executing,
Export> kill_job
Are you sure you wish to stop this job ([yes]/no): yes

In this way through ATTACH command you can interact with the job that is running. Similarly you can interact the data pump import operation.

No comments:

Post a Comment