DBMS_FLASHBACK, 2 of 2

Summary of DBMS_FLASHBACK Subprograms

Table 17-2 DBMS_FLASHBACK Subprograms

Subprogram	Description
ENABLE_AT_TIME Procedure	Enables Flashback for the entire session. The snapshot time is set to the SCN that most closely matches the time specified in `query_time.`
ENABLE_AT_SYSTEM_CHANGE_NUMBER Procedure	Takes an SCN as an Oracle number and sets the session snapshot to the specified number. Inside the Flashback mode, all queries will return data consistent as of the specified wall-clock time or SCN.
GET_SYSTEM_CHANGE_NUMBER Function	Returns the current SCN as an Oracle number. You can use the SCN to store specific snapshots.
DISABLE Procedure	Disables the Flashback mode for the entire session.

ENABLE_AT_TIME Procedure

This procedure enables Flashback for the entire session. The snapshot time is set to the SCN that most closely matches the time specified in query_time.

Syntax

DBMS_FLASHBACK.ENABLE_AT_TIME (
   query_time   IN TIMESTAMP);

Parameters

Table 17-3 ENABLE_AT_TIME Procedure Parameters

Parameter Description

Parameter	Description
`query_time`	This is an input parameter of type `TIMESTAMP.` A time stamp can be specified in the following ways: Using the TIMESTAMP constructor: Example: `execute dbms_flashback.enable_at_time(TIMESTAMP '2001-01-09 12:31:00').` Use the Globalization Support (NLS) format and supply a string. The format depends on the Globalization Support settings. Using the `TO_TIMESTAMP` function: Example: `execute dbms_flashback.enable_at_time(TO_TIMESTAMP('12-02-2001 14:35:00', 'DD-MM-YYYY HH24:MI:SS')). Y`ou provide the format you want to use. This example shows the `TO_TIMESTAMP` function for February 12, 2001, 2:35 PM. If the time is omitted from query time, it defaults to the beginning of the day, that is, 12:00 A.M. Note that if the query time contains a time zone, the time zone information is truncated.

query_time

This is an input parameter of type TIMESTAMP. A time stamp can be specified in the following ways:

Using the TIMESTAMP constructor: Example: execute dbms_flashback.enable_at_time(TIMESTAMP '2001-01-09 12:31:00'). Use the Globalization Support (NLS) format and supply a string. The format depends on the Globalization Support settings.

Using the TO_TIMESTAMP function: Example: execute dbms_flashback.enable_at_time(TO_TIMESTAMP('12-02-2001 14:35:00', 'DD-MM-YYYY HH24:MI:SS')). You provide the format you want to use. This example shows the TO_TIMESTAMP function for February 12, 2001, 2:35 PM.

If the time is omitted from query time, it defaults to the beginning of the day, that is, 12:00 A.M.

Note that if the query time contains a time zone, the time zone information is truncated.

ENABLE_AT_SYSTEM_CHANGE_NUMBER Procedure

This procedure takes an SCN as an input parameter and sets the session snapshot to the specified number.

In the Flashback mode, all queries return data consistent as of the specified wall-clock time or SCN.

Syntax

DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER (
   query_scn IN NUMBER);

Parameters

Table 17-4 ENABLE_AT_SYSTEM_CHANGE_NUMBER Procedure Parameters

Parameter	Description
`query_scn`	The system change number (SCN), a version number for the database that is incremented on every transaction commit.

GET_SYSTEM_CHANGE_NUMBER Function

This function returns the current SCN as an Oracle number datatype. You can obtain the current change number and stash it away for later use. This helps you store specific snapshots.

Syntax

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER (
RETURN NUMBER);

DISABLE Procedure

This procedure disables the Flashback mode for the entire session.

Syntax

DBMS_FLASHBACK.DISABLE;

Example

The following example queries the salary of an employee, Joe, on August 30, 2000:

EXECUTE dbms_flashback.enable_at_time('30-AUG-2000');
SELECT salary from emp where name = 'Joe'
EXECUTE dbms_flashback.disable;