今天同事发现一个库的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开始了。