alter table set unused column

結論

1,alter table set unused column指定表的某列為不可用
2,alter table set unused column生效後不可用列已經不再屬於表，但空間不會釋放，需要重組方可釋放
3,alter table set unused column的資訊可用user_unused_col_tabs查詢
4,alter table set unused column適用於高併發OLTP環境，在業務繁忙時，先執行此語句，待業務壓力小時，然後用alter table drop unused columns真正刪除不可用的列;
  alter table drop column刪除列消耗的時間明顯要高於alter table set unused column
5,alter table set unused column指定某個LONG列不可用，不能新增其它LONG列
6，同一個表中不能同時存在2個LONG，其它LOB列不受其限制
7，透過alter table set unsed(表中列的列表，以逗號分割)，可以同時指定多個列不可用
8, alter table set unused column cascade constraints配置某列不可用會遞迴刪除定義的約束,發現指不指定cascade constraints選項都會遞迴刪除其約束
9, 透過alter table set unsed(表中列的列表，以逗號分割)，可以同時指定多個列不可用

測試

1，資料庫版本
SQL> select * from v$version where rownum=1;


BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production


2,獲取暫時不使用的列的表資訊
SQL> select count(*) from user_unused_col_tabs;


  COUNT(*)
----------
         0


3，建立某個測試表


SQL> create table t_unused_col(a int,b int,c int);


Table created.         




4,配置指定列為不可用


查閱官方手冊，配置表的列為不可用，其語法請參考drop_column_clause 節
SQL> alter table t_unused_col set unused column b; 


Table altered.


SQL> alter table t_unused_col set unused column c; 


Table altered.


SQL> select count(*) from user_unused_col_tabs;


  COUNT(*)
----------
         1
5，配置指定不可用後不再顯示其列
SQL> desc user_unused_col_tabs;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 COUNT                                              NUMBER


6，可以在如下字典查詢到不可用列的相關資訊
SQL> select table_name,count from user_unused_col_tabs;


TABLE_NAME                                                        COUNT
------------------------------------------------------------ ----------
T_UNUSED_COL                                                          2


SQL> insert into t_unused_col values(1,1,1);
insert into t_unused_col values(1,1,1)
            *
ERROR at line 1:
ORA-00913: too many values




SQL> insert into t_unused_col values(1);


1 row created.


SQL> commit;


Commit complete.


SQL> select * from t_unused_col;


         A
----------
         1


7,刪除測試表中不可用的列
SQL> alter table t_unused_col drop unused columns;


Table altered.


8，重新新增B與C列
SQL> alter table t_unused_col add b int;


Table altered.


SQL> alter table t_unused_col add c int;


Table altered.


9，配置某列為不可用後，可以新增同名的列
SQL> alter table t_unused_col set unused column b;


Table altered.


SQL> alter table t_unused_col add b int;


Table altered.


10，配置某列為不可用，其列佔用的空間不會釋放
SQL> drop table t_unused_col purge;


Table dropped.


SQL> create table t_unused_col(a int,b int,c int);


Table created.


SQL> insert into t_unused_col select level,level,level from dual connect by level<=100000;


100000 rows created.


SQL> commit;


Commit complete.


SQL> select segment_name,bytes/1024/1024 from user_segments where segment_name='T_UNUSED_COL';


SEGMENT_NAME                   BYTES/1024/1024
------------------------------ ---------------
T_UNUSED_COL                                 3


SQL> alter table t_unused_col set unused column b;


Table altered.


SQL> select segment_name,bytes/1024/1024 from user_segments where segment_name='T_UNUSED_COL';


SEGMENT_NAME                   BYTES/1024/1024
------------------------------ ---------------
T_UNUSED_COL                                 3


11，刪除表中不可用的列後，其空間仍不會釋放
SQL> alter table t_unused_col drop unused columns;


Table altered.


SQL> select segment_name,bytes/1024/1024 from user_segments where segment_name='T_UNUSED_COL';


SEGMENT_NAME                   BYTES/1024/1024
------------------------------ ---------------
T_UNUSED_COL                                 3


12,只有重組表後，才會釋放刪除不可用列的空間
SQL> alter table t_unused_col move;


Table altered.


SQL> select segment_name,bytes/1024/1024 from user_segments where segment_name='T_UNUSED_COL';


