The following experiment:
gyj@ocm> Create Table EMP (
2 Empno Number (4) is 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 (M, ' Yijun ', ' Guo ', 35000, 1);
1 row created.
gyj@ocm>commit;
Commit complete.
gyj@ocm> altertable emp Drop column first_name; --data can also be deleted! Exclude Answer 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----Verify that answer b is right.
SET Unused Clause---official explanation, excluding answer C
Specify SET unused to mark one or more columns asunused. Specifying this clause does is not actually remove the target columns Fromeach row in the table. The It does not restore the disk space used Bythese columns. Therefore, the response time are faster than when you execute THEDROP clause.
Clear out the dictionary information (undo storage) and not be recoverable.
Do not immediately drop column, you should first setunused column can not be used to avoid the system peak time to deal with the deletion of data in the field, to note that one but you set unused column, this field can not be reused.
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.
------verified the answer D was right.
Answer: BD
See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/