Understanding Flashback Technology - OCP: Oracle Database 12c Administrator Certified Professional - page 371

3 'DD-MON-YYYY HH24:MI:SS')) e, departments as of timestamp(
4 to_timestamp('23-AUG-2013 11:53:00','DD-MON-YYYY HH24:MI:SS')) d, jobs j
5 where e.department_id = d.department_id
6 and e.job_id = j.job_id
7* and e.employee_id = 200;
LAST_NAME DEPARTMENT_NAME JOB_TITLE
---------- -------------------- ------------------------
Whalen Administration Administration Assistant
This query joined a total of three tables. Two of these tables were flashed back to different
points in time. The third table was not flashed back at all. This demonstrates the flexibility of
Flashback Query. In fact, a single table could even be joined multiple times, each join flashed
back to a different point in time. As has been discussed before, the only limitation is how far
back in time you can flash back, and that is determined by the undo retention period.
It is important to understand that the data present in the undo segment governs the
ability to flash back to a point in time. Undo retention is only a guideline.
In this section, you've seen a sample of what Flashback Query can do and what a
powerful tool it can be in a DBA's arsenal. It allows you to look into the past to view
data as it existed at a specific point in time. But what if you're not sure exactly when
a change was made? Or suppose you wanted to see all the changes that were made to a
column over a period of time. Those are different types of problems and require a differ-
ent type of tool—Flashback Version Query.
In Exercise 8.2, you'll practice using the Flashback Query feature.
exeRCiSe 8.2
Using Flashback Query
To practice using the Flashback Query feature, perform the following:
1. Create a table, insert rows of data, and commit.
SQL> create table foo (x number, y varchar2(10));
Table created.
SQL> insert into foo values (1,'test1');
1 row created.