Flashbackquery: Examples of SCN and timestamp

Flashback Query
Flashback is a feature provided by Oracle since 9i. In 9i, we use Oracle to query data that has been operated in a table for a certain period of time, this feature is also called flashback query.
Flashback query: flashback query, flashback version query, and flashback transaction QueryThree.

Flashback Query
Flashback query uses the multi-version read consistency feature to read the record data before the operation from the Undo tablespace.
Flashback query is invalid for dynamic performance views such as V $ tables and x $ tables. However, it is effective for data dictionaries such as DBA _ *, all _ *, and user.
This feature also supports access to remote databases, such as select * from [email protected] as of SCN 3600.
Multi-version read consistency
When writing data, different transactions write the data's pre-image to the Undo tablespace. If other transactions query the table data at the same time, you can use the pre-image of the data in the Undo tablespace to construct the required complete record set without waiting for the written transaction to be committed or rolled back.
Flashback query can be used to construct a query record set in multiple ways. The record set range can be time-based or SCN-based. It can even be used to query the front image of records in different transactions in the Undo tablespace at the same time. The usage is very similar to the standard query. To use the flashback query to query undo data, the simplest method is to keep up with the as of Timestamp (based on time) after the name of the standard query statement) or as of SCN (based on SCN.
As of Timestamp | the SCN syntax is supported only after 9ir2.
================================ As of Timestamp example ======================== ==========

SQL> select sysdate from dual; SYSDATE-------------------2014-10-16 15: 13: 06sql> Delete from emp2; 14 rows have been deleted. SQL> select * From emp2 as of Timestamp to_timestamp ('2017-10-16 15:13:06 ', 'yyyy-MM-DD hh24: MI: ss '); empno ename job Mgr hiredate Sal comm deptno ---------- --------- ---------- today ---------- 7369 Smith clerk 7902 clerk 00:00:00 800 Allen salesman 207499 1981-02-20 00:00:00 7698 ward salesman 1600 00:00:00 300 30 ......

View the status before deletion: assume that the data has been deleted for about five minutes:

SQL> select * from EMP2 as of timestamp sysdate-5/1440;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 207499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 307521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30……

Use flashback to restore the previous data:

SQL> insert into emp2 select * From emp2 as of Timestamp to_timestamp ('2017-10-16 15:13:06 ', 'yyyy-MM-DD hh24: MI: ss'); 14 rows have been created. SQL> select * From emp2; empno ename job Mgr hiredate Sal comm deptno ---------- --------- ---------- today ---------- 7369 Smith clerk 7902 clerk 00:00:00 800 Allen salesman 207499 1981-02-20 00:00:00 7698 ward salesman 1600 00:00:00 300 30 ......

We recommend that you use the as of SCN method to execute the flashback query. For example, if you want to restore multiple tables with primary and foreign key constraints, data selection or insertion may fail due to inconsistent time points. The SCN method ensures the consistency of the constraints of records.

====================================== As of SCN example ================== ==============
1. View SCN:

SQL> show user; user is "sys" SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER------------------------1151413SQL> select current_scn from V $ database; CURRENT_SCN-----------1151425

2. Delete the database/view the deleted data

SQL> show user; the user is "Scott" SQL> Delete from emp2; 14 rows have been deleted. SQL> select * From emp2; SQL> select * From emp2 as of SCN 1151425; empno ename job Mgr hiredate Sal comm deptno ---------- --------- ---------- ------------ ---------- 7369 Smith clerk 7902 17-12 month-80 800 Allen salesman 207499 20-2 month-81 7698 1600 ward salesman 300 22-2 month-81 1250 500 30 ......

3. Use flashbach query to restore the previous data

SQL> insert into emp2 select * From emp2 as of SCN 1151413; 14 rows have been created. SQL> select * From emp2; empno ename job Mgr hiredate Sal comm deptno ---------- --------- ---------- ------------ ---------- 7369 Smith clerk 7902 17-12 month-80 800 Allen salesman 207499 20-2 month-81 7698 1600 ward salesman 300 22-2 month-81 1250 500 30 ......

========================================================== ====
4. Relationship between SCN and timestamp
Oracle uses SCN internally. Even if you specify as of Timestamp, Oracle converts it to SCN. There is a table between the system time mark and SCN, that is, smon_scn_time under sys.
Every five minutes, the system generates a matching system time mark with SCN and stores it in SYS. smon_scn_time table, which records the matching records of the last 1440 system time tags and SCN. Because this table only maintains the most recent 1440 records, therefore, if you use the as of Timestamp method, you can only use flashback data in the last five days (assuming that the system is continuously running without interruption or shutdown or restarting ).
Note that the system time mark matches with SCN every 5 minutes. For example, SCN: 339988,339989 matches 08-05-3013: 52: 00 and 2008-13:57:00, When you query the time in the period from 08-05-30 13:52:00 or 08-05-30 13:56:59 through the as of Timestamp, oracle will match it to SCN: 339988 to the Undo tablespace, that is, during this time, no matter what time point you specified, the data returned by the query will be from 13:52:00.
View the ing between SCN and timestamp:
SQL> select SCN, to_char (time_dp, 'yyyy-mm-dd hh24: MI: ss') from SYS. smon_scn_time;

From: http://blog.csdn.net/tianlesoftware/article/details/4677.pdf

Flashbackquery: Examples of SCN and timestamp