oracle 基础(一)--闪回技术

时间:2022-12-26 08:15:32

一,闪回表初探

   闪回须知:

 1 使用闪回表注意如下事项: 2 3 (1)被闪回的表必须启用行移动功能 4 5 SQL> alter table dept enable row movement; 6 7 (2)“FLASHBACK TABLE”命令的执行者必须有“FLASHBACK ANY TABLE”系统权限或者在被闪回的表上具有“FLASHBACK”对象权限。 8 9 (3)“FLASHBACK TABLE”属于DDL命令,隐式提交。 10 11 (4)SYS用户的任何表无法使用此功能。

  闪回的类型

    1闪回表( Flashback Table)
      将表回滚到一个过去的时间点或系统改变号scn上,用来快速恢复表。

    2闪回删除 ( Flashback Drop)
      将删除的对象从回收站中还原。
  
    3闪回版本查询(Flashback Version Query)
      查著看某个表在指定时间段或某两个scn之间的修改操作。

    4闪叫事务查询( Flashback Transaction Query)
      查看某个对象的事务信息,该信息中记录了撤销sql语句,用于实现对该事务进行撤销处理。

    5闪回数据库(Flashback Database)。
      将数据库前滚到一个过去的时间点或系统改变号scn,快速恢复数据库。

    6闪回数据归档(Flashback Data Archive)。
      将数据库对象的修改操作记录在闪回数据归档区域中,这样可以使得数据的闪回不依赖于UNDO撤销数据。
   

  01,闪回具体时间

   set time on; 开启时间显示

   查看具体时间:

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

    TO_CHAR(SYSDATE,'YY
    -------------------
    2019-02-21 21:00:02

   以时间的方式生成节点

   开始生成数据
    insert into dg values(11); 

SQL> select * from dg;

	ID
----------
	 1
	 2
	 3
	 4
	 5
	 6
	 7
	 8
	10

 

 再次插入数据

  闪回操作:

SQL>  flashback table dg to timestamp to_timestamp('2019-02-21 20:43:22','yyyy-mm-dd hh24:mi:ss');
 flashback table dg to timestamp to_timestamp('2019-02-21 20:43:22','yyyy-mm-dd hh24:mi:ss')
                 *
ERROR at line 1:
ORA-08185: Flashback not supported for user SYS

   发现默认不能用sys闪回,这个加入必须要的话是可以完成的,现再先不使用

    切换用户 

SQL> show user
USER is "SYS"
SQL> conn scott/123456
Connected.
SQL> show user
USER is "SCOTT"

   重新建表,插入数据查询

SQL> create table dg(id number);

Table created.

SQL> insert into dg values(1);

1 row created.

   再次闪回,

SQL> flashback table dg to timestamp to_timestamp('2019-02-21 20:57:08','yyyy-mm-dd hh24:mi:ss');
flashback table dg to timestamp to_timestamp('2019-02-21 20:57:08','yyyy-mm-dd hh24:mi:ss')
                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

    发现没开启行移

SQL> alter table dg enable row movement;

Table altered.

     这次总行了吧

SQL> flashback table dg to timestamp to_timestamp('2019-02-21 20:57:08','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

     再次查看数据 

SQL> select * from dg;

no rows selected

     我的天闪回有点多

  02,闪回大概时间

flashback table dg to timestamp(systimestamp-interval '10' minute);

   03,闪回SCN

    SCN查询 

01,
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1094422

02,
SQL> select dbms_flashback.get_system_change_number() from dual;

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
				  1094447

     flashback table dg to scn 1094422;

  05,多表闪回

flashback table scott.dg,scott.t to scn 1094422

 二,闪回删表

   01,删除表: 

SQL> drop table t
  2  ;

Table dropped.
oracle 基础(一)--闪回技术oracle 基础(一)--闪回技术
 1 select * from t
 2               *
 3 ERROR at line 1:
 4 ORA-00942: table or view does not exist
 5 
 6 
 7 SQL> insert into t values(2);
 8 insert into t values(2)
 9             *
10 ERROR at line 1:
11 ORA-00942: table or view does not exist
View Code

   开始闪回:

SQL> select * from t;
select * from t
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> flashback table scott.t to before drop;

Flashback complete.

SQL> select * from t;

	ID
----------
	 1
	 1
	 1
	 1
	 2

    02,删除表后重新创建新的同名表闪回

 1 SQL> drop table t;
 2 
 3 Table dropped.
 4 
 5 SQL> create table t(ID number);
 6 
 7 Table created.
 8 
 9 SQL> flashback table scott.t to before drop;  --有同名表闪回会报错
10 flashback table scott.t to before drop
11 *
12 ERROR at line 1:
13 ORA-38312: original name is used by an existing object
14 
15 
16 SQL> flashback table scott.t to before drop rename to t2;  --闪回的时候重新命名即可
17 
18 Flashback complete.
19 
20 SQL> select * from t2;
21 
22     ID
23 ----------
24      1
25      1
26      1
27      1
28      2
29 
30 SQL>     

三, 闪回-回收站

  01.功能开启

    查看当前用户:

     oracle 基础(一)--闪回技术

    查看回收站是否开启:

show parameter recyclebin   --查看功能是否开启

    开启功能:

alter system set recyclebin='ON' scope=spfile; 

    禁用功能:   

alter system set recyclebin='OFF' scope=spfile; 

    非sys用户会有报错信息:

11:04:05 SQL> alter system set recyclebin='ON' scope=spfile;
alter system set recyclebin='ON' scope=spfile
*
ERROR at line 1:
ORA-01031: insufficient privileges
oracle 基础(一)--闪回技术

   切换用户sys,开启回收站功能 oracle 基础(一)--闪回技术

     

  02回收功能查看:

      删除表:

drop table flashback_test;

       查看回收站是否有内容:   

 select * from recyclebin;
OBJECT_NAME		       ORIGINAL_NAME			OPERATION
------------------------------ -------------------------------- ---------
TYPE			  TS_NAME			 CREATETIME
------------------------- ------------------------------ -------------------
DROPTIME	       DROPSCN PARTITION_NAME			CAN CAN
------------------- ---------- -------------------------------- --- ---
   RELATED BASE_OBJECT PURGE_OBJECT	 SPACE
---------- ----------- ------------ ----------
BIN$gnQLKdbBRprgUwoAqMBIHA==$0 FLASHBACK_TEST			DROP
TABLE			  USERS 			 2019-02-22:09:19:27
2019-02-22:11:18:23    1159361					YES YES
     88632	 88632	      88632	     8

   03,闪回

      flashback 表闪回     

flashback table FLASHBACK_TEST to before drop;
 
oracle 基础(一)--闪回技术oracle 基础(一)--闪回技术
 1 11:19:45 SQL> select * from FLASHBACK_TEST;
 2 select * from FLASHBACK_TEST
 3               *
 4 ERROR at line 1:
 5 ORA-00942: table or view does not exist
 6 
 7 
 8 11:21:55 SQL> flashback table FLASHBACK_TEST to before drop;
 9 
10 Flashback complete.
11 
12 11:22:35 SQL> select * from FLASHBACK_TEST;
13 
14 A
15 --------------------------------------------------------------------------------
16 B
17 --------------------------------------------------------------------------------
18 C
19 --------------------------------------------------------------------------------
20 hello
21 with
22 world
23 
24 d2
25 sw
26 sf
27 
28 A
29 --------------------------------------------------------------------------------
30 B
31 --------------------------------------------------------------------------------
32 C
33 --------------------------------------------------------------------------------
34 
35 d3
36 swd
37 ss
38 
39 d2
40 sw
41 
42 A
43 --------------------------------------------------------------------------------
44 B
45 --------------------------------------------------------------------------------
46 C
47 --------------------------------------------------------------------------------
48 sf
49 
50 d2
51 sw
52 sf
53 
54 
55 11:22:37 SQL>
View Code

     回收站闪回

flashback table " BIN$gnQLKdbBRprgUwoAqMBIHA==$0" to before drop; 

     两个闪回操作实例

       创建表

CREATE TABLE t5(table_name CHAR(16),name CHAR(16));

         删除表

drop table t5;

        继续创建一个同名表

 CREATE TABLE t5(table_name CHAR(16),name CHAR(16));

       再把它删除

drop table t5;

       我们查看以下回收站有什么内容 

11:27:29 SQL> show recyclebin;
ORIGINAL NAME	 RECYCLEBIN NAME		OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
T5		 BIN$gnQLKdbDRprgUwoAqMBIHA==$0 TABLE	     2019-02-22:11:27:29
T5		 BIN$gnQLKdbCRprgUwoAqMBIHA==$0 TABLE	     2019-02-22:11:26:59

 可以看见有两个一样的表存在再回收站,那么问题来了,我执行下面这条命令

flashback table t5 to before drop;

 会恢复出来哪一张表呢???????

   你会不会觉得是第一次的表?或者后面那次的?

我们先来看看这个操作把.

oracle 基础(一)--闪回技术

会不会和你想的不一样呢?

  所以 呢这个闪回理论上是--遵循的是后入先出,意思是说后进入回收站的,先闪回

但是你非得要闪回特定的那就需要指定RECYCLEBIN_NAME 了

  也就是:

flashback table " BIN$gnQLKdbCRprgUwoAqMBIHA==$0" to before drop; 

   这种指定闪回

  

  当然还有一个删除 的谨慎操作咯

purge recyclebin;  --删除回收站所有的对象 

oracle 基础(一)--闪回技术

四,闪回查询

    什么是闪回查询呢?

    那就是以表为单位查询过去的数据称为闪回查询,主要有两种方式:1. 闪回时间点查询。利用select命令的“as of”子句与PL/SQL包dbms_flashback在过去的一个时间点上的查询。2. 闪回版本查询。利用select命令的“versions between”子句在过去的一段时间范围内的查询。
    创建一个新的表

13:30:27 SQL> create table t11 as select id from t;

Table created.

 

13:35:14 SQL> select * from t;

	ID
----------
	 1
13:35:34 SQL> update t11 set id = 11;

1 row updated.

13:35:39 SQL> update t11 set id = 12;

1 row updated.

13:35:47 SQL> update t11 set id = 13;

1 row updated.

13:35:51 SQL> update t11 set id = 14;

1 row updated.

13:35:56 SQL>
13:36:00 SQL> update t11 set id = 15;

1 row updated.

13:36:01 SQL> commit   这条数据提交了,只会commit最后一个数据,千米那的改变都不会计入闪回区的
13:36:08   2  ;

Commit complete.

13:36:09 SQL> update t11 set id = 16;

1 row updated.

13:36:21 SQL> commit;  提交 的会记录

Commit complete.

13:36:25 SQL> update t11 set id = 17;

1 row updated.

13:36:31 SQL> commit 提交 的会记录
13:36:33   2  ;

Commit complete.

13:36:35 SQL> update t11 set id = 18;

1 row updated.

13:36:40 SQL> commit 提交 的会记录
13:36:42   2  ;

Commit complete.

       执行闪回版本查询:

select * from t11 versions between timestamp(systimestamp -interval '6' minute) and maxvalue
              *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

       六分钟之内查询的话发现没有这个表,也就是那时候都不存在这个

       换小点时间的表查询

13:40:17 SQL> select * from t11 versions between timestamp(systimestamp -interval '4' minute) and maxvalue;

	ID
----------
	18
	17
	16

       发现4分组之内时候改变了三次

13:46:26 SQL> select * from t11 versions between timestamp(systimestamp -interval '5' minute) and maxvalue;

	ID
----------
	18
	17
	16
	15
	 1

       这一个时间段有数据出来了,包含了所有的变化了

13:48:49 SQL> select versions_xid,versions_startscn,versions_endscn,id from t11 
versions between timestamp(systimestamp - interval '13' minute) and maxvalue; VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN ID ---------------- ----------------- --------------- ---------- 05000F00A4030000 1168541 18 01001A00C1020000 1168531 1168541 17 02000500AD030000 1168520 1168531 16 04001500D4020000 1168504 1168520 15 1168504 1

 --versions_xid为事务号
--versions_startscn和versions_endscn分别是事务开始时的SCN和修改该行的下一个事务开始时的SCN。
--首尾衔接这两个字段的SCN号很容易得出真实的修改顺序

五,闪回事务查询

   查询事务:

    需要sysdba权限

    也可以执行:授予权限,让其他用户能使用

grant select any transaction to scott;

  指定事务:

SQL> select undo_sql from flashback_transaction_query where xid='01001A00C1020000';

UNDO_SQL
--------------------------------------------------------------------------------

   好吧,到这里事务先告一段落了