Lower Sqlset related table Wri$_sqlset_plan_lines high watermark, free Sysaux tablespace

Customer proposed Sysaux space is too large, has occupied more than 20 g space, landing system found that Sysaux table space occupy top segment is the Wri$_sqlset_plan_lines table

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

Rows selected
For the Wri$_sqlset_plan_lines table to view only 0 of the data, but the segment distribution has reached 4G, this priority is in the system leisure time move or shrink space operation:

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 is issued on a segment with long columns. This isn't
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
A way to move or shrink space,oracle that contains a long datasheet is to export this part of the data first, exp or EXPDP, then truncate the table, and finally import the data into the table. Here for the Wri$_sqlset_plan_lines table is only stored SQL set information, and this part of the SQL set has been deleted, here directly truncate the table can release the table space, of course, if there is data in the table can refer to the above approach.

The difference between select and set in SQL

(1). When assigning values to multiple variables at the same time

DECLARE @a varchar (128), @b varchar (128)
SET @a= ' ABC ', @b= ' EFG '
Go--Error: Message 102, Level 15, State 1, line 3rd ', ' There is a syntax error nearby.

DECLARE @a varchar (128), @b varchar (128)
SELECT @a= ' ABC ', @b= ' EFG '
Go--run correctly

(2). When an expression returns multiple values

Before we explain this, we'll create a table to use and assign it a value, as follows:

Assignment Test Table--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--Error: Message 512, Level 16, State 1, line 2nd
The subquery returns more than one value. This is not allowed when the subquery follows =,!=, <, <=, >, >=, or the subquery is used as an expression.

DECLARE @name varchar (128)
Select @name =name from Fuzhitest
Print @name
Go--run correctly: Show Name3

(3). When an expression does not return a value

DECLARE @name varchar (128)
Set @name = ' Set initial value '
Set @name = (select name from fuzhitest where id = 4)
Print @name
Go--run correctly: Show null

DECLARE @name varchar (128)
Set @name = ' Select initial value '
Select @name = name from fuzhitest where id = 4
Print @name--run correctly: Display Select initial value
Go

Note: SELECT can also assign the value of a standard quantum query to a variable, and if the scalar query does not return a value, the variable is set to a null value, which is exactly the same as using the SET assignment.
The concept of a scalar quantum query may be unfamiliar to you, as illustrated below (contrasting the green section above):

DECLARE @name varchar (128)
Set @name = ' Select initial value '
Select @name = (select name from fuzhitest where id = 4)
Print @name--run correctly: Show null
Go