After DBMS_FLASHBACK is enabled as of a particular timestamp
or SCN, the database appears as if the clock was turned back to the timestamp or SCN until
DBMS_FLASHBACK is disabled. Although DML is not allowed when DBMS_FLASHBACK is
enabled, a cursor can be opened in a PL/SQL procedure before DBMS_FLASHBACK is enabled
to allow data from a previous point in time to be inserted or updated in the database as of the
current point in time.
Table 7-2 lists the procedures available within DBMS_FLASHBACK.
Procedure Description
DISABLE Disables Flashback mode for the session
ENABLE_AT_SYSTEM_CHANGE_NUMBER Enables Flashback mode for the session,
specifying an SCN
ENABLE_AT_TIME Enables Flashback mode for the session,
using the SCN closest to the TIMESTAMP
specified
GET_SYSTEM_CHANGE_NUMBER Returns the current SCN
TRANSACTION_BACKOUT Backs out a transaction and all dependent
transactions using transaction names or
transaction identifiers (XIDs)
TABLE 7-2 DBMS_FLASHBACK Procedures
226 Oracle Database 11g DBA Handbook
The procedures that enable and disable Flashback mode are relatively simple to use. The
complexity usually lies within a PL/SQL procedure, for example, that creates cursors to support
DML commands.
In the following example, we??™ll revisit HR??™s deletion of the EMPLOYEES rows and how HR can
restore those to the table using the DBMS_FLASHBACK package. In this scenario, HR will put the
deleted employee rows back into the table and instead add a termination date column to the table
to reflect the date at which the employees left the company:
SQL> delete from hr.
Pages:
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387