近期某客户遇到问题，处理完检查表空间时，发现SYSTEM表空间使用率很高，使用了28GB。检查此表空间占空间大的segment信息，发现无数为LOB字段，继续检查，LOB字段对应的均为SYS_EXPORT_FULL_n表的XML_CLOB字段；通过检查dba_datapump_jobs视图中的JOB状态，以及操作系统中实际查看EXPDP进程，可以判断这些SYS_EXPORT_FULL_n表均已经没有对应的EXPDP进程；这种情况下参照MOS文档“如何清除 DBA_DATAPUMP_JOBS 视图中的异常数据泵作业? (文档 ID 1626201.1)”中的步骤进行确认及清理即可，清理后表空间使用率恢复正常。
1.检查占空间大的SEGMENT信息
SQL> col segment_name for a20
SQL> col owner for a10
SQL> select * from (select segment_name,owner,segment_type,bytes/1024/1024 mb from dba_segments
2 where tablespace_name='SYSTEM' order by 4 desc) where rownum<11;
SEGMENT_NAME OWNER SEGMENT_TYPE MB
-------------------- ---------- ------------------ ----------
SYS_LOB0008477747C00 SYSTEM LOBSEGMENT 1159
045$$
SYS_LOB0008471189C00 SYSTEM LOBSEGMENT 1157
045$$
SYS_LOB0008480605C00 SYSTEM LOBSEGMENT 1152
045$$
SYS_LOB0008479614C00 SYSTEM LOBSEGMENT 1152
045$$
SEGMENT_NAME OWNER SEGMENT_TYPE MB
-------------------- ---------- ------------------ ----------
SYS_LOB0008482808C00 SYSTEM LOBSEGMENT 1152
045$$
SYS_LOB0008469268C00 SYSTEM LOBSEGMENT 1152
045$$
SYS_LOB0008248725C00 SYSTEM LOBSEGMENT 1152
045$$
SYS_LOB0008466660C00 SYSTEM LOBSEGMENT 1152
SEGMENT_NAME OWNER SEGMENT_TYPE MB
-------------------- ---------- ------------------ ----------
045$$
SYS_LOB0008463601C00 SYSTEM LOBSEGMENT 1152
045$$
SYS_LOB0008475758C00 SYSTEM LOBSEGMENT 1152
045$$
SQL> select owner,TABLE_NAME,COLUMN_NAME from dba_lobs where SEGMENT_NAME='SYS_LOB0008477747C00045$$';
OWNER TABLE_NAME
---------- ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
SYSTEM SYS_EXPORT_FULL_18
XML_CLOB
SQL> SQL> SQL> SQL> select owner,TABLE_NAME,COLUMN_NAME from dba_lobs where SEGMENT_NAME='SYS_LOB0008471189C00045$$';
OWNER TABLE_NAME
---------- ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
SYSTEM SYS_EXPORT_FULL_15
XML_CLOB
2.检查EXPDP JOB状态信息
SQL> COL owner_name FORMAT a10;
SQL> COL job_name FORMAT a20
SQL> COL state FORMAT a12
SQL> COL operation LIKE state
SQL> COL job_mode LIKE state
SQL>
SQL> -- 查找数据泵作业:
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
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
---------- -------------------- ------------ ------------ ------------ -----------------
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
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
---------- -------------------- ------------ ------------ ------------ -----------------
SYSTEM SYS_EXPORT_FULL_07 EXPORT FULL NOT RUNNING 0
SYSTEM SYS_EXPORT_FULL_08 EXPORT FULL NOT RUNNING 0
SYSTEM SYS_EXPORT_FULL_09 EXPORT FULL NOT RUNNING 0
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
---------- -------------------- ------------ ------------ ------------ -----------------
SYSTEM SYS_EXPORT_FULL_10 EXPORT FULL NOT RUNNING 0
SYSTEM SYS_EXPORT_FULL_11 EXPORT FULL NOT RUNNING 0
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
---------- -------------------- ------------ ------------ ------------ -----------------
SYSTEM SYS_EXPORT_FULL_12 EXPORT FULL NOT RUNNING 0
SYSTEM SYS_EXPORT_FULL_13 EXPORT FULL NOT RUNNING 0
SQL> SELECT o.status, o.object_id, o.object_type,
o.owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;
STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
------- ---------- ------------------- ------------------------------
VALID 3014671 TABLE SYSTEM.SYS_EXPORT_FULL_01
VALID 4715421 TABLE SYSTEM.SYS_EXPORT_FULL_02
VALID 5067224 TABLE SYSTEM.SYS_EXPORT_FULL_03
VALID 5067276 TABLE SYSTEM.SYS_EXPORT_FULL_04
VALID 7879624 TABLE SYSTEM.SYS_EXPORT_FULL_05
VALID 7881723 TABLE SYSTEM.SYS_EXPORT_FULL_06
VALID 7884112 TABLE SYSTEM.SYS_EXPORT_FULL_07
VALID 8116556 TABLE SYSTEM.SYS_EXPORT_FULL_08
VALID 8216766 TABLE SYSTEM.SYS_EXPORT_FULL_09
VALID 8248725 TABLE SYSTEM.SYS_EXPORT_FULL_10
VALID 8463601 TABLE SYSTEM.SYS_EXPORT_FULL_11
VALID 8466660 TABLE SYSTEM.SYS_EXPORT_FULL_12
VALID 8468643 TABLE SYSTEM.SYS_EXPORT_FULL_13
VALID 8469268 TABLE SYSTEM.SYS_EXPORT_FULL_14
VALID 8471189 TABLE SYSTEM.SYS_EXPORT_FULL_15
VALID 8473335 TABLE SYSTEM.SYS_EXPORT_FULL_16
VALID 8475758 TABLE SYSTEM.SYS_EXPORT_FULL_17
VALID 8477747 TABLE SYSTEM.SYS_EXPORT_FULL_18
VALID 8479614 TABLE SYSTEM.SYS_EXPORT_FULL_19
VALID 8480605 TABLE SYSTEM.SYS_EXPORT_FULL_20
3.清理并检查
SQL> DROP TABLE system.SYS_EXPORT_FULL_02;
SQL> DROP TABLE system.SYS_EXPORT_FULL_01;
SQL> SELECT owner_name, job_name, operation, job_mode,
2 state, attached_sessions
3 FROM dba_datapump_jobs
4 WHERE job_name NOT LIKE 'BIN$%'
5 ORDER BY 1,2;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
---------- -------------------- ------------ ------------ ------------ -----------------
ZJIRPT SYS_EXPORT_FULL_01 EXPORT FULL NOT RUNNING 0
