oracle 11g之alter table drop unused columns checkpoint刪除表不可用列系列二

結論

1,alter table drop unused columns checkpoint適用於減少不必要的undo空間消耗

2,checkpoint選項可取值有：
    不取值，預設在512條記錄後發生檢查點動作
    大於表記錄個數，表明在處理完所有表記錄後發生檢查點
    小於表記錄個數，表明在指定表記錄後發生檢查點

3，如果checkpoint選項後產生的檢查點動作被中斷，僅truncate table,drop table,alter table drop unused columns continue語句可以執行

4，alter table drop unused columns continue即恢復繼續執行被中斷的alter table drop unused columns checkpoint繼續下去    

測試

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> show parameter checkpoint


NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
log_checkpoint_interval              integer                0
log_checkpoint_timeout               integer                1800
log_checkpoints_to_alert             boolean                FALSE


SQL> alter system set log_checkpoints_to_alert=true;


System altered.


[oracle@seconary trace]$ tail -f alert_guowang.log 
  Current log# 2 seq# 356 mem# 0: /oracle/oradata/guowang/redo02.log
Fri Oct 16 08:18:16 2015
Thread 1 advanced to log sequence 357 (LGWR switch)
  Current log# 3 seq# 357 mem# 0: /oracle/oradata/guowang/redo03.log
Fri Oct 16 08:27:17 2015
ALTER SYSTEM SET log_checkpoints_to_alert=TRUE SCOPE=BOTH;
Fri Oct 16 08:28:44 2015
Beginning log switch checkpoint up to RBA [0x166.2.10], SCN: 11696352
Thread 1 advanced to log sequence 358 (LGWR switch)
  Current log# 1 seq# 358 mem# 0: /oracle/oradata/guowang/redo01.log


SQL> alter system checkpoint;


System altered.


Fri Oct 16 08:29:15 2015
Beginning global checkpoint up to RBA [0x166.1044.10], SCN: 11698731
Completed checkpoint up to RBA [0x166.1044.10], SCN: 11698731
Completed checkpoint up to RBA [0x166.2.10], SCN: 11696352


3,建立測試表並插入資料
SQL> conn scott/system
Connected.
SQL> create table t_checkpoint(a int,b int);


Table created.


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


100000 rows created.


SQL> commit;


Commit complete.


4，指定測試表B列為不可用
SQL> alter table t_checkpoint set unused column b;


Table altered.


5，可見指定checkpoint選項後會發生一個檢查點動作


SQL> alter table t_checkpoint drop unused columns checkpoint 500;


Table altered.


Fri Oct 16 08:34:11 2015
Beginning log switch checkpoint up to RBA [0x167.2.10], SCN: 11720087
Thread 1 advanced to log sequence 359 (LGWR switch)
  Current log# 2 seq# 359 mem# 0: /oracle/oradata/guowang/redo02.log




6,如果正在進行檢查點動作，被中斷，會如何呢？
SQL> drop table t_checkpoint purge;


Table dropped.


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


Table created.


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


100000 rows created.


SQL> commit;


Commit complete.  


SQL> alter table t_checkpoint drop unused columns checkpoint;


alter table t_checkpoint drop unused columns checkpoint
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation






SQL> SQL> 


SQL> select table_name,status from user_tables where table_name='T_CHECKPOINT';


TABLE_NAME                                                   STATUS
------------------------------------------------------------ ----------------
T_CHECKPOINT                                                 VALID


可見如果檢查點程式被中斷，不能進行alter table add操作，必須先要讓檢查點執行完成，即執行
  alter table drop columns continue
SQL> alter table t_checkpoint add c int;
alter table t_checkpoint add c int
            *
ERROR at line 1:
ORA-12986: columns in partially dropped state. Submit ALTER TABLE DROP COLUMNS
CONTINUE




SQL> 


SQL> host oerr ora 12986
12986, 00000, "columns in partially dropped state. Submit ALTER TABLE DROP COLUMNS CONTINUE"
// *Cause:  An attempt was made to access a table with columns in partially 
//          dropped state (i.e., drop column operation was interrupted).
// *Action: Submit ALTER TABLE DROP COLUMNS CONTINUE to complete the drop
//          column operation before accessing the table.


select查詢也不成
SQL> select count(*) from t_checkpoint;
select count(*) from t_checkpoint
                     *
ERROR at line 1:
ORA-12986: columns in partially dropped state. Submit ALTER TABLE DROP COLUMNS
CONTINUE


truncate操作是可以成功的
SQL> truncate table t_checkpoint;


Table truncated.




alter table drop columns continue用於繼續執行被中斷的檢查點選項的刪除列操作
SQL> alter table t_checkpoint drop columns continue;


Table altered.


SQL> alter table t_checkpoint add c int;


Table altered.

個人簡介

8年oracle從業經驗，具備豐富的oracle技能，目前在國內北京某專業oracle服務公司從事高階技術顧問。

服務過的客戶：

中國電信

中國移動

中國聯通

中國電通

國家電網

四川達州商業銀行

湖南老百姓大藥房

山西省公安廳

中國郵政

北京302醫院     

河北廊坊新奧集團公司

 專案經驗:

中國電信3G專案AAA系統資料庫部署及最佳化
      中國聯通4G資料庫效能分析與最佳化

中國聯通CRM資料庫效能最佳化

中國移動10086電商平臺資料庫部署及最佳化

湖南老百姓大藥房ERR資料庫sql最佳化專案

四川達州商業銀行TCBS核心業務系統資料庫模型設計和RAC部署及最佳化

四川達州商業銀行TCBS核心業務系統後端批處理儲存過程功能模組編寫及最佳化

北京高鐵訊號監控系統RAC資料庫部署及最佳化

河南宇通客車資料庫效能最佳化

中國電信電商平臺核心採購模組表模型設計及最佳化

中國郵政儲蓄系統資料庫效能最佳化及sql最佳化

北京302醫院資料庫遷移實施

河北廊坊新奧data guard部署及最佳化

山西公安廳身份證審計資料庫系統故障評估

國家電網上海災備專案4 node rac+adg 
       貴州移動crm及客服資料庫效能最佳化專案
       貴州移動crm及客服務資料庫sql稽核專案
       深圳穆迪軟體有限公司資料庫效能最佳化專案

聯絡方式：

手機：18201115468

qq   :   305076427

qq微博: wisdomone1

新浪微博：wisdomone9

qq群：275813900    

itpub部落格名稱：wisdomone1    http://blog.itpub.net/9240380/