Flashback query, flashback

Flashback query, flashback

The FLASHBACK Technology includes multiple aspects: flashback database, flashback drop, flashback query, and flashback table.

Among them, flashbackdatabase uses the flash back log, the recycle bin used by flashback drop, and flashback query and flashbacktable use the undo information. Flashbackquery helps us determine how table data is stored at a certain time point or time period in the past, the flashback table helps us to flash the table back to a certain time point in the past.

 

Flashback queryYesOracle 9iTo help us query the data status of a table at a certain time point in the past. Syntax format:

Select * from table_name as of scnscn;

Select * from table_name as oftimestamp timestamp;

 

Because flashbackquery can only query information at a certain time point in the past,You cannot query a certain period of time. Therefore10gMedium,OracleIntroducedFlashbackversion queryThe syntax format is as follows:

Select versions_xid, versions_startscn, versions_endscn, versions_starttime, versions_endtime ,.... From table_name versions between scn startscn and endscn;

Select versions_xid, versions_startscn, versions_endscn, versions_starttime, versions_endtime ,.... From table_name versions between scn minvalue and maxvalue;

 

Select versions_xid, versions_startscn, versions_endscn, versions_starttime, versions_endtime ,.... From table_name versions between timstamp starttime and endtime;

Note that,Flashback version queryCannot SpanDdlRun

 

In addition to the flashbackversion query, oracle also provides us with the flashback transaction query. Through the flashback transaction query, We can query a transaction statement to learn the table data affected by the transaction. The syntax format is as follows:

 

Select * from flashback_transaction_query where xid = ??;

 

Flashback table can flash a table back to a previous state. The syntax format is as follows:

Flashbacktable table_name to scn;

Flashbacktable table_name to timestamp;

 

Note thatFlashback tableIt cannot be crossed.DdlBut if we only add columns to the tableDdlOperation, so we can flash backDdlIn a previous status, the values of newly added columns are all null.

 

Flashback tableA temporary table is used to delete and insert tables to be flashed back (delete all data and re-insert all data). Therefore, the row location may change.