system表空間滿的處理-SYS_LOB0003450292C00039$$

今天同事發現一個庫的system表空間快滿了，正好沒事，登入上去也分析一下。

$ sqlplus '/ as sysdba'

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

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,

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

3 FROM ( 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,

5 sum(bytes)/1024/1024 AS sum_free_m

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

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

TABLESPACE_NAME FILES EXTENT_MAN TOTAL_SIZE FREE_MB FREE_PCT

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

SYSTEM 2 LOCAL 5590 23.1875 .41

TMS2006 6 LOCAL 24000 5798.8125 24.16

USERS 1 LOCAL 5 2 40.00

EXAMPLE 1 LOCAL 150 72.3125 48.21

HR 3 LOCAL 9020 5822.0625 64.55

UNDOTBS1 1 LOCAL 5000 3998.1875 79.96

SYSAUX 2 LOCAL 4630 3924.5 84.76

CQWEB 2 LOCAL 5000 4998.1875 99.96

果然，system表空間已經佔用了5個G了。看下top10物件有哪些

col owner for a10

SELECT *

FROM (SELECT BYTES, segment_name, segment_type, owner

FROM dba_segments

WHERE tablespace_name = 'SYSTEM'

ORDER BY BYTES DESC)

WHERE ROWNUM < 10

/

BYTES SEGMENT_NAME SEGMENT_TYPE OWNER

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

1811939328 SYS_LOB0003450292C00039$$ LOBSEGMENT SYSTEM

1143996416 C_OBJ#_INTCOL# CLUSTER SYS

465567744 I_COL1 INDEX SYS

411041792 C_OBJ# CLUSTER SYS

226492416 I_COL2 INDEX SYS

218103808 I_COL3 INDEX SYS

209715200 HIST_HEAD$ TABLE SYS

183500800 IDL_UB1$ TABLE SYS

117440512 I_H_OBJ#_COL# INDEX SYS

簡單看下，就知道前面兩個肯定不對勁，先處理最大的一個吧。

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

OWNER TABLE_NAME

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

COLUMN_NAME

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

SEGMENT_NAME TABLESPACE_NAME INDEX_NAME CHUNK

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

PCTVERSION RETENTION FREEPOOLS CACHE LOGGING IN_ FORMAT PAR

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

SYSTEM SYS_EXPORT_FULL_02

XML_CLOB

SYS_LOB0003450292C00039$$ SYSTEM SYS_IL0003450292C00039$$ 8192

900 NO YES YES ENDIAN NEUTRAL NO

從查詢結果可知這個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

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 Name Parameter Value:

CLIENT_COMMAND system/********@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 *

2 FROM (SELECT BYTES, segment_name, segment_type, owner

3 FROM dba_segments

4 WHERE tablespace_name = 'SYSTEM'

5 ORDER BY BYTES DESC)

6 WHERE ROWNUM < 10

7 ;

BYTES SEGMENT_NAME SEGMENT_TYPE OWNER

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

1811939328 SYS_LOB0003450292C00039$$ LOBSEGMENT SYSTEM

1143996416 C_OBJ#_INTCOL# CLUSTER SYS

465567744 I_COL1 INDEX SYS

411041792 C_OBJ# CLUSTER SYS

226492416 I_COL2 INDEX SYS

218103808 I_COL3 INDEX SYS

209715200 HIST_HEAD$ TABLE SYS

183500800 IDL_UB1$ TABLE SYS

117440512 I_H_OBJ#_COL# INDEX SYS

還是存在，下面嘗試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

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 Name Parameter Value:

CLIENT_COMMAND system/********@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 *

2 FROM (SELECT BYTES, segment_name, segment_type, owner

3 FROM dba_segments

4 WHERE tablespace_name = 'SYSTEM'

5 ORDER BY BYTES DESC)

6 WHERE ROWNUM < 10

7 ;

BYTES SEGMENT_NAME SEGMENT_TYPE OWNER

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

1143996416 C_OBJ#_INTCOL# CLUSTER SYS

465567744 I_COL1 INDEX SYS

411041792 C_OBJ# CLUSTER SYS

226492416 I_COL2 INDEX SYS

218103808 I_COL3 INDEX SYS

209715200 HIST_HEAD$ TABLE SYS

183500800 IDL_UB1$ TABLE SYS

117440512 I_H_OBJ#_COL# INDEX SYS

109051904 I_HH_OBJ#_COL# INDEX SYS

驚喜的發現那個segment已經不見了，果然和我想的一樣。

再看C_OBJ#_INTCOL#這個物件是cluster，它佔的空間也比較大，但這時我的system表空間已經free2個多G了，由於C_OBJ#_INTCOL#這張表是系統的核心表，所以決定不管它了，懶得去動它，麻煩。關於這張表的清理，請參看一下連結。

使用EVENT 38003來重建BOOTSTRAP$物件

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

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開始了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/11088128/viewspace-692457/，如需轉載，請註明出處，否則將追究法律責任。

system表空間滿的處理-SYS_LOB0003450292C00039$$

相關文章