首页 > 代码库 > Oracle学习(十三):闪回

Oracle学习(十三):闪回

1.知识点:能够对比以下的录屏进行阅读

SQL> --1. 错误地删除了记录
SQL> --2. 错误地删除了表
SQL> --3. 查询历史记录
SQL> --4. 怎样撤销一个已经提交的事务
SQL> 
SQL> --闪回的类型
SQL> --1. 闪回表:将表回退到过去的一个时间上
SQL> --2. 闪回删除:Oracle回收站
SQL> --3. 闪回版本号查询:全部历史记录
SQL> --4. 闪回事务查询:通过select语句得到一个 undo_sql

SQL> --5. 闪回数据库
SQL> --6. 闪回归档日志
--------------------------------------------------------------------------
须要考虑的事情
FLASHBACK TABLE命令作为单一的事务运行。会得到一个单一的DML锁
表的统计数据不会被闪回
当前的索引和从属的对象会被维持
闪回表操作:1、系统表不能被闪回2、不能跨越DDL操作3、会被写入警告日志4、产生撤销和重做的数据

--------------------------------------------------------------------------
语法:
FLASHBACK TABLE[achema.]<table_name>
TO
{[BEFORE DROP[RENAME TO table]]
[SCN|TIMESTAMP]expr
[ENABLE|DISABLE]TRIGGERS}
schema:模式名,一般为username
TO TIMESTAMP:系统邮戳,包括年月日时分秒
TO SCN:系统更改号
ENABLE TRIGGERS:表示触发器恢复以后为enable状态。而默觉得disable状态
TO BEFORE DROP:表示恢复到删除前
RENAME TO table:表示更换表名
--------------------------------------------------------------------------
--SCN:系统改变号(时间)
SQL> select to_char(systimestamp,‘yyyy-mm-dd hh24:mi:ss*ff‘) time,
  2         timestamp_to_scn(systimestamp) SCN
  3  from dual;

--scope的取值: momery  spfile  both
--momery:仅仅改当前内存(即数据库),不改參数文件;重新启动数据库后恢复原样
--spfile:仅仅改參数文件,不改数据库。重新启动数据库后变为改动后的
--both:数据库。參数都改

--授予闪回权限:grant flashback any table to scott;
--授予事务查询权限:grant select any transaction to scott;

SQL> -- 1.闪回表:将表回退到过去的一个时间上
SQL> flashback table  flashback_table to scn 3784220;

SQL> --开启表的行移动
SQL> alter table flashback_table enable row movement;

SQL> --2.闪回删除
SQL> flashback table testseq to before drop;
SQL> --闪回重名的表
SQL> flashback table testseq to before drop rename to testseq_old;

SQL> --查看回收站,管理员没有回收站
SQL> show recyclebin
SQL> --清空回收站
SQL> purge recyclebin;
SQL> --通过回收站的名字查看表要加双引號
SQL> select * from "BIN$UhseqyX1Reyl5iurpupyEg==$0";

SQL> /*
SQL> 小结:
SQL> 基本概念: 1. SCN   2. 900秒  3. 权限 flashback any tables
SQL> 闪回表:将表回退到过去的一个时间
SQL>          1. 行移动
SQL>          2. 运行闪回表
SQL>         注意: 系统表不能闪回;不能跨越DDL
SQL>         问题: 怎样得到离该操作近期的一个时间?

?

SQL> 闪回删除: Oracle的回收站 SQL> 1. 运行闪回删除 SQL> 2. 通过回收站中的名字闪回(双引號) SQL> 3. 重名的表 SQL> 4. 触发器(disable) SQL> */ SQL> --3.闪回版本号查询 SQL> --运行闪回版本号查询 SQL> select tid,tname,versions_operation 操作,versions_starttime 起始时间, 2 versions_endtime 结束时间,versions_xid 事务号 3 from versions_table 4 versions between TIMESTAMP MINVALUE and MAXVALUE 5 order by 1,4; SQL> --4.闪回事务查询 SQL> --视图flashback_transaction_query SQL> desc flashback_transaction_query SQL> --得到XID SQL> select tid,tname,versions_operation 操作,versions_starttime 起始时间, 2 versions_endtime 结束时间,versions_xid 事务号 3 from transaction_table 4 versions between TIMESTAMP MINVALUE and MAXVALUE 5 order by 事务号; SQL> --通过XID查询事务信息,再利用undo_sql撤销事务操作 SQL> select operation,undo_sql 2 from flashback_transaction_query 3 where xid=‘080029005C050000‘;

