OCP-047 flashback table emp to before drop

发布时间：2020-12-15 07:14:26 所属栏目：百科来源：网络整理

导读：98. View the Exhibit and examine the data in EMP and DEPT tables. In the DEPT table,DEPTNO is the PRIMARY KEY. In the EMP table,EMPNO is the PRIMARYKEYand DEPTNO is the FOREIGN KEY referencing the DEPTNO column in the DEPT table. What woul

98. View the Exhibit and examine the data in EMP and DEPT tables.

In the DEPT table,DEPTNO is the PRIMARY KEY.
In the EMP table,EMPNO is the PRIMARYKEYand DEPTNO is the FOREIGN KEY referencing the
DEPTNO column in the DEPT table.
What would be the outcome of the following statements executed in the given sequence?
DROPTABLE emp;
FLASHBACK TABLE emp TO BEFORE DROP;
INSERT INTO emp VALUES (2,COTT 10); ? ?

?INSERT INTO emp VALUES (3,ING 55);

?? ?（题目中的insert into ?的语句明显不对，INSERT INTO emp VALUES (2,COTT 10); 3个列的值需要，号分隔

所以题目中的INSERT INTO emp VALUES (2,COTT 10);应该为INSERT INTO emp VALUES (2,‘COTT’,10);

? ? ? ? ? ? ? ? ? ? ? ?INSERT INTO emp VALUES (3,ING 55); ? 应该为：??INSERT INTO emp VALUES (3,'ING',55);

否则，应该是2个语句都因为 values的值不对使得insert 语句失败。）

A. Both the INSERT statements would fail because all constraints are automatically retrievedwhen the
table is flashed back.
B. Both the INSERT statements would succeed because none of the constraints on the table are
automatically retrievedwhen the table is flashed back.
C. Only the first INSERT statement would succeed because all the constraints except the primary key
constraint are automatically retrieved after a table is flashed back.
D. Only the second INSERT statement would succeed because all the constraints except referential
integrity constraints that reference other tables are retrieved automatically after the table is flashed back.
Answer: D

本题的考点应该是flashback drop table 后约束是否能被恢复。外键不会在表flashing back drop table 后恢复，而主键约束被恢复了。

第一个insert语句会提示违法唯一约束而失败，第二个语句则会成功执行。

Notes on Flashing Back Dropped Tables?The following notes apply to flashing back dropped tables:

Oracle Database retrieves all indexes defined on the table retrieved from the recycle bin except for bitmap join indexes. (Bitmap join indexes are not put in the recycle bin during a?DROP?TABLE?operation,so cannot be retrieved.)
The database also retrieves all triggers and constraints defined on the table except for referential integrity constraints that reference other tables.

The retrieved indexes,triggers,and constraints have recycle bin names. Therefore it is advisable to query the?USER_RECYCLEBIN?view before issuing aFLASHBACK?TABLE?...?TO?BEFORE?DROP?statement so that you can rename the retrieved triggers and constraints to more usable names.
When you drop a table,all materialized view logs defined on the table are also dropped but are not placed in the recycle bin. Therefore,the materialized view logs cannot be flashed back along with the table.
You cannot flash back a table if it has been purged,either by a user or by Oracle Database as a result of some space reclamation operation.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!