1.Flashback Transaction Query功能
从FLASHBACK_TRANSACTION_QUERY视图中获得事务的历史以及Undo_sql，也就是说可以通过查询视图FLASHBACK_TRANSACTION_QUERY获得表的操作记录，同时可以获得恢复错误操作的SQL语句。
2.体验一下这个功能带给我们的神奇吧
1）创建测试表test_ftq，并进行一些简单的插入和删除操作
sec@orcl> create table test_ftq (a int, b int);
Table created.
sec@orcl> insert into test_ftq values (1,1);
1 row created.
sec@orcl> insert into test_ftq values (2,2);
1 row created.
sec@orcl> insert into test_ftq values (3,3);
1 row created.
sec@orcl> commit;
Commit complete.
2）查询在一个时间段内对表test_ftq的操作记录
sec@orcl> select versions_xid, versions_operation
2 from test_ftq
3 versions between timestamp to_date('2009-04-28 05:28:06','yyyy-mm-dd hh24:mi:ss') and maxvalue
4 WHERE versions_xid is not null
5 order by versions_starttime;
VERSIONS_XID V
---------------- -
04001E0043950400 I
04001E0043950400 I
04001E0043950400 I
04001A0042950400 D
3）根据VERSIONS_XID的信息可以通过查询视图FLASHBACK_TRANSACTION_QUERY得到相应的回滚SQL语句
sec@orcl> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY where xid='04001E0043950400';
UNDO_SQL
------------------------------------------------------------------------------------------------------------------------------
delete from "SEC"."TEST_FTQ" where ROWID = 'AABIejAAfAAAAASAAA';
sec@orcl> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY where xid='04001A0042950400';
UNDO_SQL
------------------------------------------------------------------------------------------------------------------------------
insert into "SEC"."TEST_FTQ"("A","B") values ('1','1');
3.之所以有这样神奇的效果，与视图FLASHBACK_TRANSACTION_QUERY是分不开的，OK，让我们看看oracle官方文档中关于该视图的描述
--The End --
从FLASHBACK_TRANSACTION_QUERY视图中获得事务的历史以及Undo_sql，也就是说可以通过查询视图FLASHBACK_TRANSACTION_QUERY获得表的操作记录，同时可以获得恢复错误操作的SQL语句。
2.体验一下这个功能带给我们的神奇吧
1）创建测试表test_ftq，并进行一些简单的插入和删除操作
sec@orcl> create table test_ftq (a int, b int);
Table created.
sec@orcl> insert into test_ftq values (1,1);
1 row created.
sec@orcl> insert into test_ftq values (2,2);
1 row created.
sec@orcl> insert into test_ftq values (3,3);
1 row created.
sec@orcl> commit;
Commit complete.
2）查询在一个时间段内对表test_ftq的操作记录
sec@orcl> select versions_xid, versions_operation
2 from test_ftq
3 versions between timestamp to_date('2009-04-28 05:28:06','yyyy-mm-dd hh24:mi:ss') and maxvalue
4 WHERE versions_xid is not null
5 order by versions_starttime;
VERSIONS_XID V
---------------- -
04001E0043950400 I
04001E0043950400 I
04001E0043950400 I
04001A0042950400 D
3）根据VERSIONS_XID的信息可以通过查询视图FLASHBACK_TRANSACTION_QUERY得到相应的回滚SQL语句
sec@orcl> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY where xid='04001E0043950400';
UNDO_SQL
------------------------------------------------------------------------------------------------------------------------------
delete from "SEC"."TEST_FTQ" where ROWID = 'AABIejAAfAAAAASAAA';
sec@orcl> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY where xid='04001A0042950400';
UNDO_SQL
------------------------------------------------------------------------------------------------------------------------------
insert into "SEC"."TEST_FTQ"("A","B") values ('1','1');
3.之所以有这样神奇的效果，与视图FLASHBACK_TRANSACTION_QUERY是分不开的，OK，让我们看看oracle官方文档中关于该视图的描述
FLASHBACK_TRANSACTION_QUERY
FLASHBACK_TRANSACTION_QUERY displays information about all flashback transaction queries in the database.
| Column | Datatype | NULL | Description |
|---|---|---|---|
| XID | RAW(8) | Transaction identifier | |
| START_SCN | NUMBER | Transaction start system change number (SCN) | |
| START_TIMESTAMP | DATE | Transaction start timestamp | |
| COMMIT_SCN | NUMBER | Transaction commit system change number (null for active transactions) | |
| COMMIT_TIMESTAMP | DATE | Transaction commit timestamp (null for active transactions) | |
| LOGON_USER | VARCHAR2(30) | Logon user for the transaction | |
| UNDO_CHANGE# | NUMBER | Undo system change number (1 or higher) | |
| OPERATION | VARCHAR2(32) | Forward-going DML operation performed by the transaction:
|
|
| TABLE_NAME | VARCHAR2(256) | Name of the table to which the DML applies | |
| TABLE_OWNER | VARCHAR2(32) | Owner of the table to which the DML applies | |
| ROW_ID | VARCHAR2(19) | Rowid of the row that was modified by the DML | |
| UNDO_SQL | VARCHAR2(4000) | SQL to undo the DML indicated by OPERATION |
--The End --
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/70034820/viewspace-2993866/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- 中国OCM联盟隆重落户ITPUB社区，百位OCM已入驻，欢迎更多的OCM加入我们哦！
- OCMU联盟-一个有温度的联盟
- 丢掉盔甲，我们依然是骄傲的骑士——Secooler“三问四看”话认证培训
- 【数据安全】一次惊心动魄的ASM磁盘头损坏故障处理过程带来的深思
- 【参数】恢复db_recovery_file_dest_size参数为默认值“0”方法
- 【如何做快乐的DBA】ITPUB社区第一期博客专家专题“如何做快乐的DBA”
- 【Bitmap Index】B-Tree索引与Bitmap位图索引的锁代价比较研究
- 【RMAN】使用RMAN的Duplicate功能创建物理DataGuard报错（ORA-17627、ORA-17629）处理
- 【CUBE】Oracle分组函数之CUBE魅力
- 【ROLLUP】Oracle分组函数之ROLLUP魅力
