对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
可以看到主键约束和唯一索引被恢复了,外键却没有
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7691350/viewspace-1046501/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7691350/viewspace-1046501/