<h2> 結論 </h2> 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(表中列的列表，以逗號分割)，可以同時指定多個列不可用 <h2> 測試 </h2> 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 <h2 style="margin:0px;padding:0px;"> 個人簡介 </h2> <div style="text-indent:2em;"> 8年oracle從業經驗，具備豐富的oracle技能，目前在國內北京某專業oracle服務公司從事高階技術顧問。 </div> <div style="text-indent:2em;"> </div> <div style="text-indent:2em;"> 服務過的客戶： </div> <div style="text-indent:2em;"> 中國電信 </div> <div style="text-indent:2em;"> 中國移動 </div> <div style="text-indent:2em;"> 中國聯通 </div> <div style="text-indent:2em;"> 中國電通 </div> <div style="text-indent:2em;"> 國家電網 </div> <div style="text-indent:2em;"> 四川達州商業銀行 </div> <div style="text-indent:2em;"> 湖南老百姓大藥房 </div> <div style="text-indent:2em;"> 山西省公安廳 </div> <div style="text-indent:2em;"> 中國郵政 </div> <div style="text-indent:2em;"> 北京302醫院      </div> <div style="text-indent:2em;"> 河北廊坊新奧集團公司 </div> <div style="text-indent:2em;">  專案經驗: </div> <div style="text-indent:2em;"> 中國電信3G專案AAA系統資料庫部署及最佳化       中國聯通4G資料庫效能分析與最佳化 </div> <div style="text-indent:2em;"> 中國聯通CRM資料庫效能最佳化 </div> <div style="text-indent:2em;"> 中國移動10086電商平臺資料庫部署及最佳化 </div> <div style="text-indent:2em;"> 湖南老百姓大藥房ERR資料庫sql最佳化專案 </div> <div style="text-indent:2em;"> 四川達州商業銀行TCBS核心業務系統資料庫模型設計和RAC部署及最佳化 </div> <div style="text-indent:2em;"> 四川達州商業銀行TCBS核心業務系統後端批處理儲存過程功能模組編寫及最佳化 </div> <div style="text-indent:2em;"> 北京高鐵訊號監控系統RAC資料庫部署及最佳化 </div> <div style="text-indent:2em;"> 河南宇通客車資料庫效能最佳化 </div> <div style="text-indent:2em;"> 中國電信電商平臺核心採購模組表模型設計及最佳化 </div> <div style="text-indent:2em;"> 中國郵政儲蓄系統資料庫效能最佳化及sql最佳化 </div> <div style="text-indent:2em;"> 北京302醫院資料庫遷移實施 </div> <div style="text-indent:2em;"> 河北廊坊新奧data guard部署及最佳化 </div> <div style="text-indent:2em;"> 山西公安廳身份證審計資料庫系統故障評估 </div> <div style="text-indent:2em;"> 國家電網上海災備專案4 node rac+adg         貴州移動crm及客服資料庫效能最佳化專案        貴州移動crm及客服務資料庫sql稽核專案        深圳穆迪軟體有限公司資料庫效能最佳化專案 </div> <div style="text-indent:2em;"> 聯絡方式： </div> <div style="text-indent:2em;"> 手機：18201115468 </div> <div style="text-indent:2em;"> qq   :   305076427 </div> <div style="text-indent:2em;"> qq微博: wisdomone1 </div> <div style="text-indent:2em;"> 新浪微博：wisdomone9 </div> <div style="text-indent:2em;"> qq群：275813900     </div> <div style="text-indent:2em;"> itpub部落格名稱：wisdomone1    <a href="http://blog.itpub.net/9240380/" style="text-decoration:none;color:#565656;">http://blog.itpub.net/9240380/</a> </div>

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

相關文章