| PL/SQL Packages and Types Reference 10g Release 1 (10.1) Part Number B10802-01 |
|
|
View PDF |
| PL/SQL Packages and Types Reference 10g Release 1 (10.1) Part Number B10802-01 |
|
|
View PDF |
Using DBMS_FLASHBACK, you can flash back to a version of the database at a specified wall-clock time or a specified system change number (SCN)
| See Also:
Oracle Database Application Developer's Guide - Fundamentals and Oracle Database SQL Reference for detailed information about |
This chapter contains the following topics:
When DBMS_FLASHBACK is enabled, the user session uses the Flashback version of the database, and applications can execute against the Flashback version of the database.
You may want to use DBMS_FLASHBACK for the following reasons:
To use this package, a database administrator must grant EXECUTE privileges for DBMS_FLASHBACK.
DBMS_FLASHBACK is automatically turned off when the session ends, either by disconnection or by starting another connection.
PL/SQL cursors opened in Flashback mode return rows as of the flashback time or SCN. Different concurrent sessions (connections) in the database can perform Flashback to different wall-clock times or SCNs. DML and DDL operations and distributed operations are not allowed while a session is running in Flashback mode. You can use PL/SQL cursors opened before disabling Flashback to perform DML.
Under Automatic Undo Management (AUM) mode, you can use retention control to control how far back in time to go for the version of the database you need. If you need to perform a Flashback over a 24-hour period, the DBA should set the undo_retention parameter to 24 hours. This way, the system retains enough undo information to regenerate the older versions of the data.
You can set the RETENTION GUARANTEE clause for the undo tablespace to ensure that unexpired undo is not discarded.UNDO_RETENTION is not in itself a complete guarantee because, if the system is under space pressure, unexpired undo may be overwritten with freshly generated undo. In such cases, RETENTION GUARANTEE prevents this. For more information, see the Oracle Database Administrator's Guide
In a Flashback-enabled session, SYSDATE will not be affected; it will continue to provide the current time.
DBMS_FLASHBACK can be used within logon triggers to enable Flashback without changing the application code.
The following example illustrates how Flashback can be used when the deletion of a senior employee triggers the deletion of all the personnel reporting to him. Using the Flashback feature, you can recover and re-insert the missing employees.
DROP TABLE employee; DROP TABLE keep_scn; REM keep_scn is a temporary table to store scns that we are interested in CREATE TABLE keep_scn (scn number); SET ECHO ON CREATE TABLE employee ( employee_no number(5) PRIMARY KEY, employee_name varchar2(20), employee_mgr number(5) CONSTRAINT mgr_fkey REFERENCES EMPLOYEE ON DELETE CASCADE, salary number, hiredate date ); REM Populate the company with employees INSERT INTO employee VALUES (1, 'John Doe', null, 1000000, '5-jul-81'); INSERT INTO employee VALUES (10, 'Joe Johnson', 1, 500000, '12-aug-84'); INSERT INTO employee VALUES (20, 'Susie Tiger', 10, 250000, '13-dec-90'); INSERT INTO employee VALUES (100, 'Scott Tiger', 20, 200000, '3-feb-86'); INSERT INTO employee VALUES (200, 'Charles Smith', 100, 150000, '22-mar-88'); INSERT INTO employee VALUES (210, 'Jane Johnson', 100, 100000, '11-apr-87'); INSERT INTO employee VALUES (220, 'Nancy Doe', 100, 100000, '18-sep-93'); INSERT INTO employee VALUES (300, 'Gary Smith', 210, 75000, '4-nov-96'); INSERT INTO employee VALUES (310, 'Bob Smith', 210, 65000, '3-may-95'); COMMIT; REM Show the entire org SELECT lpad(' ', 2*(level-1)) || employee_name Name FROM employee CONNECT BY PRIOR employee_no = employee_mgr START WITH employee_no = 1 ORDER BY LEVEL; REM Sleep for a short time (approximately 10 to 20 seconds) to avoid querying REM close to table creation EXECUTE DBMS_LOCK.SLEEP(10); REM Store this snapshot for later access through Flashback DECLARE I NUMBER; BEGIN I := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER; INSERT INTO keep_scn VALUES (I); COMMIT; END; / REM Scott decides to retire but the transaction is done incorrectly DELETE FROM EMPLOYEE WHERE employee_name = 'Scott Tiger'; COMMIT; REM notice that all of scott's employees are gone SELECT lpad(' ', 2*(level-1)) || employee_name Name FROM EMPLOYEE CONNECT BY PRIOR employee_no = employee_mgr START WITH employee_no = 1 ORDER BY LEVEL; REM Flashback to see Scott's organization DECLARE restore_scn number; BEGIN SELECT scn INTO restore_scn FROM keep_scn; DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER (restore_scn); END; / REM Show Scott's org. SELECT lpad(' ', 2*(level-1)) || employee_name Name FROM employee CONNECT BY PRIOR employee_no = employee_mgr START WITH employee_no = (SELECT employee_no FROM employee WHERE employee_name = 'Scott Tiger') ORDER BY LEVEL; REM Restore scott's organization. DECLARE scotts_emp NUMBER; scotts_mgr NUMBER; CURSOR c1 IS SELECT employee_no, employee_name, employee_mgr, salary, hiredate FROM employee CONNECT BY PRIOR employee_no = employee_mgr START WITH employee_no = (SELECT employee_no FROM employee WHERE employee_name = 'Scott Tiger'); c1_rec c1 % ROWTYPE; BEGIN SELECT employee_no, employee_mgr INTO scotts_emp, scotts_mgr FROM employee WHERE employee_name = 'Scott Tiger'; /* Open c1 in flashback mode */ OPEN c1; /* Disable Flashback */ DBMS_FLASHBACK.DISABLE; LOOP FETCH c1 INTO c1_rec; EXIT WHEN c1%NOTFOUND; /* Note that all the DML operations inside the loop are performed with Flashback disabled */ IF (c1_rec.employee_mgr = scotts_emp) then INSERT INTO employee VALUES (c1_rec.employee_no, c1_rec.employee_name, scotts_mgr, c1_rec.salary, c1_rec.hiredate); ELSE IF (c1_rec.employee_no != scotts_emp) THEN INSERT INTO employee VALUES (c1_rec.employee_no, c1_rec.employee_name, c1_rec.employee_mgr, c1_rec.salary, c1_rec.hiredate); END IF; END IF; END LOOP; END; / REM Show the restored organization. select lpad(' ', 2*(level-1)) || employee_name Name FROM employee CONNECT BY PRIOR employee_no = employee_mgr START WITH employee_no = 1 ORDER BY LEVEL;
This procedure disables the Flashback mode for the entire session.
DBMS_FLASHBACK.DISABLE;
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;
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. It enables Flashback for the entire session.
DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER ( query_scn IN NUMBER);
| Parameter | Description |
|---|---|
|
|
The system change number (SCN), a version number for the database that is incremented on every transaction commit. |
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.It enables Flashback for the entire session.
DBMS_FLASHBACK.ENABLE_AT_TIME ( query_time IN TIMESTAMP);
This function returns the current SCN as an Oracle number datatype. You can obtain the current change number and store it for later use. This helps you retain specific snapshots.
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER RETURN NUMBER;
This function takes the SCN as an Oracle number datatype and returns the corresponding TIMESTAMP.
DBMS_FLASHBACK.SCN_TO_TIMESTAMP query_scn IN NUMBER) RETURN TIMESTAMP;
| Parameter | Description |
|---|---|
|
|
The system change number (SCN), a version number for the database that is incremented on every transaction commit. |
This function takes a TIMESTAMP as input and returns the corresponding SCN as an Oracle number datatype.
DBMS_FLASHBACK.TIMESTAMP_TO_SCN query_time IN TIMESTAMP RETURN NUMBER);
|
 Copyright © 1998, 2003 Oracle Corporation All Rights Reserved. |
|