2.在Sqlplus下实际运行的结果录屏
SQL> host cls

SQL> /*
SQL> 1. 错误地删除了记录
SQL> 2. 错误地删除了表
SQL> 3. 查询历史记录
SQL> 4. 怎样撤销一个已经提交的事务
SQL> 
SQL> 闪回的类型
SQL> 1. 闪回表:将表回退到过去的一个时间上
SQL> 2. 闪回删除:Oracle回收站
SQL> 3. 闪回版本号查询:全部历史记录
SQL> 4. 闪回事务查询: 通过select语句得到一个 undo_sql
SQL> 
SQL> 5. 闪回数据库
SQL> 6. 闪回归档日志
SQL> */
SQL> --SCN系统改变号(时间)
SQL> select to_char(systimestamp,‘yyyy-mm-dd hh24:mi:ss*ff‘) 时间,
  2         timestamp_to_scn(systimestamp) SCN
  3  from dual;

时间                                 SCN                                        
----------------------------- ----------                                        
2012-10-25 10:31:02*000000       3783972                                        

SQL> /*
SQL> SQL> show parameter undo
SQL> 
SQL> NAME                                 TYPE        VALUE
SQL> ------------------------------------ ----------- --------------------
SQL> undo_management                      string      AUTO
SQL> undo_retention                       integer     1200
SQL> undo_tablespace                      string      UNDOTBS1
SQL> SQL> --scope的取值: momery  spfile both
SQL> SQL> alter system set undo_retention=900 scope=both;
SQL> */
SQL> --权限  grant flashback any table to scott;
SQL> create table flashback_table
  2  (fid number,fname varchar2(20));

表已创建。

SQL> insert into flashback_table values(1,‘Tom‘); 已创建 1 行。

SQL> insert into flashback_table values(2,‘Mary‘); 已创建 1 行。

SQL> insert into flashback_table values(3,‘Mike‘); 已创建 1 行。

SQL> commit; 提交完毕。 SQL> --当前时间 SQL> select to_char(systimestamp,‘yyyy-mm-dd hh24:mi:ss*ff‘) 时间, 2 timestamp_to_scn(systimestamp) SCN 3 from dual; 时间 SCN ----------------------------- ---------- 2012-10-25 10:36:22*046000 3784220 SQL> select * from flashback_table; FID FNAME ---------- -------------------- 1 Tom 2 Mary 3 Mike SQL> --删除mary SQL> delete from flashback_table where fid =2; 已删除 1 行。 SQL> commit; 提交完毕。

SQL> select * from flashback_table; FID FNAME ---------- -------------------- 1 Tom 3 Mike SQL> --运行闪回表 SQL> flashback table flashback_table to scn 3784220; flashback table flashback_table to scn 3784220 * 第 1 行出现错误: ORA-08189: 由于未启用行移动功能, 不能闪回表 SQL> --开启表的行移动 SQL> alter table flashback_table enable row movement; 表已更改。

SQL> flashback table flashback_table to scn 3784220; 闪回完毕。 SQL> select * from flashback_table; FID FNAME ---------- -------------------- 1 Tom 2 Mary 3 Mike SQL> --问题:怎样获取近期的一个时间? SQL> host cls SQL> --闪回删除 SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- DEPT TABLE EMP TABLE BONUS TABLE SALGRADE TABLE EMP20 TABLE TESTSAVEPOINT TABLE TEST2 TABLE MYPERSON TABLE EMP10 TABLE EMPINCOME TABLE VIEW1 VIEW TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- VIEW2 VIEW TESTSEQ TABLE HREMP SYNONYM MSG1 TABLE TEST1 TABLE PM_CI TABLE PM_STU TABLE FLASHBACK_TABLE TABLE SYS_TEMP_FBT TABLE 已选择20行。 SQL> drop table test1; 表已删除。

