SYSAUX表空间异常处理

最新推荐文章于 2021-09-07 13:39:29 发布

ctest3001

最新推荐文章于 2021-09-07 13:39:29 发布

阅读量177

点赞数

最近发现SYSAUX这个SYSTEM表空间的辅助表空间剩下不多了，于是查询里面比较大的segment。

SQL> select * from (select segment_name,segment_type,bytes/1024/1024 MB from dba_segments where tablespace_name = 'SYSAUX' order by MB desc) where rownum < 6;

SEGMENT_NAME SEGMENT_TYPE MB

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

SYS_LOB0000006213C00038$$ LOBSEGMENT 256

WRH$_SQL_PLAN TABLE 168

WRI$_ADV_SQLT_PLANS TABLE 96

WRH$_SQL_PLAN_PK INDEX 88

I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX 72

看到WRH$_SQL_PLAN 表很大，来看看这个表的存的什么

SQL> desc WRH$_SQL_PLAN

Name Null? Type

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

SNAP_ID NUMBER

DBID NOT NULL NUMBER

SQL_ID NOT NULL VARCHAR2(13)

PLAN_HASH_VALUE NOT NULL NUMBER

ID NOT NULL NUMBER

OPERATION VARCHAR2(30)

OPTIONS VARCHAR2(30)

OBJECT_NODE VARCHAR2(128)

OBJECT# NUMBER

OBJECT_OWNER VARCHAR2(30)

OBJECT_NAME VARCHAR2(31)

OBJECT_ALIAS VARCHAR2(65)

OBJECT_TYPE VARCHAR2(20)

OPTIMIZER VARCHAR2(20)

PARENT_ID NUMBER

DEPTH NUMBER

POSITION NUMBER

SEARCH_COLUMNS NUMBER

COST NUMBER

CARDINALITY NUMBER

BYTES NUMBER

OTHER_TAG VARCHAR2(35)

PARTITION_START VARCHAR2(64)

PARTITION_STOP VARCHAR2(64)

PARTITION_ID NUMBER

OTHER VARCHAR2(4000)

DISTRIBUTION VARCHAR2(20)

CPU_COST NUMBER

IO_COST NUMBER

TEMP_SPACE NUMBER

ACCESS_PREDICATES VARCHAR2(4000)

FILTER_PREDICATES VARCHAR2(4000)

PROJECTION VARCHAR2(4000)

TIME NUMBER

QBLOCK_NAME VARCHAR2(31)

REMARKS VARCHAR2(4000)

TIMESTAMP DATE

OTHER_XML CLOB

原来是存awr报告的表。

other_xml想必就是最大那个lobsegment了，来验证一下：

SQL> select * from (SELECT A.TABLE_NAME, A.COLUMN_NAME, B.SEGMENT_NAME, B.SEGMENT_TYPE,

2 B.TABLESPACE_NAME, B.BYTES / 1024 / 1024, B.BLOCKS, B.EXTENTS

3 FROM USER_LOBS A, USER_SEGMENTS B

4 WHERE A.SEGMENT_NAME = B.SEGMENT_NAME

5 ORDER BY B.BYTES DESC)

6 where rownum < 4;

TABLE_NAME

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

COLUMN_NAME

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

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME B.BYTES/1024/1024 BLOCKS

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

EXTENTS

----------

WRH$_SQL_PLAN

OTHER_XML

SYS_LOB0000006213C00038$$ LOBSEGMENT SYSAUX 256 32768

103

问题找到了了，于是在测试库上truncate表WRH$_SQL_PLAN和WRH$_SQLTEXT

没有出现问题，准备继续观察两天，没问题就在生产库执行了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25915379/viewspace-731445/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25915379/viewspace-731445/