降低SQLSET相關表WRI$_SQLSET_PLAN_LINES高水位線，釋放SYSAUX資料表空間

客戶提出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
對於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
而對於包含LONG的資料表無法進行move或者shrink space，oracle提供了一種方法，就是首先exp或者expdp將這部分資料匯出，然後truncate該表，最後將資料匯入到該表中。這裡對於WRI$_SQLSET_PLAN_LINES表僅僅只是儲存的SQL SET的資訊，而這部分SQL SET已經被刪除掉，這裡直接truncate該表就可以釋放出該表的空間，當然如果表中有資料則可以參考上面這種處理方式。

SQL中select與set的區別

).同時對多個變數同時賦值時

declare @a varchar(128), @b varchar(128)
SET @a='ABC',@b='EFG'
GO --報錯：訊息 102，層級 15，狀態 1，第 3 行 ',' 附近有語法錯誤。

 

declare @a varchar(128), @b varchar(128)
SELECT @a='ABC',@b='EFG'
GO --正確運行

 

(2).運算式返回多個值時

在說明這一項前我們先建立一個要用到的表，並對其賦值，代碼如下：

 

賦值測試表--fuzhiTEST
create table fuzhiTEST(
id int ,
name varchar(128)
)
go
insert into fuzhiTEST(id,name) values(1,'name1')
insert into fuzhiTEST(id,name) values(2,'name2')
insert into fuzhiTEST(id,name) values(3,'name3')
go

declare @name varchar(128)
set @name = (select name from fuzhiTEST)
GO  --報錯：訊息 512，層級 16，狀態 1，第 2 行
                 子查詢返回的值不止一個。當子查詢跟隨在 =、!=、<、<=、>、>= 之後，或子查詢用作運算式時，這種情況是不允許的。

 

declare @name varchar(128)
select @name =name from fuzhiTEST
print @name
GO  --正確運行：顯示 name3

 

(3).運算式未傳回值時

declare @name varchar(128)
set @name = 'SET初始值'
set @name = (select name from fuzhiTEST where id = 4 )
print @name
GO   --正確運行：顯示NULL

 

declare @name varchar(128)
set @name = 'SELECT初始值'
select @name = name from fuzhiTEST where id = 4
print @name --正確運行：顯示 SELECT初始值
GO

注意：SELECT 也可以將標量子查詢的值賦給變數，如果標量子查詢不傳回值，則變數被置為 null 值（此時與使用 SET 賦值是完全相同的）。
對標量子查詢的概念大家可能覺得陌生，下面舉個例子說明下（對比上面綠色部分）：

declare @name varchar(128)
set @name = 'SELECT初始值'
select @name =(select name from fuzhiTEST where id = 4 )
print @name --正確運行：顯示NULL
GO