SQL> show recyclebin ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- TEST1 BIN$Rn2qG7V+SO2k+aNrG/oYww==$0 TABLE 2012-10-25:10:45:11 SQL> purge recyclebin; 回收站已清空。 SQL> --管理员没有回收站 SQL> select * from TESTSEQ; TID TNAME ---------- -------------------- 3 aaa 4 aaa 5 aaa 8 aaa SQL> drop table TESTSEQ; 表已删除。

SQL> show recyclebin ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- TESTSEQ BIN$UhseqyX1Reyl5iurpupyEg==$0 TABLE 2012-10-25:10:47:38 SQL> select * from TESTSEQ; select * from TESTSEQ * 第 1 行出现错误: ORA-00942: 表或视图不存在 SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- DEPT TABLE EMP TABLE BONUS TABLE SALGRADE TABLE EMP20 TABLE TESTSAVEPOINT TABLE TEST2 TABLE MYPERSON TABLE EMP10 TABLE EMPINCOME TABLE VIEW1 VIEW TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- VIEW2 VIEW HREMP SYNONYM MSG1 TABLE BIN$UhseqyX1Reyl5iurpupyEg==$0 TABLE PM_CI TABLE PM_STU TABLE FLASHBACK_TABLE TABLE SYS_TEMP_FBT TABLE 已选择19行。 SQL> select * from BIN$UhseqyX1Reyl5iurpupyEg==$0; select * from BIN$UhseqyX1Reyl5iurpupyEg==$0 * 第 1 行出现错误: ORA-00933: SQL 命令未正确结束 SQL> select * from "BIN$UhseqyX1Reyl5iurpupyEg==$0"; TID TNAME ---------- -------------------- 3 aaa 4 aaa 5 aaa 8 aaa SQL> host cls SQL> show recyclebin ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- TESTSEQ BIN$UhseqyX1Reyl5iurpupyEg==$0 TABLE 2012-10-25:10:47:38 SQL> --运行闪回删除 SQL> flashback table testseq to before drop; 闪回完毕。 SQL> show recyclebin SQL> select * from testseq; TID TNAME ---------- -------------------- 3 aaa 4 aaa 5 aaa 8 aaa SQL> host cls SQL> drop table testseq; 表已删除。

SQL> create table testseq(tid number); 表已创建。 SQL> drop table testseq; 表已删除。 SQL> show recyclebin ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- TESTSEQ BIN$ZsXHtmHLTLu40DAC6jiKqg==$0 TABLE 2012-10-25:10:51:22 TESTSEQ BIN$hBllsvl5Tum9hHaSvtQhag==$0 TABLE 2012-10-25:10:51:01 SQL> flashback table testseq to before drop; 闪回完毕。 SQL> show recyclebin ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- TESTSEQ BIN$hBllsvl5Tum9hHaSvtQhag==$0 TABLE 2012-10-25:10:51:01 SQL> select * from testseq; 未选定行 SQL> flashback table testseq to before drop; flashback table testseq to before drop * 第 1 行出现错误: ORA-38312: 原始名称已被现有对象使用 SQL> --闪回重名的表 SQL> flashback table testseq to before drop rename to testseq_old; 闪回完毕。

