Which of the following are the correct statements of using the Flashback Query feature in Oracle?
Each correct answer represents a complete solution. Choose all that apply.
A.
CREATE TABLE AS SELECT AS OF
B.
SELECT AS OF
C.
CREATE TABLE AS SELECT
D.
INSERT INTO (SELECT )
Explanation:
You perform a Flashback Query by using a SELECT statement with an AS OF clause. You use a
Flashback Query to retrieve data as it existed at
some time in the past. The query explicitly references a past time by means of a timestamp or SCN.
It returns committed data that was current
at that point in time. To use the results of a Flashback Query in a DDL or DML statement that affects
the current state of the database, use an
AS OF clause inside the INSERT or CREATE TABLE AS SELECT statement.
Answer C is incorrect. The CREATE TABLE AS SELECT command is used to create a table with data
and structure copied from another
table. Since there is no use of the AS OF clause in this statement, this command is not a Flashback
Query command; instead it’s the normal
CREATE TABLE command.
Answer D is incorrect. INSERT INTO (SELECT ) is not a Flashback Query because the AS OF clause is
not used in this command. This
command will select data from a table and insert into another table on which the INSERT INTO
clause is based. Flashback Query does support
insert commands, but the correct usage is INSERT INTO (SELECT AS OF).