轉載請注明出處：http://blog.csdn.net/guoyjoe/article/details/10286737
正确答案：D
根據題意如下操作：
一、建立表dept
[email protected]> CREATE TABLE DEPT
2 (DEPTNO NUMBER(2,0),
3 DNAME VARCHAR2(14),
4 LOC VARCHAR2(13),
5 CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO)
6 );
Table created.
二、建立表emp
[email protected]> CREATE TABLE EMP
2 (EMPNO NUMBER(4,0),
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4,0),
6 HIREDATE DATE,
7 SAL NUMBER(7,2),
8 COMM NUMBER(7,2),
9 DEPTNO NUMBER(2,0),
10 CONSTRAINT PK_EMP PRIMARY KEY (EMPNO),
11 CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO)
12 REFERENCES DEPT (DEPTNO) ENABLE
13 );
Table created.
三、分别向表dept和表emp插入資料
[email protected]> insert into dept values(10,'IT',null);
1 row created.
[email protected]> insert into dept values(20,'HR',null);
1 row created.
[email protected]> insert into dept(DEPTNO,DNAME) values(10,'IT');
1 row created.
[email protected]> insert into dept(DEPTNO,DNAME) values(20,'HR');
1 row created.
[email protected]> insert into emp(EMPNO,ENAME,DEPTNO) values(1,'KING',10);
1 row created.
[email protected]> insert into emp(EMPNO,ENAME,DEPTNO) values(2,'HARI',20);
1 row created.
[email protected]> COMMIT;
Commit complete.
四、查dept和emp的資料及限制。
[email protected]> SELECT deptno,dname FROM dept;
DEPTNO DNAME
---------- --------------
10 IT
20 HR
[email protected]> SELECT EMPNO,ENAME,DEPTNO FROM emp;
EMPNO ENAME DEPTNO
---------- ---------- ----------
1 KING 10
2 HARI 20
[email protected]> col CONSTRAINT_NAME for a10
[email protected]> col R_CONSTRAINT_NAME for a10
[email protected]> col TABLE_NAME for a10
[email protected]> col INDEX_NAME for a10
[email protected]> select CONSTRAINT_NAME,R_CONSTRAINT_NAME,TABLE_NAME,INDEX_NAME,CONSTRAINT_TYPE,STATUS
2 from user_constraints where table_name
3 in('EMP','DEPT');
CONSTRAINT R_CONSTRAI TABLE_NAME INDEX_NAME C STATUS
---------- ---------- ---------- ---------- - --------
PK_DEPT DEPT PK_DEPT P ENABLED
PK_EMP EMP PK_EMP P ENABLED
FK_DEPTNO PK_DEPT EMP R ENABLED
五、删除表emp
[email protected]> drop table emp;
Table dropped.
六、查資源回收筒，可以看出表和索引同時被删除除
[email protected]> select OBJECT_NAME,ORIGINAL_NAME,OPERATION,TYPE ,DROPTIME from recyclebin;
OBJECT_NAME ORIGINAL_N OPERATION TYPE DROPTIME
------------------------------ ---------- --------- ---------- -------------------
BIN$5LNox53pT0PgQ4rZqMD+/Q==$0 PK_EMP DROP INDEX 2013-08-24:22:33:40
BIN$5LNox53qT0PgQ4rZqMD+/Q==$0 EMP DROP TABLE 2013-08-24:22:33:40
七、并且可以查出表emp的外鍵也被删除了
[email protected]> select CONSTRAINT_NAME,R_CONSTRAINT_NAME,TABLE_NAME,INDEX_NAME,CONSTRAINT_TYPE,STATUS
2 from user_constraints where table_name
3 in('EMP','DEPT');
CONSTRAINT R_CONSTRAI TABLE_NAME INDEX_NAME C STATUS
---------- ---------- ---------- ---------- - --------
PK_DEPT DEPT PK_DEPT P ENABLED
八、閃回表emp
[email protected]> flashback table emp to before drop;
Flashback complete.
九、查資源回收筒，已沒資訊，說明表和唯一索引（主鍵限制）都被閃回了，但外鍵沒有被閃回（失去了外鍵的制約），如下操作。
[email protected]> select OBJECT_NAME,ORIGINAL_NAME,OPERATION,TYPE ,DROPTIME from recyclebin;
no rows selected
[email protected]> SELECT EMPNO,ENAME,DEPTNO FROM emp;
EMPNO ENAME DEPTNO
---------- ---------- ----------
1 KING 10
2 HARI 20
[email protected]> col index_name for a50
[email protected]> select index_name from user_indexes where table_name='EMP';
INDEX_NAME
--------------------------------------------------
BIN$5LNox53pT0PgQ4rZqMD+/Q==$0
[email protected]> col CONSTRAINT_NAME for a40
[email protected]> col index_name for a40
[email protected]> select CONSTRAINT_NAME,R_CONSTRAINT_NAME,TABLE_NAME,INDEX_NAME,CONSTRAINT_TYPE,STATUS
2 from user_constraints where table_name
3 in('EMP','DEPT');
CONSTRAINT_NAME R_CONSTRAI TABLE_NAME INDEX_NAME C STATUS
---------------------------------------- ---------- ---------- ---------------------------------------- - --------
PK_DEPT DEPT PK_DEPT P ENABLED
BIN$5LNox53oT0PgQ4rZqMD+/Q==$0 EMP BIN$5LNox53pT0PgQ4rZqMD+/Q==$0 P ENABLED
十、插入兩條資料，第一次插入報錯(emp中已存在empno為2的員工了，empno是主鍵)，第二次插入成功。
[email protected]> INSERT INTO emp(EMPNO,ENAME,DEPTNO) values(2,'COTT',10);
INSERT INTO emp(EMPNO,ENAME,DEPTNO) values(2,'COTT',10)
*
ERROR at line 1:
ORA-00001: unique constraint (GYJ.BIN$5LNox53oT0PgQ4rZqMD+/Q==$0) violated
[email protected]> INSERT INTO emp(EMPNO,ENAME,DEPTNO) values(3,'ING',55);
1 row created.
[email protected]> SELECT EMPNO,ENAME,DEPTNO FROM emp;
EMPNO ENAME DEPTNO
---------- ---------- ----------
1 KING 10
2 HARI 20
3 ING 55
十一、閃回索引和限制的名稱還是：BIN$5LNox53pT0PgQ4rZqMD+/Q==$0和BIN$5LNox53oT0PgQ4rZqMD+/Q==$0，最好修改索引和限制，如下操作：
[email protected]> ALTER INDEX "BIN$5LNox53pT0PgQ4rZqMD+/Q==$0" RENAME TO PK_EMP;
Index altered.
[email protected]> ALTER TABLE EMP RENAME CONSTRAINT "BIN$5LNox53oT0PgQ4rZqMD+/Q==$0" TO PK_EMP;
Table altered.
[email protected]> select CONSTRAINT_NAME,R_CONSTRAINT_NAME,TABLE_NAME,INDEX_NAME,CONSTRAINT_TYPE,STATUS
2 from user_constraints where table_name
3 in('EMP','DEPT');
CONSTRAINT_NAME R_CONSTRAI TABLE_NAME INDEX_NAME C STATUS
---------------------------------------- ---------- ---------- ---------------------------------------- - --------
PK_DEPT DEPT PK_DEPT P ENABLED
PK_EMP EMP PK_EMP P ENABLED
[email protected]> select index_name from user_indexes where table_name='EMP';
INDEX_NAME
----------------------------------------
PK_EMP
呵呵，這樣插入記錄時報錯就能看到正常的限制名稱了。
[email protected]> INSERT INTO emp(EMPNO,ENAME,DEPTNO) values(2,'COTT',10);
INSERT INTO emp(EMPNO,ENAME,DEPTNO) values(2,'COTT',10)
*
ERROR at line 1:
ORA-00001: unique constraint (GYJ.PK_EMP) violated
總結：flashback table閃回表的同時也閃回了索引（此索引是建主鍵限制時産生的唯一索引，即也閃回了主鍵限制），但沒有閃回外鍵限制。
QQ：252803295
學習交流QQ群：
DSI&Core Search Ⅰ 群：127149411（技術:已滿）
DSI&Core Search Ⅱ 群：177089463（技術:未滿）
DSI&Core Search Ⅲ 群：284596437（技術:未滿）
DSI&Core Search Ⅳ 群：192136702（技術:未滿）
DSI&Core Search Ⅴ 群：285030382（閑聊:未滿）
MAIL：[email protected]
BLOG： http://blog.csdn.net/guoyjoe
WEIBO：http://weibo.com/guoyJoe0218
ITPUB： http://www.itpub.net/space-uid-28460966.html
OCM： http://education.oracle.com/education/otn/YGuo.HTM