SEGMENT_NAME                   BYTES/1024/1024
------------------------------ ---------------
T_UNUSED_COL                                 2


13，配置某個LONG型別的列為不可用後，必須刪除其列後方可新增新的LONG列
SQL> alter table t_unused_col add b clob;


Table altered.


SQL> alter table t_unused_col set unused column b;


Table altered.


SQL> alter table t_unused_col add x long;


Table altered.


SQL> alter table t_unused_col set unused column x;


Table altered.


SQL> alter table t_unused_col add y long;
alter table t_unused_col add y long
                             *
ERROR at line 1:
ORA-01754: a table may
contain only one column of
type LONG




SQL> alter table t_unused_col drop unused columns;


Table altered.


SQL> alter table t_unused_col add y long;


Table altered.


SQL> drop table t_unused_col purge;


Table dropped.


SQL> create table t_unused_col(a int,b long,c long);
create table t_unused_col(a int,b long,c long)
                                       *
ERROR at line 1:
ORA-01754: a table may
contain only one column of
type LONG


14,可見alter table drop columns消耗的時間明顯要大於alter table set unused columns，所以後者適用於高併發的OLTP環境，減少鎖持有情況發生；
到業務壓力不大時，可以採用alter table drop unused columns
SQL> create table t_unused_col(a int,b int);


Table created.


SQL> insert into t_unused_col select level,level from dual connect by level<=1000000;


1000000 rows created.


SQL> commit;


Commit complete.




SQL> set timing on time on
06:34:03 SQL> alter table t_unused_col drop column b;


Table altered.


Elapsed: 00:03:06.30




06:37:42 SQL> drop table t_unused_col purge;


Table dropped.


Elapsed: 00:00:03.94


06:37:56 SQL> create table t_unused_col(a int,b int);


Table created.


Elapsed: 00:00:01.98


06:38:12 SQL> insert into t_unused_col select level,level from dual connect by level<=1000000;




1000000 rows created.


Elapsed: 00:00:15.59
06:40:58 SQL> 06:40:58 SQL> commit;


06:41:11 SQL> alter table t_unused_col set unused column b;


Table altered.


Elapsed: 00:00:00.77


06:41:32 SQL> alter table t_unused_col drop unused columns;


Table altered.


Elapsed: 00:02:22.51
06:44:13 SQL> commit;


Commit complete.


Elapsed: 00:00:00.00




15,透過alter table set unsed(表中列的列表，以逗號分割)，可以同時指定多個列不可用
SQL> drop table t_unused_col purge;


Table dropped.


SQL> create table t_unused_col(a int,b int,c int);


Table created.


SQL> alter table t_unused_col set unused (b,c);


Table altered.


16,alter table set unused column cascade constraints配置某列不可用會遞迴刪除定義的約束,發現指不指定cascade constraints選項都會遞迴刪除其約束
SQL> drop table t_unused_col purge;


Table dropped.


SQL> create table t_unused_col(a int,b int);


Table created.


SQL> alter table t_unused_col add constraint chk_b check(b>1);


Table altered.


SQL> select constraint_name,constraint_type,status from user_constraints where table_name='T_UNUSED_COL';


CONSTRAINT_NAME                CO STATUS
------------------------------ -- ----------------
CHK_B                          C  ENABLED


SQL> alter table t_unused_col set unused column b cascade constraints;


Table altered.


SQL> select constraint_name,constraint_type,status from user_constraints where table_name='T_UNUSED_COL';


no rows selected


SQL> drop table T_UNUSED_COL purge;


Table dropped.


SQL> drop table t_unused_col purge;


Table dropped.


SQL> create table t_unused_col(a int primary key,b int);


Table created.


SQL> select constraint_name,constraint_type,status from user_constraints where table_name='T_UNUSED_COL';


CONSTRAINT_NAME                CO STATUS
------------------------------ -- ----------------
SYS_C0011644                   P  ENABLED


SQL> alter table t_unused_col set unused column a;


Table altered.


SQL> select constraint_name,constraint_type,status from user_constraints where table_name='T_UNUSED_COL';


no rows selected

出自：

oracle11g alter table set unused column指定表某列不可用之系列一_ITPUB部落格