Flashback-scn-timestamp

I. Time-based (as of timestamp) flashback
1. Create a table
CREATE TABLE Flash_tab (ID,VL) as
Select Rownum,oname from (select substr (object_name,1,1) oname from all_objects
Group by substr (object_name,1,1) Order by 1)
where rownum<=20;

2. Query the contents of the table
Sql> select * from Flash_tab;
ID VL
---------- --
1/
2 A
3 B
4 C
5 D
6 E
7 F
8 G
9 H
Ten I
J
K
L
+ M
N
+ O
P
Q
R
S
Rows selected

3, delete the table data.
sql> Delete Flash_tab where id<10;
9 rows deleted

Sql> select * from Flash_tab;
ID VL
---------- --
Ten I
J
K
L
+ M
N
+ O
P
Q
R
S
Rows selected

4. Execute Flashbach Query
Sql> SELECT * from Flash_tab as of timestamp sysdate-2/1440;
ID VL
---------- --
1/
2 A
3 B
4 C
5 D
6 E
7 F
8 G
9 H
Ten I
J
K
L
+ M
N
+ O
16 1
Q
R
S
Rows selected
===============
Description
As of timestamp represents time-based
sysdate-2/1440 indicates that the current system time is two minutes ago; 1440 means 60 minutes * 24 hours

5. Restore deleted records
INSERT INTO Flash_tab
SELECT * from Flash_tab as of timestamp sysdate-2/1440
where id<10;

Commit

Sql> select * from Flash_tab;
ID VL
---------- --
Ten I
J
K
L
+ M
N
+ O
16 1
Q
R
S
1/
2 A
3 B
4 C
5 D
6 E
7 F
8 G
9 H
Rows selected

Ii. Flashback based on SCN (as of SCN)
The SCN value query can use the Dbms_flashback.get_system_change_number function, or query the CURRENT_SCN value from the V$database view.

1, authorized Scott users have the ability to query the SCN value
Sql> Grant execute on Dbms_flashback to Scott;

Authorization is successful.
Sql> Grant SELECT on V_$database to Scott;

Authorization is successful.

2. Query the current SCN value
Sql> select Dbms_flashback.get_system_change_number from dual;
Get_system_change_number
------------------------
1564606

Sql> select Current_scn from V$database; --inconsistent SCN values for different point-in-time queries
Current_scn
-----------
1564631

3. Querying for records in objects when specifying SCN values
Sql> SELECT * from Flash_tab as of SCN 1564606;
ID VL
---------- --
Ten I
J
K
L
+ M
N
+ O
P
Q
R
S
1/
2 A
3 B
4 C
5 D
6 E
7 F
8 G
9 H
Rows selected

4. Delete Object modification
Sql> Delete from Flash_tab;
Rows deleted

Sql> commit;
Commit Complete

5. Performing flashback as of SCN recovery data
Sql> select * from Flash_tab;
ID VL
---------- --

Sql> SELECT * from Flash_tab as of SCN 1564606;
ID VL
---------- --
Ten I
J
K
L
+ M
N
+ O
P
Q
R
S
1/
1 b
3 B
4 C
5 D
6 E
7 F
8 G
9 H
Rows selected

sql> INSERT INTO Flash_tab select * from Flash_tab as of SCN 1564606;
Rows inserted

Sql> select * from Flash_tab;
ID VL
---------- --
Ten I
J
K
L
+ M
N
+ O
P
Q
R
S
1/
2 A
3 B
4 C
5 D
6 E
7 F
8 G
9 H
Rows selected
============================
Description
SCN is more accurate than timestamp. In fact timestamp is also converted into SCN. Because Oracle uses the SCN to mark operations instead of time.
Each time point corresponds to an SCN value. In 10g, the system generates a system time and SCN match on average every 3 seconds to be stored in the Sys.smo_scn_time table. SCN and timestamp can also be converted to each other.

Sql> DESC Smon_scn_time;
Name Type Nullable Default Comments
------------ --------- -------- ------------------------------- --------
THREAD number Y
TIME_MP number Y
TIME_DP DATE Y
SCN_WRP number Y
Scn_bas number Y
Num_mappings number Y
Tim_scn_map RAW (x) Y
SCN number Y 0
Orig_thread number Y 0/* for downgrade */

Sql> Select TIMESTAMP_TO_SCN (sysdate) from dual;
TIMESTAMP_TO_SCN (Sysdate)
-------------------------
1569124

Sql> Select To_char (Scn_to_timestamp (1569124), ' Yyyy-mm-dd HH24:MI:SS ') from dual; --time is accurate to milliseconds
To_char (Scn_to_timestamp (15691
------------------------------
2015-01-05 22:55:00

Flashback-scn-timestamp