Flashback feature is available only for enterprise edition. In this article, Oracle 19c has been used for all examples. With flashback feature you can recover your table or data, perform queries that return past data.
Note that you cannot use flashback to previous point in time after a DDL or TRUNCATE is performed.
SQL> SET LINESIZE 100
SQL> COLUMN CURRENT_SCN FORMAT 999999999999999
SQL>
SQL> SELECT
2 CURRENT_SCN,
3 TO_CHAR(SYSTIMESTAMP, 'DD/MM/YYYY HH24:MI'),
4 FLASHBACK_ON,
5 SUPPLEMENTAL_LOG_DATA_ALL,
6 SUPPLEMENTAL_LOG_DATA_FK,
7 SUPPLEMENTAL_LOG_DATA_MIN,
8 SUPPLEMENTAL_LOG_DATA_PK,
9 SUPPLEMENTAL_LOG_DATA_PL,
10 SUPPLEMENTAL_LOG_DATA_SR,
11 SUPPLEMENTAL_LOG_DATA_UI
12 FROM V$DATABASE;
CURRENT_SCN TO_CHAR(SYSTIMES FLASHBACK_ON SUP SUP SUPPLEME SUP SUP SUP SUP
---------------- ---------------- ------------------ --- --- -------- --- --- --- ---
49602012448 10/01/2021 15:49 NO NO NO NO NO NO NO NO
SQL>
Let’s create a test table.
SQL> CREATE TABLE TEST_TABLE
2 AS
3 SELECT * FROM USER_OBJECTS;
Tablo yarat²ld².
SQL> SELECT COUNT(*) FROM TEST_TABLE;
COUNT(*)
----------
1135
You can get the SCN number from V$DATABASE table or DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER
SQL> COLUMN SCN_NUM FORMAT 999999999999999
SQL>
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER SCN_NUM FROM DUAL;
SCN_NUM
----------------
49602012591
Let’s do some DML and commit.
SQL> DELETE FROM TEST_TABLE WHERE OBJECT_TYPE = 'TABLE';
309 sat²rlar² silindi.
SQL>
SQL> COMMIT;
Kaydetme tamamland².
SQL>
SQL> SELECT COUNT(*) FROM TEST_TABLE;
COUNT(*)
----------
827
SQL>
SQL> SELECT COUNT(*) FROM TEST_TABLE AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '1' MINUTE;
COUNT(*)
----------
1136
As you can see from the above code. You can reach the deleted rows with AS OF clause. You can also use SCN which refers to System Change Number.
SQL> COLUMN SCN_COL FORMAT 999999999999999
SQL> SELECT TIMESTAMP_TO_SCN(SYSTIMESTAMP - INTERVAL '1' MINUTE) SCN_COL FROM DUAL;
SCN_COL
----------------
49602015771
SQL> SELECT COUNT(*) FROM TEST_TABLE AS OF SCN 49602015771;
COUNT(*)
----------
1136
Let’s rollback the commited table. In order to do that you have to enable row movement of table.
SQL> ALTER TABLE TEST_TABLE ENABLE ROW MOVEMENT;
Tablo dei■tirildi.
SQL>
SQL> FLASHBACK TABLE TEST_TABLE TO SCN 49602015771;
Gerisinyal tamamland².
SQL>
SQL> SELECT COUNT(*) FROM TEST_TABLE;
COUNT(*)
----------
1136
There might be multiple dml on the same row. If you want to see the versions of data. You can use the VERSIONS clause like below.
SQL> SELECT OBJECT_TYPE FROM TEST_TABLE WHERE OBJECT_ID = 99215;
OBJECT_TYPE
-----------------------
TABLE
SQL>
SQL> UPDATE TEST_TABLE SET OBJECT_TYPE = 'TABLE 1' WHERE OBJECT_ID = 99215;
1 sat²r g³ncellendi.
SQL>
SQL> COMMIT;
Kaydetme tamamland².
SQL>
SQL> UPDATE TEST_TABLE SET OBJECT_TYPE = 'TABLE 2' WHERE OBJECT_ID = 99215;
1 sat²r g³ncellendi.
SQL>
SQL> COMMIT;
Kaydetme tamamland².
SQL>
SQL> UPDATE TEST_TABLE SET OBJECT_TYPE = 'TABLE 3' WHERE OBJECT_ID = 99215;
1 sat²r g³ncellendi.
SQL>
SQL> COMMIT;
Kaydetme tamamland².
SQL>
SQL> UPDATE TEST_TABLE SET OBJECT_TYPE = 'TABLE 4' WHERE OBJECT_ID = 99215;
1 sat²r g³ncellendi.
SQL>
SQL> COMMIT;
Kaydetme tamamland².
SQL>
SQL> UPDATE TEST_TABLE SET OBJECT_TYPE = 'UNKNOWN' WHERE OBJECT_ID = 99215;
1 sat²r g³ncellendi.
SQL>
SQL> COMMIT;
Kaydetme tamamland².
You can get more information with flashback pseudocolumns as shown below.
SELECT
VERSIONS_STARTSCN,
VERSIONS_STARTTIME,
VERSIONS_ENDSCN,
VERSIONS_ENDTIME,
VERSIONS_XID,
VERSIONS_OPERATION,
T.OBJECT_TYPE
FROM TEST_TABLE VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '5' MINUTE AND SYSTIMESTAMP T
WHERE OBJECT_ID = 99215;
Moreover, you can get undo sql with the following table.
SELECT * FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = HEXTORAW('0A001400158C0000');
However, UNDO_SQL column might be NULL due to SUPPLEMENTAL LOG is disable. In order to enable it you can use the following statements.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
Finally, you can recover a drop table with the following command.
SQL> DROP TABLE TEST_TABLE;
Tablo iptal edildi.
SQL>
SQL> FLASHBACK TABLE TEST_TABLE TO BEFORE DROP;
Gerisinyal tamamland².
SQL>
SQL> SELECT COUNT(*) FROM TEST_TABLE;
COUNT(*)
----------
1136
You can also rename the dropped table.
SQL> DROP TABLE TEST_TABLE;
Tablo iptal edildi.
SQL>
SQL> FLASHBACK TABLE TEST_TABLE TO BEFORE DROP RENAME TO TEST_TABLE2;
Gerisinyal tamamland².
SQL>
SQL> SELECT COUNT(*) FROM TEST_TABLE;
SELECT COUNT(*) FROM TEST_TABLE
*
1 sat²r²nda HATA:
ORA-00942: tablo veya g÷r³nt³ mevcut degil
SQL>
SQL> SELECT COUNT(*) FROM TEST_TABLE2;
COUNT(*)
----------
1136
I hope this will help you 🙂