Version User Scope of changes
Sep 9 2008, 8:11 PM EDT (current) juancarlosreyesp 1132 words added
Sep 9 2008, 8:08 PM EDT juancarlosreyesp

Changes

Key:  Additions   Deletions
1 Query Flashback What it’s for?
· Recovering deleted or incorrectly modified data.
· Compare Current Data with Old Data.
· See historic data.
· Avoid the use of temporal data.

1.1 How it works

To flashback in time Oracle only read the needed undo segments and display the information, this isn’t a new technology is the same technology to get a consistent query, to the point the query started.

1.2 Syntax

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER RETURN NUMBER;
Get the SCN number
EXEC DBMS_FLASHBACK.ENABLE_AT_TIME ( TIMESTAMP '2002-01-01 00:00:00');
Enable the session in flashback mode at approximately that time
EXEC DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(3254658);
Enable the session in flashback mode exactly at that SCN
EXEC DBMS_FLASHBACK.DISABLE;
Disable the flashback mode

SELECT FROM AS OF TIMESTAMP '2002-01-01 00:00:00'
Select the table in flashback mode at approximately that time
SELECT FROM A AS OF SCN 3254658;
Select the table in flashback mode exactly at that SCN

1.3 Database Configuration

Is advisable to set automatic undo management

1.4 Performance

1.4.1 Most performant with queries that normally do a small amount of logical IO

Select small sets of data using indexes, rather than queries that require full table scans. If you must do a full table scan, consider adding a parallel hint to the query.

1.4.2 Keep statistics current on tables involved in flashback queries

Because it uses the cost-based optimizer.

1.5 Privileges

FLASHBACK ANY TABLE to issue a flashback on any table, view or materialized view in any schema. Or FLASHBACK object privilege to specific objects. ( Not needed for DBMS_FLASBACK package).
Privilege to execute DBMS_FLASBACK package, if you use that package.

1.6 Examples and techniques

1.6.1 Inserting Data


We start creating the table, inserting the values and getting the proper SCN, after every operation.
14:34:07 SQL> CREATE TABLE A ( B VARCHAR2(1))
14:34:23 2 ;
Table created.

14:34:25 SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
3254181

14:57:58 SQL> INSERT INTO A VALUES( '1');
GET_SYSTEM_CHANGE_NUMBER
------------------------
3254652

14:58:22 SQL> INSERT INTO A VALUES( '2');
GET_SYSTEM_CHANGE_NUMBER
------------------------
3254653

14:58:26 SQL> INSERT INTO A VALUES( '3');
GET_SYSTEM_CHANGE_NUMBER
------------------------
3254654

14:58:30 SQL> commit;
Commit complete.

15:08:32 SQL> DELETE FROM A;
3 rows deleted.

15:08:36 SQL> COMMIT;
Commit complete.

1.6.2 Using SELECT AS OF

After we delete the data, we still can see the data
If you would like to use the time stamp you could use

SELECT * FROM A AS OF TIMESTAMP TO_TIMESTAMP('2003-01-01 01:00','YYYY-MM-DD HH:MI')

15:08:39 SQL> SELECT * FROM A AS OF SCN 3254658;
B
-
1
2
3

15:08:41 SQL> SELECT * FROM A;
no rows selected

1.6.3 Using DBMS_FLASBACK package


If you would like to use the time stamp you could use
EXEC DBMS_FLASHBACK.ENABLE_AT_TIME(TO_TIMESTAMP('2003-01-01 01:00','YYYY-MM-DD HH:MI'));

But we use the SCN

15:14:27 SQL> EXEC DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(3254658);
PL/SQL procedure successfully completed.

15:15:46 SQL> SELECT * FROM A;
B
-
1
2
3

15:15:52 SQL> EXEC DBMS_FLASHBACK.DISABLE;
PL/SQL procedure successfully completed.

15:15:55 SQL> SELECT * FROM A;
no rows selected

1.6.4 Use SCN to be more precise

You don’t need to wait 5 minutes.

scott@ORA920> column scn new_val scn
scott@ORA920> select dbms_flashback.get_system_change_number scn from dual;

SCN
----------
25585211

