Failed Oracle Backup via Expdp

Recently I received a call concerning failing backup jobs being taken via Expdp. I logged in to our Virtual-DBA client’s site and immediately checked the alertlog for errors. The below errors were found in the alertlog.

Exporting orcl on DBHOST01 Sun Dec 16 21:03:32 PST 2012
 ORA-39171: Job is experiencing a resumable wait.
 ORA-01691: unable to extend lob segment ORACLE.SYS_LOB0001423675C00039$ by 128 in tablespace USERS
 ORA-39171: Job is experiencing a resumable wait.
 ORA-01691: unable to extend lob segment ORACLE.SYS_LOB0001423675C00039$ by 128 in tablespace USERS
 ORA-39171: Job is experiencing a resumable wait.
 ORA-01691: unable to extend lob segment ORACLE.SYS_LOB0001423675C00039$ by 128 in tablespace USERS
 ORA-39171: Job is experiencing a resumable wait.
 ORA-01691: unable to extend lob segment ORACLE.SYS_LOB0001423675C00039$ by 128 in tablespace USERS
 ORA-39171: Job is experiencing a resumable wait.
 ORA-01691: unable to extend lob segment ORACLE.SYS_LOB0001423675C00039$ by 128 in tablespace USERS
 ORA-39171: Job is experiencing a resumable wait.
 ORA-01691: unable to extend lob segment ORACLE.SYS_LOB0001423675C00039$ by 128 in tablespace USERS
 ORA-39171: Job is experiencing a resumable wait.
 ORA-01691: unable to extend lob segment ORACLE.SYS_LOB0001423675C00039$ by 128 in tablespace USERS
 ORA-39171: Job is experiencing a resumable wait.

The problem, obvious to some, is orphaned data pump jobs that haven’t been cleaned up. They were consuming the free space in the USERS tablespace.

You can see the the LOB segments associated with the export tables with the below query.

SELECT x.owner, x.table_name, y.bytes
 FROM dba_lobs x, dba_segments y
 WHERE x.table_name LIKE 'SYS_EXPORT_FULL%'
 AND x.table_name = y.segement_name;

With a quick search on metalink we found a note just for this issue. Note: 336014.1. Below are the steps to follow to fix the problem.

1. Find out which jobs exist in the database:

SELECT ower_name, job_name, operation, job_mode, state, attached_sessions
 FROM dba_datapump_jobs
 WHERE job_name NOT LIKE 'BIN$%'
 ORDER BY 1,2;

OWNER_NAME JOB_NAME             OPERATION    JOB_MODE  STATE        ATTACHED_SESSIONS
———- ——————– ———— ———— ———— —————–
SYSTEM     SYS_EXPORT_FULL_01   EXPORT       FULL      NOT RUNNING                  0
SYSTEM     SYS_EXPORT_FULL_02   EXPORT       FULL      NOT RUNNING                  0
SYSTEM     SYS_EXPORT_FULL_03   EXPORT       FULL      NOT RUNNING                  0
SYSTEM     SYS_EXPORT_FULL_04   EXPORT       FULL      NOT RUNNING                  0
SYSTEM     SYS_EXPORT_FULL_05   EXPORT       FULL      NOT RUNNING                  0
SYSTEM     SYS_EXPORT_FULL_06   EXPORT       FULL      NOT RUNNING                  0
SYSTEM     SYS_EXPORT_FULL_07   EXPORT       FULL      NOT RUNNING                  0

2. Make sure the jobs listed aren’t running. The state column will say ‘NOT RUNNING’.

3. Lastly drop the tables

DROP TABLE SYSTEM.SYS_EXPORT_FULL_01;
 DROP TABLE SYSTEM.SYS_EXPORT_FULL_02;
 DROP TABLE SYSTEM.SYS_EXPORT_FULL_03;
 DROP TABLE SYSTEM.SYS_EXPORT_FULL_04;
 DROP TABLE SYSTEM.SYS_EXPORT_FULL_05;
 DROP TABLE SYSTEM.SYS_EXPORT_FULL_06;
 DROP TABLE SYSTEM.SYS_EXPORT_FULL_07;

We then reran the job and it ran without error.