oracle11g alter table set unused column指定表某列不可用之系列一
结论
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(表中列的列表，以逗号分割)，可以同时指定多个列不可用
测试
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
个人简介
8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。
服务过的客户：
中国电信
中国移动
中国联通
中国电通
国家电网
四川达州商业银行
湖南老百姓大药房
山西省公安厅
中国邮政
北京302医院
河北廊坊新奥集团公司
项目经验:
中国电信3G项目AAA系统数据库部署及优化
中国联通4G数据库性能分析与优化
中国联通4G数据库性能分析与优化
中国联通CRM数据库性能优化
中国移动10086电商平台数据库部署及优化
湖南老百姓大药房ERR数据库sql优化项目
四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化
四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化
北京高铁信号监控系统RAC数据库部署及优化
河南宇通客车数据库性能优化
中国电信电商平台核心采购模块表模型设计及优化
中国邮政储蓄系统数据库性能优化及sql优化
北京302医院数据库迁移实施
河北廊坊新奥data guard部署及优化
山西公安厅身份证审计数据库系统故障评估
国家电网上海灾备项目4 node rac+adg
贵州移动crm及客服数据库性能优化项目
贵州移动crm及客服务数据库sql审核项目
深圳穆迪软件有限公司数据库性能优化项目
贵州移动crm及客服数据库性能优化项目
贵州移动crm及客服务数据库sql审核项目
深圳穆迪软件有限公司数据库性能优化项目
联系方式：
手机：18201115468
qq : 305076427
qq微博: wisdomone1
新浪微博：wisdomone9
qq群：275813900
itpub博客名称：wisdomone1 http://blog.itpub.net/9240380/
正文到此结束
热门推荐
相关文章
近期评论
-
666
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
