advertisements
_____________________________________________________________________________________________________________________
One of the main advantage of datapump is you can suspend the running export or import job and it can be resume if needed. Suppose if your server load is high when you started the export job, you can suspend the job and later you can resume the job once the server load comes down. One more feature is you can suspend the job from one client machine and can be resume from different client.
See one example below
Once you press ^C on the expdp window it will come to interactive mode with Export> prompt. In that prompt you can give commands to stop_job or kill_job
$ expdp scott/tiger schemas=scott directory=exp_dir dumpfile=exp_schema.dmp logfile=exp_schema.log job_name=expschema
Export: Release 11.1.0.7.0 - 64bit Production on Tuesday, 02 August, 2011 22:17:58
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."EXPSCHEMA": scott/******** schemas=scott directory=exp_dir dumpfile=exp_schema.dmp logfile=exp_schema.log job_name=expschema
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
^C
Export> stop_job=immediate
Are you sure you wish to stop this job ([yes]/no): yes
oracle@prod(4113) prod9 /home/oracle/scott
You can use dba_datapump_jobs view to get the details of the datapump jobs.
$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.1.0.7.0 - Production on Tue Aug 2 22:19:43 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select OWNER_NAME,JOB_NAME,OPERATION,JOB_MODE,STATE from dba_datapump_jobs;
OWNER_NAME JOB_NAME
------------------------------ ------------------------------
OPERATION JOB_MODE
------------------------------ ------------------------------
STATE
------------------------------
SCOTT EXPSCHEMA
EXPORT SCHEMA
NOT RUNNING
Using the below mentioned command you can resume the job. Once you fire the below command in the prompt, expdp will load the job details and come export> prompt. You have to give continue_client command to resume the job.
$ expdp scott/tiger attach=EXPSCHEMA
Export: Release 11.1.0.7.0 - 64bit Production on Tuesday, 02 August, 2011 22:25:09
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Job: EXPSCHEMA
Owner: SCOTT
Operation: EXPORT
Creator Privs: TRUE
GUID: A993FD0301520998E04400144F9F5BAA
Start Time: Tuesday, 02 August, 2011 22:25:12
Mode: SCHEMA
Instance: prod9
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND scott/******** schemas=scott directory=exp_dir dumpfile=exp_schema.dmp logfile=exp_schema.log job_name=expschema
State: IDLING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /home/oracle/scott/exp_schema.dmp
bytes written: 4,096
Worker 1 Status:
Process Name: DW01
State: UNDEFINED
Export> continue_client
Job EXPSCHEMA has been reopened at Tuesday, 02 August, 2011 22:25
Restarting "SCOTT"."EXPSCHEMA": scott/******** schemas=scott directory=exp_dir dumpfile=exp_schema.dmp logfile=exp_schema.log job_name=expschema
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
^C
Export> kill_job
Are you sure you wish to stop this job ([yes]/no): yes
Once you kill the export job it will remove the details from the dba_datapump_jobs.
$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.1.0.7.0 - Production on Tue Aug 2 22:26:46 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select OWNER_NAME,JOB_NAME,OPERATION,JOB_MODE,STATE from dba_datapump_jobs;
no rows selected
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment