alter table set unused之后各种情况处理
来源:互联网 发布:seo专有名词大全 编辑:程序博客网 时间:2024/05/04 15:23
如下实验:
gyj@OCM> Create table emp(
2 Empno number(4) not null,
3 First_name varchar2(20),
4 Last_name varchar2(20),
5 Salary number(10,2),
6 Deptno number(2)
7 );
Table created.
gyj@OCM> insertinto emp values(100,'yijun','guo',35000,1);
1 row created.
gyj@OCM>commit;
Commit complete.
gyj@OCM> altertable emp drop column first_name; --有数据也能被删除!排除答案A
Table altered.
gyj@OCM> altertable emp drop column Empno;
Table altered.
gyj@OCM> altertable emp drop column last_name;
Table altered.
gyj@OCM> altertable emp drop column salary;
Table altered.
gyj@OCM> altertable emp drop column deptno;
alter table empdrop column deptno
*
ERROR at line 1:
ORA-12983: cannotdrop all columns in a table ----验证了答案B是对的
SET UNUSED Clause ---官方解释,排除答案C
Specify SET UNUSED to mark one or more columns as unused. Specifying this clause does not actually remove the target columns from each row in the table. That is, it does not restore the disk space used bythese columns. Therefore, the response time is faster than when you execute the DROP clause(即不带UNUSED的 DROP clause).
SET UNUSED Clause会撤消相关列的存储空间,并修正与存储空间字典信息,故而相关列的存储空间不可恢复。当然,这些列的列名等信息并未从系统字典信息里清除掉。
不要马上drop column,应该先set unused让column无法使用,set unused命令会避开系统尖峰时间再来处理删除栏位里的资料。要注意的是一但你set unused column,这个栏位是无法再回复使用的。
用法如下:
gyj@OCM> ALTERTABLE emp SET UNUSED (first_name);
Table altered.
gyj@OCM> altertable emp drop unused column;
Table altered.
gyj@OCM> altertable emp add first_name varchar2(10);
Table altered.
gyj@OCM> select *from emp;
DEPTNO FIRST_NAME
---------- ----------
1
gyj@OCM> updateemp set first_name='yijun';
1 row updated.
gyj@OCM> commit;
Commit complete.
gyj@OCM> ALTERTABLE emp SET UNUSED (first_name);
Table altered.
gyj@OCM> altertable emp drop unused column;
Table altered.
gyj@OCM> select *from emp;
DEPTNO
----------
1
gyj@OCM> alter table emp add first_name varchar2(10);
Table altered.
gyj@OCM> select * from emp
2 ;
DEPTNO FIRST_NAME
---------- ----------
1
gyj@OCM> update emp set first_name='yijun';
1 row updated.
gyj@OCM> commit;
Commit complete.
gyj@OCM> alter table emp add primary key(deptno,first_name);
Table altered.
yj@OCM> alter table emp drop column first_name;
alter table emp drop column first_name
*
ERROR at line 1:
ORA-12991: column is referenced in a multi-column constraint
gyj@OCM> alter table emp drop column first_name cascade constraints;
Table altered.
------验证了答案D是对的
答案:BD
- alter table set unused之后各种情况处理
- [每日一题] OCP1z0-047 :2013-07-26 alter table set unused之后各种情况处理..................15
- alter set unused用法
- oracle 删除字段方法 alter table set unused
- alter table t_agent_adv alter column status set default 1;
- 恢复SET UNUSED操作
- 恢复SET UNUSED操作
- alter table
- ALTER TABLE
- Alter Table
- Alter table
- alter table
- ALTER TABLE
- alter table
- alter table
- ALTER TABLE
- 指针处理大数各种情况
- Oracle set unused的用法
- 宏定义
- Android代码优化技术
- 数据类型-Dictionary
- ie6-ie9中不支持table.innerHTML的问题
- 面对新语言和跳槽,程序员如何保持技术优势?
- alter table set unused之后各种情况处理
- AVL树的Java实现(递归方法)
- hdu 2019 数列有序!
- MongoDB与TokuMX在Sharding Balancer的性能比较
- SQL多表连接查询(详细实例)
- linux系统安全配置规范
- STL中与快速排序媲美的merge sort
- 从底部升起的Menu_动画效果
- IBinder对象在进程间传递的形式(二)