<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>

ITPub博客

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

最新文章