Oracle学习(十三):闪回

Oracle学习(十三):闪回
强烈推介IDEA2020.2破解激活,IntelliJ IDEA 注册码,2020.2 IDEA 激活码

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:模式名,一般为用户名
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

本文来源程序员囧辉,由javajgs_com转载发布,观点不代表Java架构师必看的立场,转载请标明来源出处:https://javajgs.com/archives/8779

发表评论