[每日一题] OCP1z0-047 :2013-07-15 drop column
如下实验：
[email protected]> Create table emp(
2Empno number(4) not null,
3First_name varchar2(20),
4Last_name varchar2(20),
5Salary number(10,2),
6Deptno number(2)
7 );
Table created.
[email protected]> insertinto emp values(100,'yijun','guo',35000,1);
1 row created.
[email protected]>commit;
Commit complete.
[email protected]> altertable emp drop column first_name; --有数据也能被删除！排除答案A
Table altered.
[email protected]> altertable emp drop column Empno;
Table altered.
[email protected]> altertable emp drop column last_name;
Table altered.
[email protected]> altertable emp drop column salary;
Table altered.
[email protected]> 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 asunused. Specifying this clause does not actually remove the target columns fromeach 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 theDROP clause.
清楚掉字典信息（撤消存储空间），不可恢复。
不要马上drop column，应该先setunused让column无法使用，避开系统尖峰时间再来处理删除栏位里的资料，要注意的是一但你set unused column，这个栏位是无法再回复使用的。
[email protected]> altertable emp add first_name varchar2(10);
Table altered.
[email protected]> select *from emp;
DEPTNO FIRST_NAME
---------- ----------
1
[email protected]> updateemp set first_name='yijun';
1 row updated.
[email protected]> commit;
Commit complete.
[email protected]> ALTERTABLE emp SET UNUSED (first_name);
Table altered.
[email protected]> altertable emp drop unused column;
Table altered.
[email protected]> select *from emp;
DEPTNO
----------
1
[email protected]> alter table emp add first_name varchar2(10);
Table altered.
[email protected]> select * from emp
2;
DEPTNO FIRST_NAME
---------- ----------
1
[email protected]> update emp set first_name='yijun';
1 row updated.
[email protected]> commit;
Commit complete.
[email protected]> alter table emp add primary key(deptno,first_name);
Table altered.
[email protected]> 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
[email protected]> alter table emp drop column first_name cascade constraints;
Table altered.
------验证了答案D是对的
答案:BD