EXPDP/IMPDP任务管理_ora-31632-CSDN博客

本文链接：https://blog.csdn.net/suyishuai/article/details/29806543

大家都知道在oracle10G以后，我们有了新的工具数据泵(expdp|impdp)，这个工具是在oracle服务端来运行的，就算我们关闭当前的交换模式，数据泵相应的进程也是在运行的。今天就演示下如何和这个数据泵交互。

[oracle@hadoop02 ~]$ expdp ami/ami attach=my_job

Export: Release 10.2.0.1.0 - 64bit Production on Tuesday, 10 June, 2014 10:00:39

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.KUPV$FT", line 430
ORA-31638: cannot attach to job MY_JOB for user AMI
ORA-31632: master table "AMI.MY_JOB" not found, invalid, or inaccessible
ORA-00942: table or view does not exist

上面的命令参数，attach=job_name,每个数据泵进程都有jobName的，显示是没有启动那个job，所以才报错的，也可也利用oracle相关视图，查看现在运行的所有job

SQL> set linesize 1000

SQL> select owner_name,job_name ,state from dba_datapump_jobs;

no rows selected

SQL>

现在我先启动一个导出job,来演示如何交换

[oracle@hadoop02 ~]$ expdp ami/ami  schemas=ami directory=dir_dmp1  dumpfile =aaaaa.dump   job_name=my_job

Export: Release 10.2.0.1.0 - 64bit Production on Tuesday, 10 June, 2014 10:02:11

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "AMI"."MY_JOB":  ami/******** schemas=ami directory=dir_dmp1 dumpfile =aaaaa.dump job_name=my_job 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
^C ---大家用ctrl+c 就能从这里进入交换模式了
Export> help --用help查看可以使用的命令
The following commands are valid while in interactive mode.
Note: abbreviations are allowed

Command               Description
------------------------------------------------------------------------------
ADD_FILE              Add dumpfile to dumpfile set.
CONTINUE_CLIENT       Return to logging mode. Job will be re-started if idle. --退出交换模式，退回刚才的状态
EXIT_CLIENT           Quit client session and leave job running.
FILESIZE              Default filesize (bytes) for subsequent ADD_FILE commands.
HELP                  Summarize interactive commands.
KILL_JOB              Detach and delete job.
PARALLEL              Change the number of active workers for current job.
                      PARALLEL=<number of workers>.
START_JOB             Start/resume current job.
STATUS                Frequency (secs) job status is to be monitored where
                      the default (0) will show new status when available.
                      STATUS[=interval]
STOP_JOB              Orderly shutdown of job execution and exits the client.
                      STOP_JOB=IMMEDIATE performs an immediate shutdown of the
                      Data Pump job.

Export> CONTINUE_CLIENT  --退回刚才的log状态
Total estimation using BLOCKS method: 14.03 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "AMI"."E_MP_DAY_READ":"READ_P201401"        873.6 MB 5592648 rows
^C
Export> 
Export> CONTINUE_CLIENT
Total estimation using BLOCKS method: 14.03 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "AMI"."E_MP_DAY_READ":"READ_P201401"        873.6 MB 5592648 rows
^C
Export> CONTINUE_CLIENT
. . exported "AMI"."E_MP_DAY_READ":"READ_P201312"        873.6 MB 5592648 rows
. . exported "AMI"."E_MP_DAY_READ":"READ_P201403"        873.6 MB 5592648 rows
^C
Export> STOP_JOB --停止当前任务
Are you sure you wish to stop this job ([yes]/no): yes

这个时候，我们就把这个job停止了，只是停止了。

SQL> 
SQL> select owner_name,job_name ,state from dba_datapump_jobs;

OWNER_NAME                     JOB_NAME                       STATE
------------------------------ ------------------------------ ------------------------------
AMI                            MY_JOB                         STOP PENDING

这个停止以后，还是存在的，可用用kill命令删除

[oracle@hadoop02 ~]$ expdp ami/ami attach=my_job

Export: Release 10.2.0.1.0 - 64bit Production on Tuesday, 10 June, 2014 10:04:07

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

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

Job: MY_JOB
  Owner: AMI                            
  Operation: EXPORT                         
  Creator Privs: FALSE                          
  GUID: FB72B750BA4FA4F0E040A8C08F431E06
  Start Time: Tuesday, 10 June, 2014 10:04:08
  Mode: SCHEMA                         
  Instance: amidb
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        ami/******** schemas=ami directory=dir_dmp1 dumpfile =aaaaa.dump job_name=my_job 
  State: IDLING                         
  Bytes Processed: 4,521,189,440
  Percent Done: 31
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /home/oracle/suys/aaaaa.dump
    bytes written: 4,521,971,712
  
Worker 1 Status:
  State: UNDEFINED                      

Export> kill_job
Are you sure you wish to stop this job ([yes]/no): yes

这个时候，可以用最开始的命令，进入刚才启动job的交换模式，kill以后查看相关视图

no rows selected

SQL> 
SQL> 
SQL> 
SQL> select owner_name,job_name ,state from dba_datapump_jobs;

no rows selected

SQL>

这样，这个job就算真正的删除了。