Expdp failed with error

Issue: Error while expdp backup

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
ORA-39171: Job is experiencing a resumable wait.
ORA-01691: unable to extend lob segment SYSTEM.SYS_LOB0000565576C00045$$ by 40964 in tablespace SYSTEM
ORA-39171: Job is experiencing a resumable wait.
ORA-01691: unable to extend lob segment SYSTEM.SYS_LOB0000565576C00045$$ by 40964 in tablespace SYSTEM
ORA-39171: Job is experiencing a resumable wait.
ORA-01691: unable to extend lob segment SYSTEM.SYS_LOB0000565576C00045$$ by 40964 in tablespace SYSTEM
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.CREATE_OBJECT_ROWS [TABLE]
TABLE:"GL"."XLA_GLT_526074"
ORA-30032: the suspended (resumable) statement has timed out
ORA-01691: unable to extend lob segment SYSTEM.SYS_LOB0000565576C00045$$ by 40964 in tablespace SYSTEM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 8170
----- PL/SQL Call Stack -----
object      line  object
handle    number  name
7000000963489b8     19028  package body SYS.KUPW$WORKER
7000000963489b8      8191  package body SYS.KUPW$WORKER
7000000963489b8      7382  package body SYS.KUPW$WORKER
7000000963489b8      2629  package body SYS.KUPW$WORKER
7000000963489b8      8846  package body SYS.KUPW$WORKER
7000000963489b8      1651  package body SYS.KUPW$WORKER
7000000b340a158         2  anonymous block
Job "SYSTEM"."SYS_EXPORT_FULL_07" stopped due to fatal error at 04:28:58

Error in Alert Log:
Thu Dec 01 02:28:53 2011
statement in resumable session 'SYSTEM.SYS_EXPORT_FULL_07.1' was suspended due to
ORA-01691: unable to extend lob segment SYSTEM.SYS_LOB0000565576C00045$$ by 40964 in tablespace SYSTEM

 

Findings:
There are orphaned Data Pump jobs that have not been cleaned up where the LOB segment associated with
the SYS_EXPORT_FULL_n tables is consuming much of the space in the SYSTEM tablespace.

col owner format a10;
col table_name format a20;
select l.owner,
l.table_name,
l.segment_name,
s.bytes
from   dba_lobs l,
dba_segments s
where  l.table_name like 'SYS_EXPORT_FULL%' and
l.table_name = s.segment_name;
OWNER      TABLE_NAME           SEGMENT_NAME                        BYTES
---------- -------------------- ------------------------------ ----------
SYSTEM     SYS_EXPORT_FULL_02   SYS_LOB0000456326C00045$$       132726784
SYSTEM     SYS_EXPORT_FULL_01   SYS_LOB0000434197C00045$$        59056128
SYSTEM     SYS_EXPORT_FULL_03   SYS_LOB0000456884C00045$$       132726784
SYSTEM     SYS_EXPORT_FULL_04   SYS_LOB0000529281C00045$$       199041024
SYSTEM     SYS_EXPORT_FULL_06   SYS_LOB0000564856C00045$$        59039744
SYSTEM     SYS_EXPORT_FULL_05   SYS_LOB0000564275C00045$$        59039744
SYSTEM     SYS_EXPORT_FULL_07   SYS_LOB0000565576C00045$$        59039744

7 rows selected.

The results will show multiple export tables where the LOB segments associated with them are consuming the space.

 

Solution:
Drop the export tables by following the steps outlined in Note 336014.1.

Step 1. Determine in SQL*Plus which Data Pump jobs exist in the database:
CONNECT / as sysdba
SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a12
COL operation LIKE state
COL job_mode LIKE state

SELECT owner_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

Step 2. Ensure that the listed jobs in dba_datapump_jobs are not export/import Data Pump jobs that are active: status should be 'NOT RUNNING'.

Step 3. Drop master tables which are not running
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;

How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ? [ID 336014.1]