scott@ORA920>
scott@ORA920> update emp set ename = lower(ename);
14 rows updated.

scott@ORA920>
scott@ORA920> select a.ename, b.ename
2 from emp a, emp as of scn &scn b
3 where a.empno = b.empno
4 /
old 2: from emp a, emp as of scn &scn b
new 2: from emp a, emp as of scn 25585211 b

ENAME ENAME
---------- ----------
smith SMITH
allen ALLEN
ward WARD
jones JONES
martin MARTIN
blake BLAKE
clark CLARK
scott SCOTT
king KING
turner TURNER
adams ADAMS
james JAMES
ford FORD
miller MILLER
14 rows selected.

1.6.5 Function to save SCN data

To get precision in your flashback queries is better to save the SCN number periodically.
The system change number (SCN), a version number for the database that is incremented on every commit.
You can use the function In Oracle 9i
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER
In 8i you can use
USERENV('COMMITSCN')

create or replace procedure save_scn
as
pragma autonomous_transaction/* commit independently*/;
l_scn number;
begin
insert into scn
values (
SYSDATE,
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER
);
commit;
end;
/

1.6.6 Create a view or a function to get old data

CREATE VIEW vew_example AS
SELECT * FROM example AS OF
TIMESTAMP (SYSTIMESTAMP - INTERVAL '120' MINUTE);
When using this technique, remember that daylight savings time and leap years can cause anomalous results. For example, SYSDATE - 1might refer to 23 or 25 hours ago, shortly after a change in daylight savings time.

1.6.7 Exporting using flashback option

You can export using flashback query:
Using the parameter flashback_scn, flashback_time.
For example
exp system/manager full=y file=flashbacktest.dmp flashback_scn=10002

1.6.8 How to get the date time for a specific SCN or vice versa

It only tracks the last five days and only in five minute increments.

You can query the table SMON_SCN_TIME
select * from SYS.SMON_SCN_TIME
1 row(s) selected
THREAD TIME_MP TIME_DP SCN_WRP SCN_BAS
---------- ---------- -------------------- ---------- ----------
1 1041971155 7-Ene-2003 16:25:57 0 3385087
1 1041971462 7-Ene-2003 16:31:04 0 3385188
1 1041971770 7-Ene-2003 16:36:12 0 3385289

1.7 Restrictions and Errors

1.7.1 CTAS your data as fast as you can

The period of UNDO retention is not guaranteed (unless you do so in 10gR2 and upward) This means if some operation requires the space, it will overwrite your previous data, the advice is to do a CREATE TABLE AS SELECT AS OF, as sonn as you can, to guarantee not to lose that information.

1.7.2 Flashback is session-specific

You enable it only for your session.

1.7.3 Automatic Undo Management

The flashback query mechanism is most effective when you use automatic undo management.

1.7.4 Use SCN for precision when gathering data

Use SCN instead of time to be more precise.

1.7.5 Undo Data Invalidation

DDL statements like drop, modify, move, truncate invalidates undo data.
Not including storage attributes of a table as INITTRANS, MAXTRANS.
This situation creates the ORA-01466 unable to read data - table definition has changed
Only the data if affected by a flashback query. Other information is got from data dictionary, including current character set.

1.7.6 Materialized Views

Flashback queries against materialized views do not take advantage of query rewrite.

1.7.7 SYS User

Cannot make calls to the DBMS_FLASHBACK package.
But can use the AS OF clause and perform flashback queries, this not includes V$VIEWS data

1.7.8 Database links

Cannot perform flashback queries on remote tables through database links

1.7.9 Cannot nest flashback calls

You must disable it before enabling it to a different time.

1.8 Oracle dictionary

1.9 Other Tools

1.10 Features by Release

1.10.1 9.0.1

Introduced.

1.10.2 9.2.0

You can flashback without using the DBMS_FLASHBACK package.
SELECT AS OF SCN, ….
CREATE TABLE AS SELECT AS OF SCN….
SELECT AS OF TIMESTAMP…..
CREATE TABLE AS SELECT AS OF TIMESTAMP…..

1.10.3 10.1

· Perform remote queries of past data.

1.10.4 10.2

Automatic Undo Retention Enhancement

1.11 Bugs by Release