alter table set unused之后各种情况处理

如下实验：

　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