1.删除列语法示例（删除t表的x列）
alter table sec.t drop column x cascade constraints checkpoint 10000;
当然在表比较小，没有太大的维护成本的情况下，可以考虑使用最简洁的语法进行列删除：
alter table sec.t drop column x;
2.注释一下上面第一条SQL关键的两处
1）cascade constraints不用过多解释，就是连带约束一同删除
2）checkpoint 10000表示Oracle每做10000行的删除操作就产生一个检查点，这个数据可以任意指定，目的是在删除具有海量数据大表的列的时候提高效率，这样可以减少还原磁盘空间的使用量，如果系统在删除期间异常崩溃了，重新启动系统后，该删除命令可以从检查点重新开始它的工作，不必重新开始，节省时间。
3.异常崩溃后重新启动删除命令的SQL语句如下：
alter table sec.t drop columns continue;
4.使用“set unused”瞬间让某一列（或某几列）瞬间消失
Oracle的“set unused”子句可以瞬间完成删除列的效果。不过这种方法只是暂时的屏蔽掉了具体的列定义，应用程序无法感知到列的信息，达到删除的效果。
在系统非常繁忙的情况下，可以考虑使用这种方法进行列的删除维护，然后寻找一个大块的维护窗口，再完成漫长的列删除操作。
“set unused”语法如下：
alter table sec.t set unused column x cascade constraints;
找到维护窗口后，可以使用下面的SQL语句将设置为unused的列进行真正的物理删除
alter table sec.t drop unused columns checkpoint 10000;
5.最后我们用实验演示一下上面的这个过程
为了模拟海量数据删除的情况，我们这里创建一个包含1个亿的测试用表t，我们的目标是删除这个表的主键字段x。
1）测试用表t的基本情况
（1）表结构如下
sec@ora10g> desc t;
Name Null? Type
--------------- -------- ----------------
X NOT NULL NUMBER
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
（2）确认一下记录数
sec@ora10g> select count(*) from t;
COUNT(*)
----------
100000000
Elapsed: 00:00:05.10
（3）查询获得测试用表T占用段空间10G
sec@ora10g> select bytes/1024/1024/1024 GB from dba_segments where segment_name = 'T';
GB
----------
10.375
2）在删除的过程中模拟数据库服务器断电故障
（1）在session 1中执行删除操作
sec@ora10g> alter table sec.t drop column x cascade constraints checkpoint 10000;
（2）在session 2中模拟断电，这里使用“shutdown abort”命令强制关闭数据库（请千万注意您操作的确实是纯测试用数据库，不要在生产系统上这样“玩火自焚”）
sys@ora10g> shutdown abort;
ORACLE instance shut down.
（3）这时session 1中的删除语句会马上收到如下的报错信息，表名这个SQL语句在执行16秒之后因为异常被迫终止了
sec@ora10g> alter table sec.t drop column x cascade constraints checkpoint 10000;
ERROR:
ORA-03114: not connected to ORACLE
alter table sec.t drop column x cascade constraints checkpoint 10000
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Elapsed: 00:00:16.24
（4）重新启动数据库
sys@ora10g> startup;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2074152 bytes
Variable Size 536873432 bytes
Database Buffers 1593835520 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.
（5）在session 1中从新启用drop命令
因为模拟的数据过大，导致这里等待了N久，而且从后台监控发现，各种资源消耗严重，因此类似这样的操作一定不要在生产高峰时段进行操作，否则您会有一种想“自残”的冲动和懊恼：）
经过了漫长并且煎熬的等待，终于在35分钟之后完成了这个删除操作
sec@ora10g> alter table sec.t drop columns continue;
Table altered.
Elapsed: 00:35:21.43
这样删除之后，连同该列对应的主键约束也会一同被删除掉。因为没有索引的帮助下，下面的查询语句使用了近1分钟的时间（上面在存在索引的情况用时5秒左右）
sec@ora10g> select count(*) from t;
COUNT(*)
----------
100000000
Elapsed: 00:00:54.60
（6）如果使用“set unused”方法将待删除字段置为不可用，看一下效率（毫秒级速度），不过一定要注意这种方式的原理是清楚掉字典信息，不可恢复！！（当然也不要过分担心，既然数据时存在的，也是有极端的恢复手段的，可以通过手工修改tab$ col$基表的方式进行恢复）
sec@ora10g> alter table sec.t set unused column x cascade constraints;
Table altered.
Elapsed: 00:00:00.16
通过user_tab_cols视图可以得到操作前和操作后的隐藏字段的相关信息
操作前查询结果：
sec@ora10g> select table_name,column_name,hidden_column from user_tab_cols where table_name='T';
TA COLUMN_NAME HID
-- ------------------------------ ---
T SECONDARY NO
T GENERATED NO
T TEMPORARY NO
T STATUS NO
T TIMESTAMP NO
T LAST_DDL_TIME NO
T CREATED NO
T OBJECT_TYPE NO
T DATA_OBJECT_ID NO
T OBJECT_ID NO
T SUBOBJECT_NAME NO
T OBJECT_NAME NO
T OWNER NO
T X NO
14 rows selected.
操作后查询结果：注意观察最后一行的提示信息
sec@ora10g> col TABLE_NAME for a2
sec@ora10g> col COLUMN_NAME for a30
sec@ora10g> select table_name,column_name,hidden_column from user_tab_cols where table_name='T';
TA COLUMN_NAME HID
-- ------------------------------ ---
T SECONDARY NO
T GENERATED NO
T TEMPORARY NO
T STATUS NO
T TIMESTAMP NO
T LAST_DDL_TIME NO
T CREATED NO
T OBJECT_TYPE NO
T DATA_OBJECT_ID NO
T OBJECT_ID NO
T SUBOBJECT_NAME NO
T OBJECT_NAME NO
T OWNER NO
T SYS_C00001_09100821:54:55$ YES
14 rows selected.
此时如果，您万分确认可以清除掉这个列的时候，可以使用下面的SQL完成彻底的删除
alter table sec.t drop unused columns checkpoint 10000;
6.小结
仅仅一个删除列的简单操作就可以引申出很多有趣的话题，探索无止境ing...
-- The End --
alter table sec.t drop column x cascade constraints checkpoint 10000;
当然在表比较小，没有太大的维护成本的情况下，可以考虑使用最简洁的语法进行列删除：
alter table sec.t drop column x;
2.注释一下上面第一条SQL关键的两处
1）cascade constraints不用过多解释，就是连带约束一同删除
2）checkpoint 10000表示Oracle每做10000行的删除操作就产生一个检查点，这个数据可以任意指定，目的是在删除具有海量数据大表的列的时候提高效率，这样可以减少还原磁盘空间的使用量，如果系统在删除期间异常崩溃了，重新启动系统后，该删除命令可以从检查点重新开始它的工作，不必重新开始，节省时间。
3.异常崩溃后重新启动删除命令的SQL语句如下：
alter table sec.t drop columns continue;
4.使用“set unused”瞬间让某一列（或某几列）瞬间消失
Oracle的“set unused”子句可以瞬间完成删除列的效果。不过这种方法只是暂时的屏蔽掉了具体的列定义，应用程序无法感知到列的信息，达到删除的效果。
在系统非常繁忙的情况下，可以考虑使用这种方法进行列的删除维护，然后寻找一个大块的维护窗口，再完成漫长的列删除操作。
“set unused”语法如下：
alter table sec.t set unused column x cascade constraints;
找到维护窗口后，可以使用下面的SQL语句将设置为unused的列进行真正的物理删除
alter table sec.t drop unused columns checkpoint 10000;
5.最后我们用实验演示一下上面的这个过程
为了模拟海量数据删除的情况，我们这里创建一个包含1个亿的测试用表t，我们的目标是删除这个表的主键字段x。
1）测试用表t的基本情况
（1）表结构如下
sec@ora10g> desc t;
Name Null? Type
--------------- -------- ----------------
X NOT NULL NUMBER
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
（2）确认一下记录数
sec@ora10g> select count(*) from t;
COUNT(*)
----------
100000000
Elapsed: 00:00:05.10
（3）查询获得测试用表T占用段空间10G
sec@ora10g> select bytes/1024/1024/1024 GB from dba_segments where segment_name = 'T';
GB
----------
10.375
2）在删除的过程中模拟数据库服务器断电故障
（1）在session 1中执行删除操作
sec@ora10g> alter table sec.t drop column x cascade constraints checkpoint 10000;
（2）在session 2中模拟断电，这里使用“shutdown abort”命令强制关闭数据库（请千万注意您操作的确实是纯测试用数据库，不要在生产系统上这样“玩火自焚”）
sys@ora10g> shutdown abort;
ORACLE instance shut down.
（3）这时session 1中的删除语句会马上收到如下的报错信息，表名这个SQL语句在执行16秒之后因为异常被迫终止了
sec@ora10g> alter table sec.t drop column x cascade constraints checkpoint 10000;
ERROR:
ORA-03114: not connected to ORACLE
alter table sec.t drop column x cascade constraints checkpoint 10000
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Elapsed: 00:00:16.24
（4）重新启动数据库
sys@ora10g> startup;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2074152 bytes
Variable Size 536873432 bytes
Database Buffers 1593835520 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.
（5）在session 1中从新启用drop命令
因为模拟的数据过大，导致这里等待了N久，而且从后台监控发现，各种资源消耗严重，因此类似这样的操作一定不要在生产高峰时段进行操作，否则您会有一种想“自残”的冲动和懊恼：）
经过了漫长并且煎熬的等待，终于在35分钟之后完成了这个删除操作
sec@ora10g> alter table sec.t drop columns continue;
Table altered.
Elapsed: 00:35:21.43
这样删除之后，连同该列对应的主键约束也会一同被删除掉。因为没有索引的帮助下，下面的查询语句使用了近1分钟的时间（上面在存在索引的情况用时5秒左右）
sec@ora10g> select count(*) from t;
COUNT(*)
----------
100000000
Elapsed: 00:00:54.60
（6）如果使用“set unused”方法将待删除字段置为不可用，看一下效率（毫秒级速度），不过一定要注意这种方式的原理是清楚掉字典信息，不可恢复！！（当然也不要过分担心，既然数据时存在的，也是有极端的恢复手段的，可以通过手工修改tab$ col$基表的方式进行恢复）
sec@ora10g> alter table sec.t set unused column x cascade constraints;
Table altered.
Elapsed: 00:00:00.16
通过user_tab_cols视图可以得到操作前和操作后的隐藏字段的相关信息
操作前查询结果：
sec@ora10g> select table_name,column_name,hidden_column from user_tab_cols where table_name='T';
TA COLUMN_NAME HID
-- ------------------------------ ---
T SECONDARY NO
T GENERATED NO
T TEMPORARY NO
T STATUS NO
T TIMESTAMP NO
T LAST_DDL_TIME NO
T CREATED NO
T OBJECT_TYPE NO
T DATA_OBJECT_ID NO
T OBJECT_ID NO
T SUBOBJECT_NAME NO
T OBJECT_NAME NO
T OWNER NO
T X NO
14 rows selected.
操作后查询结果：注意观察最后一行的提示信息
sec@ora10g> col TABLE_NAME for a2
sec@ora10g> col COLUMN_NAME for a30
sec@ora10g> select table_name,column_name,hidden_column from user_tab_cols where table_name='T';
TA COLUMN_NAME HID
-- ------------------------------ ---
T SECONDARY NO
T GENERATED NO
T TEMPORARY NO
T STATUS NO
T TIMESTAMP NO
T LAST_DDL_TIME NO
T CREATED NO
T OBJECT_TYPE NO
T DATA_OBJECT_ID NO
T OBJECT_ID NO
T SUBOBJECT_NAME NO
T OBJECT_NAME NO
T OWNER NO
T SYS_C00001_09100821:54:55$ YES
14 rows selected.
此时如果，您万分确认可以清除掉这个列的时候，可以使用下面的SQL完成彻底的删除
alter table sec.t drop unused columns checkpoint 10000;
6.小结
仅仅一个删除列的简单操作就可以引申出很多有趣的话题，探索无止境ing...
-- The End --
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/70034820/viewspace-2993999/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- 中国OCM联盟隆重落户ITPUB社区，百位OCM已入驻，欢迎更多的OCM加入我们哦！
- OCMU联盟-一个有温度的联盟
- 丢掉盔甲，我们依然是骄傲的骑士——Secooler“三问四看”话认证培训
- 【数据安全】一次惊心动魄的ASM磁盘头损坏故障处理过程带来的深思
- 【参数】恢复db_recovery_file_dest_size参数为默认值“0”方法
- 【如何做快乐的DBA】ITPUB社区第一期博客专家专题“如何做快乐的DBA”
- 【Bitmap Index】B-Tree索引与Bitmap位图索引的锁代价比较研究
- 【RMAN】使用RMAN的Duplicate功能创建物理DataGuard报错（ORA-17627、ORA-17629）处理
- 【CUBE】Oracle分组函数之CUBE魅力
- 【ROLLUP】Oracle分组函数之ROLLUP魅力
