[每日一题] OCP1z0-047 :2013-08-24 FLASHBACK—TABLE/PRIMARY KEY(FOREIGN KEY?)......98

转载请注明出处：http://blog.csdn.net/guoyjoe/article/details/10286737

正确答案：D

 

根据题意如下操作：

一、创建表dept

gyj@OCM> 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

gyj@OCM> 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插入数据

gyj@OCM> insert into dept values(10,'IT',null);1 row created.gyj@OCM> insert into dept values(20,'HR',null);1 row created.gyj@OCM> insert into dept(DEPTNO,DNAME) values(10,'IT');1 row created.gyj@OCM> insert into dept(DEPTNO,DNAME) values(20,'HR');1 row created.gyj@OCM> insert into emp(EMPNO,ENAME,DEPTNO) values(1,'KING',10);1 row created.gyj@OCM> insert into emp(EMPNO,ENAME,DEPTNO) values(2,'HARI',20);1 row created.gyj@OCM> COMMIT;Commit complete.

四、查dept和emp的数据及约束。

gyj@OCM> SELECT deptno,dname FROM dept;    DEPTNO DNAME---------- --------------        10 IT        20 HRgyj@OCM> SELECT EMPNO,ENAME,DEPTNO FROM emp;     EMPNO ENAME          DEPTNO---------- ---------- ----------         1 KING               10         2 HARI               20gyj@OCM> col CONSTRAINT_NAME for a10gyj@OCM> col R_CONSTRAINT_NAME for a10gyj@OCM> col TABLE_NAME for a10gyj@OCM> col INDEX_NAME for a10gyj@OCM> 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 ENABLEDPK_EMP                EMP        PK_EMP     P ENABLEDFK_DEPTNO  PK_DEPT    EMP                   R ENABLED

五、删除表emp

gyj@OCM> drop table emp;Table dropped.

六、查回收站，可以看出表和索引同时被删除除

gyj@OCM> 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:40BIN$5LNox53qT0PgQ4rZqMD+/Q==$0 EMP        DROP      TABLE      2013-08-24:22:33:40

七、并且可以查出表emp的外键也被删除了

gyj@OCM> 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

gyj@OCM> flashback table emp to before drop;Flashback complete.

九、查回收站，已没信息，说明表和唯一索引（主键约束）都被闪回了，但外键没有被闪回（失去了外键的制约），如下操作。

gyj@OCM> select  OBJECT_NAME,ORIGINAL_NAME,OPERATION,TYPE ,DROPTIME from recyclebin;no rows selectedgyj@OCM> SELECT EMPNO,ENAME,DEPTNO FROM emp;     EMPNO ENAME          DEPTNO---------- ---------- ----------         1 KING               10         2 HARI               20gyj@OCM> col index_name for a50gyj@OCM> select index_name from user_indexes where table_name='EMP';INDEX_NAME--------------------------------------------------BIN$5LNox53pT0PgQ4rZqMD+/Q==$0gyj@OCM> col CONSTRAINT_NAME for a40 gyj@OCM> col index_name for a40gyj@OCM> 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 ENABLEDBIN$5LNox53oT0PgQ4rZqMD+/Q==$0                      EMP        BIN$5LNox53pT0PgQ4rZqMD+/Q==$0           P ENABLED

十、插入两条数据，第一次插入报错(emp中已存在empno为2的员工了，empno是主键)，第二次插入成功。

gyj@OCM> 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) violatedgyj@OCM> INSERT INTO emp(EMPNO,ENAME,DEPTNO) values(3,'ING',55);1 row created.gyj@OCM> 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，最好修改索引和约束，如下操作：

gyj@OCM> ALTER INDEX "BIN$5LNox53pT0PgQ4rZqMD+/Q==$0" RENAME  TO PK_EMP;Index altered.gyj@OCM> ALTER TABLE EMP RENAME CONSTRAINT "BIN$5LNox53oT0PgQ4rZqMD+/Q==$0" TO PK_EMP;Table altered.gyj@OCM> 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 ENABLEDPK_EMP                                              EMP        PK_EMP                                   P ENABLEDgyj@OCM> select index_name from user_indexes where table_name='EMP';INDEX_NAME----------------------------------------PK_EMP

呵呵，这样插入记录时报错就能看到正常的约束名称了。

gyj@OCM>  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：[e-mail]

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