SQL> select * from testseq_old; TID TNAME ---------- -------------------- 3 aaa 4 aaa 5 aaa 8 aaa SQL> host cls SQL> /* SQL> 小结: SQL> 基本概念: 1. SCN 2. 900秒 3. 权限 flashback any tables SQL> 闪回表:将表回退到过去的一个时间 SQL> 1. 行移动 SQL> 2. 运行闪回表 SQL> 注意: 系统表不能闪回。不能跨越DDL SQL> 问题: 怎样得到离该操作近期的一个时间?? SQL> 闪回删除: Oracle的回收站 SQL> 1. 运行闪回删除 SQL> 2. 通过回收站中的名字闪回(双引號) SQL> 3. 重名的表 SQL> 4. 触发器(disable) SQL> */ SQL> host cls SQL> --闪回版本号查询 SQL> create table versions_table 2 (tid number,tname varchar2(20)); 表已创建。 SQL> insert into versions_table values(1.‘Tom‘); insert into versions_table values(1.‘Tom‘) * 第 1 行出现错误: ORA-00917: 缺失逗号 SQL> insert into versions_table values(1,‘Tom‘); 已创建 1 行。

SQL> commit; 提交完毕。 SQL> insert into versions_table values(2,‘Mary‘); 已创建 1 行。 SQL> commit; 提交完毕。

SQL> insert into versions_table values(3,‘Mike‘); 已创建 1 行。 SQL> commit; 提交完毕。 SQL> update versions_table set tname=‘MaryNew‘ where tid=2; 已更新 1 行。 SQL> commit; 提交完毕。 SQL> select * from versions_table; TID TNAME ---------- -------------------- 1 Tom 2 MaryNew 3 Mike SQL> --怎样查询Mary? SQL> --运行闪回版本号查询 SQL> select tid,tname 2 from versions_table 3 versions between TIMESTAMP MINVALUE and MAXVALUE 4 order by 1; TID TNAME ---------- -------------------- 1 Tom 2 MaryNew 2 Mary 3 Mike SQL> select tid,tname,versions_operation 操作,versions_starttime 起始时间, 2 versions_endtime 结束时间,versions_xid 事务号 3 from versions_table 4 versions between TIMESTAMP MINVALUE and MAXVALUE 5 order by 1,4; TID TNAME ---------- -------------------- - 起始时间 --------------------------------------------------------------------------- 结束时间 --------------------------------------------------------------------------- 事务号 ---------------- 1 Tom I 25-10月-12 11.17.28 上午 03002E005A050000 TID TNAME ---------- -------------------- - 起始时间 --------------------------------------------------------------------------- 结束时间 --------------------------------------------------------------------------- 事务号 ---------------- 2 Mary I 25-10月-12 11.17.37 上午 25-10月-12 11.18.16 上午 02000D0064050000 TID TNAME ---------- -------------------- - 起始时间 --------------------------------------------------------------------------- 结束时间 --------------------------------------------------------------------------- 事务号 ---------------- 2 MaryNew U 25-10月-12 11.18.16 上午 0600030065050000 TID TNAME ---------- -------------------- - 起始时间 --------------------------------------------------------------------------- 结束时间 --------------------------------------------------------------------------- 事务号 ---------------- 3 Mike I 25-10月-12 11.17.46 上午 0400130061050000 SQL> set linesize 150 SQL> col tid for 99 SQL> col tname for a10 SQL> col 操作 for a8 SQL> col 起始时间 for a40 SQL> col 结束时间 for a40 SQL> select tid,tname,versions_operation 操作,versions_starttime 起始时间, 2 versions_endtime 结束时间,versions_xid 事务号 3 from versions_table 4 versions between TIMESTAMP MINVALUE and MAXVALUE 5 order by 1,4; TID TNAME 操作 起始时间 结束时间 事务号 --- ---------- -------- ---------------------------------------- ---------------------------------------- ---------------- 1 Tom I 25-10月-12 11.17.28 上午 03002E005A050000 2 Mary I 25-10月-12 11.17.37 上午 25-10月-12 11.18.16 上午 02000D0064050000 2 MaryNew U 25-10月-12 11.18.16 上午 0600030065050000 3 Mike I 25-10月-12 11.17.46 上午 0400130061050000 SQL> col 起始时间 for a30 SQL> col 结束时间 for a30 SQL> col 操作 for a4 SQL> select tid,tname,versions_operation 操作,versions_starttime 起始时间, 2 versions_endtime 结束时间,versions_xid 事务号 3 from versions_table 4 versions between TIMESTAMP MINVALUE and MAXVALUE 5 order by 1,4; TID TNAME 操作 起始时间 结束时间 事务号 --- ---------- ---- ------------------------------ ------------------------------ ---------------- 1 Tom I 25-10月-12 11.17.28 上午 03002E005A050000 2 Mary I 25-10月-12 11.17.37 上午 25-10月-12 11.18.16 上午 02000D0064050000 2 MaryNew U 25-10月-12 11.18.16 上午 0600030065050000 3 Mike I 25-10月-12 11.17.46 上午 0400130061050000 SQL> host cls SQL> --闪回事务查询 SQL> create table transaction_table 2 (tid number,tname varchar2(20)); 表已创建。

