本文主要是介绍Oracle Flashback示例集锦，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
Flashback Table
本例参考Rewind a Table Using Oracle Flashback Table
Flashback Table，Flashback Query和Flashback Drop的示例合集可参见这里
首先获得当前的时间，或当前的SCN，以便后续回退使用
-- 获取SCN法1
select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;-- 获取SCN法2
select current_scn from v$database;-- 获取当前时间
alter session set nls_date_format='dd-mm-yy hh24:mi:ss';
select sysdate from dual;
输出如下：
GET_SYSTEM_CHANGE_NUMBER
------------------------942616CURRENT_SCN
-----------9426162SYSDATE
-----------------
09-01-23 15:03:33
以SYS登入，并修改数据：
connect / as sysdba
alter session set container=orclpdb1;
alter table hr.regions enable row movement;
select * from hr.regions;REGION_ID REGION_NAME
---------- -------------------------1 Europe2 Americas3 Asia4 Middle East and Africaupdate hr.regions set region_name = 'ORACLE';
commit;
select * from hr.regions;REGION_ID REGION_NAME
---------- -------------------------1 ORACLE2 ORACLE3 ORACLE4 ORACLE
此时，可以采用以下几种方法恢复：
-- 恢复到5分钟前
flashback table hr.regions to timestamp sysdate - 5/24/60;-- 恢复到指定时间点
flashback table hr.regions to timestamp to_timestamp('09-01-23 15:03:33','dd-mm-yy hh24:mi:ss');-- 恢复到指定的SCN
flashback table hr.regions to scn 9426162；
也可以用类似的语法查询之前的数据：
select * from hr.regions as of timestamp to_timestamp('09-01-23 15:03:33','dd-mm-yy hh24:mi:ss');
select * from hr.regions as of 9426162;
select * from hr.regions as of timestamp sysdate - 1/24/60;
Flashback Drop
本例参考[https://docs.oracle.com/en/database/oracle/oracle-database/tutorial-rec-flashback/index.html?opt-release-19c](Recover a Dropped Table Using Oracle Flashback Drop)。
LiveSQL中的例子可参见这里。
以SYS用户登录并执行以下：
connect / as sysdba
alter session set container=orclpdb1;
create table hr.regions_hist as select * from hr.regions;
select * from hr.regions_hist;REGION_ID REGION_NAME
---------- -------------------------1 Europe2 Americas3 Asia4 Middle East and Africadrop table hr.regions_hist;
select * from hr.regions_hist;
select * from hr.regions_hist*
ERROR at line 1:
ORA-00942: table or view does not existflashback table hr.regions_hist to before drop;
select * from hr.regions_hist;REGION_ID REGION_NAME
---------- -------------------------1 Europe2 Americas3 Asia4 Middle East and Africatruncate table hr.regions_hist;Table truncated.flashback table hr.regions_hist to before drop;
flashback table hr.regions_hist to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BINdrop table hr.regions_hist purge;
Flashback Transaction
本例参考Backing Out Transactions with Flashback
LiveSQL中的示例可参考这里。
什么是Flashback Transaction Back-Out?
Flashback Transaction Back-Out is a logical recovery option to roll back a target transaction and its dependent transactions while the database remains online. A dependent transaction is related by either a write-after-write (WAW) relationship, in which a transaction modifies the same data that was changed by the target transaction, or a primary key constraint relationship, in which a transaction re-inserts the same primary key value that was deleted by the target transaction. Flashback Transaction utilizes undo and the redo generated for undo blocks, to create and execute a compensating transaction for reverting the affected data back to its original state.
Flashback Transaction要求启用归档。如果没有启用，请运行以下脚本：
echo "******************************************* "
echo "For demo purposes ONLY:"
echo " * Unlock HR account"
echo " * Enable ARCHIVELOG mode for database"
echo ""
echo "The script may appear to hang at the SQL prompt"
echo "when the database is shutting down and being"
echo "opened. Wait a few minutes and it should progress."
echo "******************************************* "
sqlplus "/ as sysdba" << EOFALTER USER HR IDENTIFIED BY hr ACCOUNT UNLOCK;
ALTER SYSTEM SET db_recovery_file_dest_size = 10737418240 SCOPE=BOTH;shutdown immediate
startup mount
alter database archivelog;
alter database open;
archive log list
exit
EOF本实验的初始化需要运行以下脚本：
REM "******************************************* "
REM "For demo purposes ONLY:"
REM " * Setup for Flashback Transaction"
REM "Execute script as SYSDBA" set echo on
set serveroutput on
set term on
set lines 200
set pause on/*== Set up the HR database account for this OBE ==*/ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;ALTER SESSION SET CONTAINER = orclpdb1;
GRANT EXECUTE ON dbms_flashback TO hr;
GRANT select any transaction TO hr;
pause Press [Enter] to continue.../*== Create test data for flashback transaction ==*/
connect hr@orclpdb1;/*== Test transaction 1 ==*/
INSERT INTO hr.regions VALUES (10,'Pole');
INSERT INTO hr.regions VALUES (20,'Moon');
INSERT INTO hr.regions VALUES (30,'Venus');
INSERT INTO hr.regions VALUES (40,'Mars');
INSERT INTO hr.regions VALUES (50,'Saturn');
COMMIT;
pause Press [Enter] to continue.../*== Test transaction 2 ==*/
/*== Region 10 and 20 has a WAW dependency on transaction 1 ==*/
UPDATE hr.regions SET region_name='Two Poles' WHERE region_id = 10;
UPDATE hr.regions SET region_name='Many Moons' WHERE region_id = 20;
COMMIT;
pause Press [Enter] to continue.../*== Test transaction 3 ==*/
/*== Region 10 has a WAW dependency on transaction 1 and 2 ==*/
/*== Region 40 and 50 has a WAW dependency on transaction 1 ==*/
UPDATE hr.regions SET region_name='No star' WHERE region_id = 10;
UPDATE hr.regions SET region_name='Red star' WHERE region_id = 40;
UPDATE hr.regions SET region_name='Big star' WHERE region_id = 50;
COMMIT;
pause Press [Enter] to continue.../*== Test transaction 4 ==*/
/*== Region 30 has a WAW dependency on transaction 1 ==*/
UPDATE hr.regions SET region_name='Still called Venus' WHERE region_id = 30;
COMMIT;pause Press [Enter] to continue...connect / as sysdba
ALTER SYSTEM ARCHIVE LOG CURRENT;prompt "Setup for Flashback Transaction completed"
pause Press [Enter] to continue...exit
查看当前数据：
select * from hr.regions;REGION_ID REGION_NAME
---------- -------------------------10 No star20 Many Moons30 Still called Venus40 Red star50 Big star1 Europe2 Americas3 Asia4 Middle East and Africa9 rows selected.
查询和regions表相关的事务：
set lines 140
set pages 9999
col VERSIONS_STARTTIME for a22
col VERSIONS_ENDTIME for a22
select region_id, region_name, versions_xid, versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_operation from hr.regions versions between scn minvalue and maxvalue;REGION_ID REGION_NAME VERSIONS_XID VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME V
---------- ------------------------- ---------------- ----------------- -------------------- --------------- -------------------- -30 Still called Venus 05001900BE030000 9430052 09-JAN-23 03.26.31 P UM50 Big star 03001400EE030000 9430048 09-JAN-23 03.26.22 P UM40 Red star 03001400EE030000 9430048 09-JAN-23 03.26.22 P UM10 No star 03001400EE030000 9430048 09-JAN-23 03.26.22 P UM20 Many Moons 02001600C0030000 9430043 09-JAN-23 03.26.10 P UM10 Two Poles 02001600C0030000 9430043 09-JAN-23 03.26.10 P 9430048 09-JAN-23 03.26.22 P UM M50 Saturn 09000B00BB030000 9430037 09-JAN-23 03.25.53 P 9430048 09-JAN-23 03.26.22 P IM M40 Mars 09000B00BB030000 9430037 09-JAN-23 03.25.53 P 9430048 09-JAN-23 03.26.22 P IM M30 Venus 09000B00BB030000 9430037 09-JAN-23 03.25.53 P 9430052 09-JAN-23 03.26.31 P IM M20 Moon 09000B00BB030000 9430037 09-JAN-23 03.25.53 P 9430043 09-JAN-23 03.26.10 P IM M10 Pole 09000B00BB030000 9430037 09-JAN-23 03.25.53 P 9430043 09-JAN-23 03.26.10 P IM M1 Europe2 Americas3 Asia4 Middle East and Africa15 rows selected.
可查询行的所有修改历史。以上结果按时间降序排列，因此需从底部读起。
以上minvalue和maxvalue实际为scn.minvalue和scn.maxvalue，是常数。也可以查询某事件范围，如between timestamp(systimestamp - 1/24) and timestamp
依赖于伪列(pseudocolumns ); 伪列不是ISO标准。以上VERSIONS_XID，VERSIONS_STARTSCN ，VERSIONS_ENDSCN，VERSIONS_OPERATION都是伪列。最著名的伪列是ROWID。
Flashback Data Archive
本例参考（以后再写）
其它参考
- Oracle 10g: Recovering from Human Error Using Flashback
| Object Level | Scenario Examples | Flashback Technology | Depends On | Affectes Data |
|---|---|---|---|---|
| Database | Truncate table; undesired multitable changes made | Database | Flashback logs | TRUE |
| Table | Drop table | Drop | Recycle bin | TRUE |
| Update with the wrong WHERE clause | Table | Undo data | TRUE | |
| Compare current data with data from the past | Query | Undo data | FALSE | |
| Compare versions of rows | Version Query | Undo data | FALSE | |
| Transaction | Investigate and back out suspect transactions | Transaction Query | Undo/redo from archive logs | TRUE |
| Table and Transaction | Audit, compliance, historical reports, ILM | Data Archival (Temporal) | Tablespace | FALSE |
这篇关于Oracle Flashback示例集锦的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
