查看Oracle sys_lob,system表空间满的处理-SYS_LOB0003450292C00039$$

今天同事发现一个库的system表空间快满了，正好没事，登录上去也分析一下。

$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Apr 13 09:57:25 2011

Copyright (c) 1982, 2005, Oracle.All Rights Reserved.

sys@CQTMSZTF_SQL> col TABLESPACE_NAME for a30

sys@CQTMSZTF_SQL> col FREE_PCT for a20

sys@CQTMSZTF_SQL> set heading on

sys@CQTMSZTF_SQL> SELECT df.TABLESPACE_NAME,FILES, extent_management ,sum_m as TOTAL_SIZE,

2nvl(sum_free_m,0) as FREE_MB,to_char(100*nvl(sum_free_m,0.1)/sum_m, '999.99') AS FREE_PCT

3FROM ( SELECT tablespace_name,count(file_id) as files ,sum(bytes)/1024/1024 AS sum_m FROM dba_data_files GROUP BY tablespace_name) df,

4(SELECT tablespace_name,

5sum(bytes)/1024/1024 AS sum_free_m

6FROM dba_free_space GROUP BY tablespace_name ) fs,(select tablespace_name,extent_management from dba_tablespaces) ts

7where df.tablespace_name=fs.tablespace_name(+) and df.tablespace_name=ts.tablespace_name order by FREE_PCT;

TABLESPACE_NAMEFILES EXTENT_MAN TOTAL_SIZEFREE_MB FREE_PCT

------------------------------ ---------- ---------- ---------- ---------- --------------------

SYSTEM2 LOCAL559023.1875.41

TMS20066 LOCAL240005798.812524.16

USERS1 LOCAL5240.00

EXAMPLE1 LOCAL15072.312548.21

HR3 LOCAL90205822.062564.55

UNDOTBS11 LOCAL50003998.187579.96

SYSAUX2 LOCAL46303924.584.76

CQWEB2 LOCAL50004998.187599.96

果然，system表空间已经占用了5个G了。看下top10对象有哪些

col owner for a10

SELECT *

FROM (SELECTBYTES, segment_name, segment_type, owner

FROM dba_segments

WHERE tablespace_name = 'SYSTEM'

ORDER BY BYTES DESC)

WHERE ROWNUM < 10

/

BYTES SEGMENT_NAMESEGMENT_TYPEOWNER

---------- ------------------------- ------------------ ----------

1811939328 SYS_LOB0003450292C00039$$ LOBSEGMENTSYSTEM

1143996416 C_OBJ#_INTCOL#CLUSTERSYS

465567744 I_COL1INDEXSYS

411041792 C_OBJ#CLUSTERSYS

226492416 I_COL2INDEXSYS

218103808 I_COL3INDEXSYS

209715200 HIST_HEAD$TABLESYS

183500800 IDL_UB1$TABLESYS

117440512 I_H_OBJ#_COL#INDEXSYS

简单看下，就知道前面两个肯定不对劲，先处理最大的一个吧。

select * from dba_lobs where SEGMENT_NAME='SYS_LOB0003450292C00039$$';

OWNERTABLE_NAME

---------- ------------------------------

COLUMN_NAME

----------------------------------------------------------------------------------------------------

SEGMENT_NAMETABLESPACE_NAMEINDEX_NAMECHUNK

------------------------------ -------------------- ------------------------------ ----------

PCTVERSIONRETENTIONFREEPOOLS CACHELOGGING IN_ FORMATPAR

---------- ---------- ---------- ---------- ------- --- --------------- ---

SYSTEMSYS_EXPORT_FULL_02

XML_CLOB

SYS_LOB0003450292C00039$$SYSTEMSYS_IL0003450292C00039$$8192

900NOYESYES ENDIAN NEUTRALNO

从查询结果可知这个clob字段是属于SYS_EXPORT_FULL_02这张表的。检查记录数，嗯，倒是挺多

sys@CQTMSZTF_SQL> select count(*) from system.SYS_EXPORT_FULL_02;

COUNT(*)

----------

146442

在网上查了下，资料很少，只是使用数据泵导入导出的时候会出现这个名字，而且显示的是scheduler。关于数据泵的知识在头脑里闪现了一下，疑惑的登录试试：

expdp attach='SYS_EXPORT_FULL_02'

Export: Release 10.2.0.2.0 - 64bit Production on Wednesday, 13 April, 2011 11:08:40

Copyright (c) 2003, 2005, Oracle.All rights reserved.

Username: system

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Job: SYS_EXPORT_FULL_02

Owner: SYSTEM

Operation: EXPORT

Creator Privs: FALSE

GUID: 9F8764FC3DF3C0D6E0437F000001C0D6

Start Time: Wednesday, 13 April, 2011 11:08:54

Mode: FULL

Instance: cqtmsztf

Max Parallelism: 1

EXPORT Job Parameters:

Parameter NameParameter Value:

