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 2013
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]
anargodjaev.wordpress.com 