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