客户提出SYSAUX空间太大,已经占据了20多G的空间,登陆系统发觉SYSAUX表空间中占据TOP SEGMENT的主要就是WRI$_SQLSET_PLAN_LINES表
SQL> select *
2 from (select bytes / 1024 / 1024 / 1024, segment_name, owner, segment_type
3 from dba_segments
4 where tablespace_name = 'SYSAUX'
5 order by bytes desc)
6 where rownum < 20
7 ;
BYTES/1024/1024/1024 SEGMENT_NAME OWNER SEGMENT_TYPE
-------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------
3.9814453125 WRI$_SQLSET_PLAN_LINES SYS TABLE
2.8681640625 WRH$_SQLTEXT SYS TABLE
2.3955078125 SYS_LOB0000009295C00038$$ SYS LOBSEGMENT
1.0615234375 WRH$_SYSMETRIC_HISTORY SYS TABLE
0.9951171875 WRH$_SYSMETRIC_HISTORY_INDEX SYS INDEX
0.8427734375 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST SYS INDEX
0.7587890625 SYS_LOB0000009002C00038$$ SYS LOBSEGMENT
0.751953125 WRH$_ACTIVE_SESSION_HISTORY SYS TABLE PARTITION
0.673828125 WRH$_SQL_PLAN SYS TABLE
0.646484375 WRH$_ACTIVE_SESSION_HISTORY SYS TABLE PARTITION
0.599609375 WRI$_OPTSTAT_HISTGRM_HISTORY SYS TABLE
0.599609375 WRH$_ACTIVE_SESSION_HISTORY SYS TABLE PARTITION
0.5849609375 WRH$_ACTIVE_SESSION_HISTORY SYS TABLE PARTITION
0.55078125 WRH$_ACTIVE_SESSION_HISTORY SYS TABLE PARTITION
0.5244140625 WRH$_ACTIVE_SESSION_HISTORY SYS TABLE PARTITION
0.4052734375 I_WRI$_OPTSTAT_H_ST SYS INDEX
0.380859375 WRH$_SYSMETRIC_SUMMARY SYS TABLE
0.3115234375 WRI$_SQLSET_PLAN_LINES_PK SYS INDEX
0.2900390625 WRI$_SQLSET_PLANS SYS TABLE
19 rows selected
2 from (select bytes / 1024 / 1024 / 1024, segment_name, owner, segment_type
3 from dba_segments
4 where tablespace_name = 'SYSAUX'
5 order by bytes desc)
6 where rownum < 20
7 ;
BYTES/1024/1024/1024 SEGMENT_NAME OWNER SEGMENT_TYPE
-------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------
3.9814453125 WRI$_SQLSET_PLAN_LINES SYS TABLE
2.8681640625 WRH$_SQLTEXT SYS TABLE
2.3955078125 SYS_LOB0000009295C00038$$ SYS LOBSEGMENT
1.0615234375 WRH$_SYSMETRIC_HISTORY SYS TABLE
0.9951171875 WRH$_SYSMETRIC_HISTORY_INDEX SYS INDEX
0.8427734375 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST SYS INDEX
0.7587890625 SYS_LOB0000009002C00038$$ SYS LOBSEGMENT
0.751953125 WRH$_ACTIVE_SESSION_HISTORY SYS TABLE PARTITION
0.673828125 WRH$_SQL_PLAN SYS TABLE
0.646484375 WRH$_ACTIVE_SESSION_HISTORY SYS TABLE PARTITION
0.599609375 WRI$_OPTSTAT_HISTGRM_HISTORY SYS TABLE
0.599609375 WRH$_ACTIVE_SESSION_HISTORY SYS TABLE PARTITION
0.5849609375 WRH$_ACTIVE_SESSION_HISTORY SYS TABLE PARTITION
0.55078125 WRH$_ACTIVE_SESSION_HISTORY SYS TABLE PARTITION
0.5244140625 WRH$_ACTIVE_SESSION_HISTORY SYS TABLE PARTITION
0.4052734375 I_WRI$_OPTSTAT_H_ST SYS INDEX
0.380859375 WRH$_SYSMETRIC_SUMMARY SYS TABLE
0.3115234375 WRI$_SQLSET_PLAN_LINES_PK SYS INDEX
0.2900390625 WRI$_SQLSET_PLANS SYS TABLE
19 rows selected
对于WRI$_SQLSET_PLAN_LINES表查看其中数据只有0条,但是segment分配已经达到了4G多,这个优先考虑的就是在系统闲暇时间进行move或者shrink space操作:
SQL> alter table WRI$_SQLSET_PLAN_LINES shrink space;
alter table WRI$_SQLSET_PLAN_LINES shrink space
*
ERROR at line 1:
ORA-10662: Segment has long columns
SQL> !oerr ora 10662
10662, 00000, "Segment has long columns"
// *Cause: Shrink was issued on a segment with long columns. This is not
// supported.
// *Action:
SQL> alter table _SQLSET_PLAN_LINES
2
SQL> alter table WRI$_SQLSET_PLAN_LINES move;
alter table WRI$_SQLSET_PLAN_LINES move
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
alter table WRI$_SQLSET_PLAN_LINES shrink space
*
ERROR at line 1:
ORA-10662: Segment has long columns
SQL> !oerr ora 10662
10662, 00000, "Segment has long columns"
// *Cause: Shrink was issued on a segment with long columns. This is not
// supported.
// *Action:
SQL> alter table _SQLSET_PLAN_LINES
2
SQL> alter table WRI$_SQLSET_PLAN_LINES move;
alter table WRI$_SQLSET_PLAN_LINES move
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
而对于包含LONG的数据表无法进行move或者shrink space,oracle提供了一种方法,就是首先exp或者expdp将这部分数据导出,然后truncate该表,最后将数据导入到该表中。这里对于WRI$_SQLSET_PLAN_LINES表仅仅只是存储的SQL SET的信息,而这部分SQL SET已经被删除掉,这里直接truncate该表就可以释放出该表的空间,当然如果表中有数据则可以参考上面这种处理方式。
MOS中有一篇关于如何缩减SYSAUX表空间的文章 How to Reduce SYSAUX Tablespace Occupancy Due to Fragmented TABLEs and INDEXes (文档 ID 1563921.1)