SQL> --第一个事务 SQL> insert into transaction_table values(1,‘Tom‘); 已创建 1 行。 SQL> insert into transaction_table values(2,‘Mary‘); 已创建 1 行。 SQL> insert into transaction_table values(3,‘Mike‘); 已创建 1 行。 SQL> commit; 提交完毕。 SQL> --第二个事务 SQL> update transaction_table set tname=‘MaryNew‘ where tid=2; 已更新 1 行。

SQL> delete from transaction_table where tid =3; 已删除 1 行。

SQL> commit; 提交完毕。 SQL> --怎样撤销第二个事务?? SQL> --视图flashback_transaction_query SQL> desc flashback_transaction_query 名称 是否为空?

类型 ----------------------------------------------------------------------------------- -------- -------------------------------------------------------- XID RAW(8) START_SCN NUMBER START_TIMESTAMP DATE COMMIT_SCN NUMBER COMMIT_TIMESTAMP DATE LOGON_USER VARCHAR2(30) UNDO_CHANGE# NUMBER OPERATION VARCHAR2(32) TABLE_NAME VARCHAR2(256) TABLE_OWNER VARCHAR2(32) ROW_ID VARCHAR2(19) UNDO_SQL VARCHAR2(4000) SQL> --得到XID SQL> select tid,tname,versions_operation 操作,versions_starttime 起始时间, 2 versions_endtime 结束时间,versions_xid 事务号 3 from transaction_table 4 versions between TIMESTAMP MINVALUE and MAXVALUE 5 order by 事务号; TID TNAME 操作 起始时间 结束时间 事务号 --- ---------- ---- ------------------------------ ------------------------------ ---------------- 3 Mike I 25-10月-12 11.29.38 上午 25-10月-12 11.30.23 上午 0100100060050000 2 Mary I 25-10月-12 11.29.38 上午 25-10月-12 11.30.23 上午 0100100060050000 1 Tom I 25-10月-12 11.29.38 上午 0100100060050000 2 MaryNew U 25-10月-12 11.30.23 上午 080029005C050000 3 Mike D 25-10月-12 11.30.23 上午 080029005C050000 SQL> select operation,undo_sql 2 from flashback_transaction_query 3 where xid=‘080029005C050000‘; OPERATION -------------------------------- UNDO_SQL ------------------------------------------------------------------------------------------------------------------------------------------------------ DELETE insert into "SCOTT"."TRANSACTION_TABLE"("TID","TNAME") values (‘3‘,‘Mike‘); UPDATE update "SCOTT"."TRANSACTION_TABLE" set "TNAME" = ‘Mary‘ where ROWID = ‘AAANpoAAEAAAAJvAAB‘; BEGIN SQL> insert into "SCOTT"."TRANSACTION_TABLE"("TID","TNAME") values (‘3‘,‘Mike‘) 2 ; 已创建 1 行。 SQL> update "SCOTT"."TRANSACTION_TABLE" set "TNAME" = ‘Mary‘ where ROWID = ‘AAANpoAAEAAAAJvAAB‘; 已更新 1 行。 SQL> commit; 提交完毕。

SQL> select * from transaction_table; TID TNAME --- ---------- 1 Tom 2 Mary 3 Mike SQL> spool off



Oracle学习(十三):闪回