You can review the tables that
use Flashback Data Archive by querying the data dictionary view DBA_FLASHBACK_ARCHIVE_
TABLES:
SQL> select * from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_AR ARCHIVE_TABLE_NAME
---------------------- ---------- ------------ --------------------
EMPLOYEES HR FB_ES SYS_FBA_HIST_70313
Querying Flashback Data Archives
Querying the historical data for a table in a Flashback Data Archive is as easy as using the as of
clause in a table when you are using DML activity stored in an undo tablespace. In fact, users will
not know whether they are retrieving historical data from the undo tablespace or from a Flashback
Data Archive.
In this scenario, much like in the scenarios earlier in this chapter, one of the employees in the
HR department deletes an employee row in the EMPLOYEES table and forgets to archive it to the
EMPLOYEE_HISTORY table first; with Flashback Data Archives enabled for the EMPLOYEES table,
Chapter 7: Managing Transactions with Undo Tablespaces 239
the HR employee can rely on the FB_ES archive to satisfy any queries on employees no longer in
the EMPLOYEE table. This is the delete statement from three weeks ago:
SQL> delete from employees where employee_id = 169;
1 row deleted.
SQL>
The HR employee needs to find the hire date for employee 169, so she retrieves the historical
information from the EMPLOYEES table with the as of clause specifying a time four weeks ago:
SQL> select employee_id, last_name, hire_date
2 from employees
3 as of timestamp (systimestamp - interval '28' day)
4 where employee_id = 169;
EMPLOYEE_ID LAST_NAME HIRE_DATE
----------- ------------------------- ---------
169 Bloom 23-MAR-98
SQL>
Whether Oracle is using an undo tablespace or a Flashback Data Archive for a query containing
as of is completely transparent to the user.
Pages:
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402