Evaluate the following SQL statement:
ALTER TABLE hr.emp SET UNUSED (mgr_id);
Which statement is true regarding the effect of the above SQL statement?
A. Any synonym existing on the EMP table would have to be re-created.
B. Any constraints defined on the MGR_ID column would be removed by the above command.
C. Any views created on the EMP table that include the MGR_ID column would have to be dropped and recreated.
D. Any index created on the MGR_ID column would continue to exist until the DROP UNUSED COLUMNS command is executed.
Answer: B
验证过程:我在soctt用户下复制emp表为tt1表,在tt1表上进行做验证。
SQL> show user;
USER is "SCOTT"
SQL> create table tt1 as select * from emp; ----创建tt1表
Table created.
SQL> desc tt1;
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> alter table tt1 add constraint empno_pk primary key(empno); ----在empno列上添加主键
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name='TT1';
TABLE_NAME CONSTRAINT_NAME C STATUS
------------------------------ ------------------------------ - --- -----
TT1 EMPNO_PK P ENABLED
SQL> select index_name from user_indexes where table_name='TT1'; ----tt1表生产了索引
INDEX_NAME
------------------------------
EMPNO_PK
SQL> create or replace view tt1_view as select empno,ename,sal from tt1; ----在tt1表上创建视图tt1_view
View created.
SQL> select * from tt1_view;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 902
7499 ALLEN 1702
7521 WARD 1352
7566 JONES 3077
7654 MARTIN 1352
7698 BLAKE 2952
7782 CLARK 2552
7788 SCOTT 3102
7839 KING 5102
7844 TURNER 1602
7876 ADAMS 1202
7900 JAMES 1052
7902 FORD 3102
7934 MILLER 1402
14 rows selected.
SQL> show user; ----在sys用户下为scott.tt1创建同义词
USER is "SYS"
SQL> create synonym tt1 for scott.tt1;
Synonym created.
SQL> show user; ---在scott用户下执行命令 alter table tt1 set unused column empno
USER is "SCOTT"
SQL> alter table tt1 set unused column empno;
Table altered.
验证选项B:查看tt1表在empno上的主键
查询结果看出定义在empno上的主键已经被删除。
验证选项C:查看在tt1表上创建的视图tt1_view(包含empno列)
验证选项D:查看在tt1.emp上索引是否存在
验证选项A:在sys用户下查看同义词
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name='TT1';
no rows selected
查询结果看出定义在empno上的主键已经被删除。
验证选项C:查看在tt1表上创建的视图tt1_view(包含empno列)
SQL> select * from tt1_view;
select * from tt1_view
*
ERROR at line 1:
ORA-04063: view "SCOTT.TT1_VIEW" has errors(视图出错,但未被删除)
验证选项D:查看在tt1.emp上索引是否存在
SQL> select index_name from user_indexes where table_name='TT1';
no rows selected
查询结果看出索引已经被删除。
验证选项A:在sys用户下查看同义词
SQL> show user;
USER is "SYS"
SQL> select * from tt1;
ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- ---------- --------- ---------- ---------- ----------
SMITH CLERK 7902 17-DEC-80 902 20
ALLEN SALESMAN 7698 12-MAR-07 1702 30
WARD SALESMAN 7698 22-FEB-81 1352 500 30
JONES MANAGER 7839 02-APR-81 3077 20
MARTIN SALESMAN 7698 28-SEP-81 1352 1400 30
BLAKE MANAGER 7839 01-MAY-81 2952 30
CLARK MANAGER 7839 09-JUN-81 2552 10
SCOTT ANALYST 7566 19-APR-87 3102 20
KING PRESIDENT 17-NOV-81 5102 10
TURNER SALESMAN 7698 08-SEP-81 1602 0 30
ADAMS CLERK 7788 23-MAY-87 1202 20
JAMES CLERK 7698 03-DEC-81 1052 30
FORD ANALYST 7566 03-DEC-81 3102 20
MILLER CLERK 7782 23-JAN-82 1402 10
14 rows selected.
查询结果看出表tt1上的同义词还能正常使用,不用重建。
总结:命令alter table xxx set unuserd ***
致使在该列上创建的约束和索引都会被立即删除;
导致包含该列的view出错,但不会被删除;
但为该表建立的synonym还会正常使用。
注意:该命令是不可逆操作,某列一旦被set unused,就不会再恢复。
致使在该列上创建的约束和索引都会被立即删除;
导致包含该列的view出错,但不会被删除;
但为该表建立的synonym还会正常使用。
注意:该命令是不可逆操作,某列一旦被set unused,就不会再恢复。