Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.