OCP-FLASHBACK

OCP考点之FLASHBACK

对flashback还不熟，看到ocp上一个关于flashback的题目，下面针对题目的一点内容做了一下测试。

题目是考关于对一个表做了flashback之后，主键约束、唯一索引、外键约束是否会自动恢复。

以下是测试过程：

SQL> DESC TEST_1
Name Type Nullable Default Comments
----- ------------ -------- ------- --------
SID VARCHAR2(10)
SNAME VARCHAR2(20) Y

SQL> DESC T_TEST_FK
Name Type Nullable Default Comments
----- ------------ -------- ------- --------
SID VARCHAR2(10) Y
SNAME VARCHAR2(10)

SQL> ALTER TABLE test_1 ADD CONSTRAINT pk_test_1 PRIMARY KEY (sid);

Table altered

SQL> ALTER TABLE T_TEST_FK ADD CONSTRAINT PK_TEST_FK PRIMARY KEY(SNAME)
2 ;

Table altered

SQL> ALTER TABLE t_test_fk ADD CONSTRAINT fk_test_fk FOREIGN KEY (sid) REFERENCES test_1(sid);

Table altered

SQL>
SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME
2 FROM Dba_Constraints
3 WHERE table_name = 'T_TEST_FK'
4 ;

CONSTRAINT_NAME CONSTRAINT_TYPE R_CONSTRAINT_NAME
------------------------------ --------------- ------------------------------
PK_TEST_FK P
FK_TEST_FK R PK_TEST_1

SQL> SELECT INDEX_NAME,INDEX_TYPE,UNIQUENESS FROM DBA_INDEXES WHERE TABLE_NAME = 'T_TEST_FK';

INDEX_NAME INDEX_TYPE UNIQUENESS
------------------------------ --------------------------- ----------
PK_TEST_FK NORMAL UNIQUE

SQL> INSERT INTO test_1 VALUES('1','a');

1 row inserted

SQL> commit;

Commit complete

SQL> INSERT INTO t_test_fk VALUES('1','b');

1 row inserted

SQL> commit;

Commit complete

SQL> drop table t_test_fk;

Table dropped

SQL>
SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME
2 FROM Dba_Constraints
3 WHERE table_name = 'T_TEST_FK'
4 ;

CONSTRAINT_NAME CONSTRAINT_TYPE R_CONSTRAINT_NAME
------------------------------ --------------- ------------------------------

SQL> SELECT INDEX_NAME,INDEX_TYPE,UNIQUENESS FROM DBA_INDEXES WHERE TABLE_NAME = 'T_TEST_FK';

INDEX_NAME INDEX_TYPE UNIQUENESS
------------------------------ --------------------------- ----------

SQL> flashback table t_test_fk to before drop;

Done

SQL>
SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME
2 FROM Dba_Constraints
3 WHERE table_name = 'T_TEST_FK'
4 ;

CONSTRAINT_NAME CONSTRAINT_TYPE R_CONSTRAINT_NAME
------------------------------ --------------- ------------------------------
BIN$aqp9hUBBQ7a4m6WmAg4plg==$0 P

SQL> SELECT INDEX_NAME,INDEX_TYPE,UNIQUENESS FROM DBA_INDEXES WHERE TABLE_NAME = 'T_TEST_FK';

INDEX_NAME INDEX_TYPE UNIQUENESS
------------------------------ --------------------------- ----------
BIN$F9Moi8+YQwC2MAlkjur3Fg==$0 NORMAL UNIQUE

可以看到主键约束和唯一索引被恢复了，外键却没有