CLIENT_COMMANDsystem/********@CQTMSZTF full=y dumpfile=a.dmp

State: IDLING

Bytes Processed: 0

Current Parallelism: 1

Job Error Count: 0

Dump File: /oracle/app/oracle/product/10.2/admin/cqtmsztf/dpdump/a.dmp

bytes written: 487,424

Worker 1 Status:

State: UNDEFINED

Object Schema: HRZTF0317

Object Name: RPTDESDATA_1465

Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Completed Objects: 58,631

Worker Parallelism: 1

Export> stop_job

Are you sure you wish to stop this job ([yes]/no): yes

尝试停止job后，再查询空间占用情况

sys@CQTMSZTF_SQL> SELECT *

2FROM (SELECTBYTES, segment_name, segment_type, owner

3FROM dba_segments

4WHERE tablespace_name = 'SYSTEM'

5ORDER BY BYTES DESC)

6WHERE ROWNUM < 10

7;

BYTES SEGMENT_NAMESEGMENT_TYPEOWNER

---------- ------------------------- ------------------ ----------

1811939328 SYS_LOB0003450292C00039$$ LOBSEGMENTSYSTEM

1143996416 C_OBJ#_INTCOL#CLUSTERSYS

465567744 I_COL1INDEXSYS

411041792 C_OBJ#CLUSTERSYS

226492416 I_COL2INDEXSYS

218103808 I_COL3 INDEXSYS

209715200 HIST_HEAD$TABLESYS

183500800 IDL_UB1$TABLESYS

117440512 I_H_OBJ#_COL#INDEXSYS

还是存在，下面尝试kill_job

$ expdp attach='SYS_EXPORT_FULL_02'

Export: Release 10.2.0.2.0 - 64bit Production on Wednesday, 13 April, 2011 11:12:56

Copyright (c) 2003, 2005, Oracle.All rights reserved.

Username: system

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Job: SYS_EXPORT_FULL_02

Owner: SYSTEM

Operation: EXPORT

Creator Privs: FALSE

GUID: 9F8764FC3DF3C0D6E0437F000001C0D6

Start Time: Wednesday, 13 April, 2011 11:13:02

Mode: FULL

Instance: cqtmsztf

Max Parallelism: 1

EXPORT Job Parameters:

Parameter NameParameter Value:

CLIENT_COMMANDsystem/********@CQTMSZTF full=y dumpfile=a.dmp

State: IDLING

Bytes Processed: 0

Current Parallelism: 1

Job Error Count: 0

Dump File: /oracle/app/oracle/product/10.2/admin/cqtmsztf/dpdump/a.dmp

bytes written: 487,424

Worker 1 Status:

State: UNDEFINED

Object Schema: HRZTF0317

Object Name: RPTDESDATA_1465

Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Completed Objects: 58,631

Worker Parallelism: 1

Export> kill_job

Are you sure you wish to stop this job ([yes]/no): yes

sys@CQTMSZTF_SQL> SELECT *

2FROM (SELECTBYTES, segment_name, segment_type, owner

3FROM dba_segments

4WHERE tablespace_name = 'SYSTEM'

5ORDER BY BYTES DESC)

6WHERE ROWNUM < 10

7;

BYTES SEGMENT_NAMESEGMENT_TYPEOWNER

---------- ------------------------- ------------------ ----------

1143996416 C_OBJ#_INTCOL#CLUSTERSYS

465567744 I_COL1INDEXSYS

411041792 C_OBJ#CLUSTERSYS

226492416 I_COL2INDEXSYS

218103808 I_COL3INDEXSYS

209715200 HIST_HEAD$TABLESYS

183500800 IDL_UB1$TABLESYS

117440512 I_H_OBJ#_COL#INDEXSYS

109051904 I_HH_OBJ#_COL#INDEXSYS

惊喜的发现那个segment已经不见了，果然和我想的一样。

再看C_OBJ#_INTCOL#这个对象是cluster，它占的空间也比较大，但这时我的system表空间已经free2个多G了，由于C_OBJ#_INTCOL#这张表是系统的核心表，所以决定不管它了，懒得去动它，麻烦。关于这张表的清理，请参看一下链接。

下面是网友的解答，我整理如下：

expdp/impd 是Job形式的，会调用DBMS_DATAPUMP PL/SQL包，这个API提供高速的导出导入功能；还有DBMS_METADATA PL/SQL包，这个包是将metadata(对象定义)存储在XML里。

所有的进程都能load 和unload 这些metadata。  在备份期间，会自动的生成一张与Job_name 相同名称的表， 该表在备份期间保存metadata数据。 当备份结束后，自动删除该表。

可以使用SQL：

SQL>select *  FROM dba_datapump_jobs

查看Job 的信息。 如果意外情况导致备份Job失败，那么对应保存metadata的表，还是会存在。 这个时候，如果查询dba_datapump_jobs，会显示该Job为not running。 这时候，我们只需要drop 掉对应的表，在查询dba_datapump_jobs。 就没有记录了。 这个也是一种处理方法。

如果没有指定Job name。 所以系统自动生成了一个：SYS_EXPORT_FULL_02。

默认是从SYS_EXPORT_FULL_01开始，因为我之前有一个没有运行的Job，所以这里从2开始了。