使用impdp导入失败ORA-31634(转)

expdp:ORA-31634: job already exists

 

使用数据泵进行全备,由于user的表空间不足导致备份失败,添加user表空间,清理空间后再次进行备份报如下错误:

With the Partitioning, OLAP and Data Mining options

ORA-31634: job already exists

ORA-31664: unable to construct unique job name when defaulted

 

解决方法:

1.在备份时指定一个在dba_datapump_jobs中没有不存在的一个job_name

 

2.清理dba_datapump_jobs表

1)查询可以清理的master table并生成SQL

select 'drop table ' || owner_name || '.' || job_name || ';'
from dba_datapump_jobs
where state = 'NOT RUNNING';

2)清理后再次dba_datapump_jobs确认清理成功

SELECT owner_name, job_name, operation, job_mode, state, attached_sessions
FROM dba_datapump_jobs
ORDER BY 1,2;

若不成功,按下面的方法再次清理

sqlplus oak/oak

exec dbms_datapump.stop_job(dbms_datapump.attach('SYS_EXPORT_TABLE_01','OAK'));

在导入导出时会自动创建一个user.SYS_EXPORT_FULL_12的表(好像是1~100)..占用user表空间..
指定job_name不知道能不能更改他创建的表
自动创建的job名可在导入导出时的输出或者log中看到

参考:
How to cleanup orphaned datapump jobs from DBA_DATAPUMP_JOBS

Original article

In many cases you sometimes stop data pump job or in case of an abnormal end of the Data Pump job (the orphaned job) or using undocumented parameter KEEP_MASTER=Y,the master table remain in the database.

Though this topic is related to cleanup orphaned datapump jobs. But it is good to know several things before doing cleanup jobs.

1) You can check the orphaned data pump from the state column of the view dba_datapump_jobs and DBA_DATAPUMP_JOBS is based on gv$datapump_job, obj$, com$, and user$. Orphaned Data Pump jobs do not have an impact on new Data Pump jobs. If a new Data Pump job is started,a new entry will be created, which has no relation to the old Data Pump jobs.

2) For a new data pump job without any job name it is used a system generated name.
From the dba_datapump_jobs it is checked for existing data pump jobs and then obtain a unique new system generated jobname.

3) Data pump jobs are different from DBMS_JOBS and they are maintained differently.
Jobs created with DBMS_JOBS use there own processes. Data Pump jobs use a master process and worker process(es).

4) If you drop the master table while doing the data pump export or data pump import operation then the scenario is discussed below.
In case of export if you drop data pump export operation then export process will abort.
In case of import if you drop data pump import operation then import process will abort while it leads an incomplete import.
If the data pump job is completed and master table exist (a common if you do export operation with KEEP_MASTER=y) then it is safe to drop the master table.
Step by step cleanup orphaned datapump jobs is discussed below.
Step 01: Check the orphaned datapump jobs.
sqlplus / as sysdba
SET lines 140
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a12
COL operation LIKE owner_name
COL job_mode LIKE owner_name
SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
---------- -------------------- ---------- ---------- ------------ -----------------
ARJU SYS_EXPORT_SCHEMA_01 EXPORT SCHEMA NOT RUNNING 0
ARJU SYS_EXPORT_SCHEMA_02 EXPORT SCHEMA NOT RUNNING 0

Step 02: Check the state field. For orphaned jobs the state will be NOT RUNNING. So from the output we can say both are orphaned jobs.

Step 03: Drop the master table.
DROP TABLE ARJU.SYS_EXPORT_SCHEMA_01;
DROP TABLE ARJU.SYS_EXPORT_SCHEMA_02;

Step 04: Check for existing data pump jobs by query issued in step 01. If objects are in recyclebin then purge the objects from the recyclebin.

SQL> SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
---------- -------------------- ---------- ---------- ------------ -----------------
ARJU       BIN$xMNQdACzQ6yl22kj EXPORT SCHEMA NOT RUNNING 0 9U0B8A==$0
ARJU       BIN$BmUy4r5MSX+ojxFk EXPORT SCHEMA NOT RUNNING 0 sw8ocg==$0

SQL> PURGE TABLE ARJU.SYS_EXPORT_SCHEMA_01;

Table purged.

SQL> PURGE TABLE ARJU.SYS_EXPORT_SCHEMA_02;

Table purged.

Check if there is any orphaned jobs again.
SQL> SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs;

no rows selected

Step 05: In this stage you did not get any orphaned jobs if the jobs have a master table.
If there are still jobs listed in dba_datapump_jobs do cleanup process like below.

SET serveroutput on
SET lines 100
DECLARE
job1 NUMBER;
BEGIN
job1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_SCHEMA_01','ARJU');
DBMS_DATAPUMP.STOP_JOB (job1);
END;
/

DECLARE
job2 NUMBER;
BEGIN
job2 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_SCHEMA_02','ARJU');
DBMS_DATAPUMP.STOP_JOB (job2);
END;
/