1)Flashback Query:(To retrieve the data from past existing Table not current data)(Don’t Drop the Table).
Flashback Query allows the contents of a table to be queried with reference to a specific point in time, using the AS OF clause.
[oracle@trichy ~]$ export ORACLE_SID=test
[oracle@trichy ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@trichy ~]$ export ORACLE_BASE=/u01/app/oracle
[oracle@trichy ~]$ export ORACLE_HOME=$ORACLE_BASE/product/12.2.0.1/db_1
[oracle@trichy ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Oct 10 00:00:43 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2046820352 bytes
Fixed Size 8622480 bytes
Variable Size 587206256 bytes
Database Buffers 1442840576 bytes
Redo Buffers 8151040 bytes
Database mounted.
Database opened.
SQL> create table emp(id int);
Table created.
SQL> SELECT current_scn,TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
6352947 2020-10-10 00:03:43
SQL> insert into emp values(111);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT current_scn,TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
6353013 2020-10-10 00:06:26
SQL> delete from emp;
4 rows deleted.
SQL> commit;
Commit complete.
SQL> SELECT COUNT(*) FROM emp AS OF TIMESTAMP TO_TIMESTAMP('2020-10-10 00:03:43','YYYY-MM-DD HH24:MI:SS');
COUNT(*)
----------
0
SQL> select * from emp;
no rows selected
SQL> SELECT COUNT(*) FROM emp AS OF TIMESTAMP TO_TIMESTAMP('2020-10-10 00:06:26','YYYY-MM-DD HH24:MI:SS');
COUNT(*)
----------
4
SQL> SELECT * FROM emp as of scn 6352947;
no rows selected
SQL> SELECT * FROM emp as of scn 6353013;
ID
----------
111
111
111
111
Screen shots
2)Flashback Version Query
Flashback version query allows the versions of a specific row to be tracked during a specific time period using the VERSIONS BETWEEN clause.
SQL> CREATE TABLE emp1 (id NUMBER(10),name VARCHAR2(50));
SQL> INSERT INTO emp1 (id, name) VALUES (1, 'dinesh');
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
6356132 2020-10-10 00:41:48
SQL> UPDATE emp1 SET name = 'raja' WHERE id = 1;
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> UPDATE emp1 SET name = 'senthil' WHERE id = 1;
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
6356817 2020-10-10 00:43:23
SQL> COLUMN versions_startscn FORMAT 99999999999999999
COLUMN versions_starttime FORMAT A24
COLUMN versions_endscn FORMAT 99999999999999999
COLUMN versions_endtime FORMAT A24
COLUMN versions_xid FORMAT A16
COLUMN versions_operation FORMAT A1
COLUMN description FORMAT A11
SET LINESIZE 200
SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
name
FROM emp1
VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2020-10-10 00:41:48', 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2020-10-10 00:43:23', 'YYYY-MM-DD HH24:MI:SS')
WHERE id = 1;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8
VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID V NAME
------------------ ------------------------ ------------------ ------------------------ ---------------- - --------------------------------------------------
6356809 10-OCT-20 12.43.07 AM 0600020004160000 U senthil
6356801 10-OCT-20 12.42.45 AM 6356809 10-OCT-20 12.43.07 AM 090004000D160000 U raja
6356130 10-OCT-20 12.41.43 AM 6356801 10-OCT-20 12.42.45 AM 0200070028160000 I dinesh
Screen shots
No comments:
Post a Comment