[Oracle Database] flashback (FlashBack)
The flashback version queries SQL > set line 200SQL > col starttime for a30SQL > col endtime for a30SQL > col operation for a30SQL > select to_char (versions_starttime,'yyyy-mm-dd hh34:mi:ss') starttime,to_char (versions_endtime,'yyyy-mm-dd hh34:mi:ss') endtime, versions_xid xid,versions_operation operation from soe.customers versions between timestamp to_date ('2018-09-22 14 purge 00mm hh34:mi:ss') and sysdate where versions_xid is not null STARTTIME ENDTIME XID OPERATION -2018-09-22 14:00:36 05001100B8030000 U2018-09-22 13:59:59 05000700B8030000 U2018-09-22 14:00:25 08001B00CC030000 U2018-09- 22 14:00:14 05001000B8030000 I2018-09-22 14:00:20 09001700C9030000 I2018-09-22 14:00:05 0B00200041000000 I2018-09-22 14:00:05 09000700CA030000 I 2018-09-22 14:00:11 03001800B4030000 I 8 rows selected. Flashback transaction query SQL > select undo_sql from flashback_transaction_query where xid = hextoraw ('03001800B4030000') UNDO_SQL- -delete from "SOE". "LOGON" where ROWID = 'AAAVU+AAGAAAavRAB6' Delete from "SOE". "CARD_DETAILS" where ROWID = 'AAAVU4AAHAAAQEoABz';delete from "SOE". "ADDRESSES" where ROWID =' AAAVU3AAHAAAbuoAAy';delete from "SOE". "CUSTOMERS" where ROWID = 'AAAVU2AAGAAAaqxAAO';SQL > select undo_sql from flashback_transaction_query where xid = hextoraw (' 08001B00CC030000') UNDO_SQL- -update "SOE". "CUSTOMERS" set "PREFERRED_ADDRESS" = '1162533' where ROWID = 'AAAVU2AAGAAACC9AAS' Delete from "SOE". "ADDRESSES" where ROWID = 'AAAVU3AAHAAAbumAA5'
Flashback table-- scnSQL > alter table soe.customers enable row movement;Table altered.SQL > select count (*) from soe.customers; COUNT (*)-975317SQL > select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER---- 1217679 SQL > delete from soe.customers where nls_territory = 'Korea';12470 rows deleted.SQL > commit Commit complete.SQL > select count (*) from soe.customers; COUNT (*)-962847SQL > flashback table soe.customers to scn 1217679 position Flashback complete.SQL > select count (*) from soe.customers; COUNT (*)-975317 flashback table-timestamp SQL > select count (*) from soe.customers; COUNT (*)-975317SQL > select dbms_flashback.get_system_change_number from dual GET_SYSTEM_CHANGE_NUMBER---- 1221943SQL > select scn_to_timestamp (1221943) scn from dual SCN----23-SEP-18 04.17.24.000000000 PMSQL > select count (*) from soe.customers; COUNT (*)-975317SQL > select dbms_flashback.get_system_change_number from dual GET_SYSTEM_CHANGE_NUMBER---- 1222481SQL > select scn_to_timestamp (1222481) scn from dual SCN----23-SEP-18 04.22.21.000000000 PMSQL > select count (*) from soe.customers COUNT (*)-975340SQL > flashback table soe.customers to timestamp to_timestamp ('2018-09-23 16purl 17purl 240mm hh34:mi:ss'); Flashback complete.SQL > select count (*) from soe.customers; COUNT (*)-975317
Flashback delete SQL > show parameter recyclebin NAME TYPE VALUE -recyclebin string ONSQL > drop table warehouses Table dropped.SQL > show recyclebin;ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME- WAREHOUSES BIN$doc0/FwhEVPgU8kBqMBrow==$0 TABLE 2018-09-23:17:07:50SQL > flashback table warehouses to before drop Flashback complete.SQL > select count (*) from warehouses; COUNT (*)-1000 SQL > alter index "BIN$doc0/FwgEVPgU8kBqMBrow==$0" rename to warehouses_pk;Index altered.SQL > alter table warehouses rename constraint "BIN$doc0/FwfEVPgU8kBqMBrow==$0" to warehouses_pk;Table altered.
Flashback database SQL > startup mount;ORACLE instance started.Total System Global Area 1068937216 bytesFixed Size 2260088 bytesVariable Size 322962312 bytesDatabase Buffers 687865856 bytesRedo Buffers 55848960 bytesDatabase mounted.SQL > alter database flashback on;Database altered.SQL > alter database open;Database altered.SQL > select dbid,name,flashback_on,current_scn from v$database DBID NAME FLASHBACK_ CURRENT_SCN--1085678857 KING YES 1229459SQL > show parameter db_flashbackNAME TYPE VALUE Db_flashback_retention_target integer 1440SQL > alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss' Session altered.SQL > select count (*) from orders; COUNT (*)-1430069 SQL > select sysdate from dual;SYSDATE-2018-09-24 13:43:57SQL > truncate table orders;Table truncated.SQL > select count (*) from orders; COUNT (*)-0 SQL > shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL > startup mount ORACLE instance started.Total System Global Area 1068937216 bytesFixed Size 2260088 bytesVariable Size 322962312 bytesDatabase Buffers 687865856 bytesRedo Buffers 55848960 bytesDatabase mounted.SQL > flashback database to timestamp to_timestamp COUNT (*)-1430069 SQL > shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL > startup mountORACLE instance started.Total System Global Area 1068937216 bytesFixed Size 2260088 bytesVariable Size 322962312 bytesDatabase Buffers 687865856 bytesRedo Buffers 55848960 bytesDatabase mounted.SQL > alter database open resetlogs;Database altered.SQL > conn soe/soeConnected.SQL > select count (*) from orders; COUNT (*)-1430069
Summary: flashback version query, flashback transaction query, flashback table is related to UNDO, flashback deletion has nothing to do with recyclebin, flashback database has nothing to do with flashback log
