Oracle 之闪回操作

最新推荐文章于 2024-05-07 14:47:00 发布

lideguo1979

最新推荐文章于 2024-05-07 14:47:00 发布

阅读量994

点赞数

文章标签： oracle sql table insert triggers parameters

本文链接：https://blog.csdn.net/lideguo1979/article/details/6800125

版权

闪回(Flashback)

SQL> /*

SQL> 错误操作：

SQL> 1. 错误的delete一条记录，并且commit

SQL> 2. 错误地删除了一个表: drop table

SQL> 3. 查询某个表的历史记录(所有已经提交了的历史记录)

SQL> 4. 错误地执行了一个事务

SQL>

SQL> 对应闪回的四种类型:

SQL> 1. 闪回表

SQL> 2. 闪回删除

SQL> 3. 闪回版本查询

SQL> 4. 闪回事务查询

SQL> 5. 闪回数据库

SQL> */

SQL> host cls

SQL> --闪回的时间参数

SQL> conn / as sysdba

已连接。

SQL> show user

USER 为 "SYS"

SQL> show parameters undo;

l闪回表，实际上是将表中的数据快速恢复到过去的一个是焦点或者系统改变号SCN上。实现表的闪回，需要使用到与撤销表空间相关的undo信息，通过show parameter undo命令可以了解这些信息。

l用户对表数据的修改操作，都记录在撤销表空间中，这为表的闪回提供了数据恢复的基础。例如，某个修改操作在提交后被记录在撤销表空间中，保留时间为900秒，用户可以在这900秒的时间内对表进行闪回操作，从而将表中的数据恢复到修改之前的状态。

NAME TYPE VALUE

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

SQL> -- 将900秒改为20分钟

SQL> alter system set undo_retention=1200 scope=both;

系统已更改。

SQL> show parameters undo;

NAME TYPE VALUE

undo_management string AUTO

undo_retention integer 1200

undo_tablespace string UNDOTBS1

SQL> /*

SQL> scope的取值: session spfile(参数文件) both

SQL> */

SQL> --为scott授予闪回的权限

SQL> grant flashback any table to scott;

授权成功。

SQL> conn scott/tiger

已连接。

SQL> show user

USER 为 "SCOTT"

SQL> host cls

SQL> --第一种类型闪回：闪回表

SQL> create table flashback_table1

2 (fid number,fnamevarchar2(10));

表已创建。

SQL> insert into flashback_table1 values(&fid,'&fname');

输入 fid 的值: 1

输入 fname 的值: Tom

SQL> commit;

提交完成。

SQL> select * from flashback_table1;

FID FNAME

1 Tom

2 Mary

3 Mike

SQL> --记录当前的系统时间(SCN)

SQL> select sysdate 时间, timestamp_to_scn(sysdate) SCN fromdual;

时间 SCN

21-9月 -11 4354761

SQL> delete from flashback_table1 wherefid=2;

已删除 1 行。

SQL> commit;

提交完成。

SQL> select * from flashback_table1;

FID FNAME

---------- ----------

1 Tom

3 Mike

SQL> --执行闪回表

SQL> flashback table flashback_table1 toscn 4354761;

flashback table flashback_table1 to scn4354761

第 1 行出现错误:

ORA-08189: 因为未启用行移动功能, 不能闪回表

SQL> select rowid,fid,fname fromflashback_table1;

ROWID FID FNAME

------------------ ---------- ----------

AAANh7AAEAAAAGnAAA 1 Tom

AAANh7AAEAAAAGnAAC 3 Mike

SQL> --开启行移动功能

SQL> alter table flashback_table1 enable row movement;

表已更改。

SQL> flashback table flashback_table1 to scn 4354761;

闪回完成。

SQL> select rowid,fid,fname fromflashback_table1;

ROWID FID FNAME

------------------ --------------------

AAANh7AAEAAAAGnAAB 1 Tom

AAANh7AAEAAAAGnAAD 2 Mary

AAANh7AAEAAAAGnAAE 3 Mike

闪回表语法：

FLASHBACK TABLE [schema.]<table_name>

{[BEFORE DROP [RENAME TO table]]

[SCN|TIMESTAMP]expr

[ENABLE|DISABLE]TRIGGERS}

lschema：模式名，一般为用户名。

lTO TIMESTAMP：系统邮戳，包含年、月、日、时、分、秒。

lTO SCN：系统更改号，

lENABLE TRIGGERS：表示触发器恢复以后为enable状态，而默认为disable状态。

lTO BEFORE DROP：表示恢复到删除之前。

lRENAME TO table：表示更换表名。

SQL> --闪回表的思想：将表回退到过去的一个时间上

SQL> --问题：不知道过去时间，怎么办？

SQL> host cls

SQL> --闪回删除

SQL> --Oracle的回收站

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ -----------------

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

FLASHBACK_TABLE TABLE

FLASHBACK_TABLE1 TABLE

SYS_TEMP_FBT TABLE

已选择7行。

删除表。

drop table FLASHBACK_TABLE;

查看回收站

show recyclebin;

清空回收站

purge recyclebin;

彻底删除

drop table FLASHBACK_TABLE1 purge;

回收站只对普通用户有效

删除表

drop table test123;

show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

TEST123 BIN$w6NYaQ1jSZ6uzO2jUQ/ALw==$0 TABLE 2011-09-21:10:35:32

SQL> --闪回删除：从回收站中取回表

SQL> flashback table test123 to before drop;

闪回完成。

SQL> --通过回收站中的名称闪回删除

SQL> flashback table"BIN$9JMrb6kbRCON287lDV+5dA==$0" to before drop;

闪回完成。

SQL> host cls

闪回删除：回收站（recyclebin）简介

l回收站是所有被删除对象及其相依对象的逻辑存储容器，例如当一个表被删除(drop)时，该表及其相依对象并不会马上被数据库彻底删除，而是被保存到回收站中。

l回收站将用户执行的drop操作记录在一个系统表中，也就是将被删除的对象写到一个数据字典中。如果确定不再需要该对象，可以使用purge命令对回收站进行清空。

l被删除的对象的名字可能是相同的，例如用户创建了一个test表，使用drop命令删除该表后，又创建了一个test表，这时，如果再次删除该表就会导致向回收站中添加了两个相同的表。

回收站中对象的命名规则

l为了确保添加到回收站中的对象的名称都是唯一的，系统会对这些保存到回收站中的对象进行重命名，重命名的格式如下：

BIN$globalUID$version

•其中： BIN表示RECYCLEBIN；globalUID是一个全局唯一的、24个字节长的对象，该标识与原对象名没有任何关系；version指数据库分配的版本号。

SQL> -- 闪回重名的表

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ -----------------

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

TEST123 TABLE

SYS_TEMP_FBT TABLE

已选择6行。

SQL> drop table TEST123;

表已删除。

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ -----------------

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

BIN$eqIO0J1LQ0G1rYAnIHWrdg==$0 TABLE

SYS_TEMP_FBT TABLE

已选择6行。

SQL> create table test123(tid number);

表已创建。

SQL> insert into test123 values(1);

已创建 1 行。

SQL> insert into test123 values(2);

已创建 1 行。

SQL> commit;

提交完成。

SQL> drop table test123;

表已删除。

SQL> --查询回收站：应该有两个重名的test123

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

---------------------------------------------- ------------

TEST123 BIN$HDhYGEaXTayAnETFAUoaNw==$0 TABLE 2011-09-21:10:41:59

TEST123 BIN$eqIO0J1LQ0G1rYAnIHWrdg==$0 TABLE 2011-09-21:10:41:17

SQL> --闪回这两个表

SQL> flashback table test123 to beforedrop;

闪回完成。

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

---------------------------------------------- ------------

TEST123 BIN$eqIO0J1LQ0G1rYAnIHWrdg==$0 TABLE 2011-09-21:10:41:17

SQL> --闪回同名的表，先闪回最后删除那个

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ -----------------

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

BIN$eqIO0J1LQ0G1rYAnIHWrdg==$0 TABLE

SYS_TEMP_FBT TABLE

TEST123 TABLE

已选择7行。

SQL> flashback table test123 to beforedrop;

flashback table test123 to before drop

第 1 行出现错误:

ORA-38312: 原始名称已被现有对象使用

SQL> --重命名

SQL> flashback table test123 to beforedrop rename to test1234;

闪回完成。

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ -----------------

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

TEST1234 TABLE

SYS_TEMP_FBT TABLE

TEST123 TABLE

已选择7行。

SQL> select * from test123;

TID

----------

SQL> select * from test1234;

TID TNAME

---------- --------------------

1 Tom

SQL> host cls

闪回删除: 需要考虑的事情

l闪回删除对下列表无效:

在SYSTEM 表空间内的表

用精细审计的数据库或虚拟的私人数据库

属于字典管理的表空间

由于空间不足已经被手动或自动删除的表

l以下依赖不被保护:

位图索引

表之前删掉的索引

lFLASHBACK TABLE命令作为单一的事务执行，会得到一个单一的DML锁

表的统计数据不会被闪回

当前的索引和从属的对象会被维持

l闪回表操作:

系统表不能被闪回

不能跨越DDL操作

会被写入警告日志

产生撤销和重做的数据

SQL> --闪回版本查询

SQL> creat table versions_table

SP2-0734: 未知的命令开头 "creat tabl..."- 忽略了剩余的行。

SQL> create table versions_table

2 (vid number,

3 vname varchar(20));

表已创建。

SQL> insert into versions_tablevalues(1,'Tom');

已创建 1 行。

SQL> commit;

提交完成。

SQL> insert into versions_tablevalues(2,'Mary');

已创建 1 行。

SQL> commit;

提交完成。

SQL> insert into versions_tablevalues(3,'Mike');

已创建 1 行。

SQL> commit;

提交完成。

SQL> update versions_table setvname='Mary123' where vid=2;

已更新 1 行。

SQL> commit;

提交完成。

SQL> select * from versions_table;

VID VNAME

1 Tom

2 Mary123

3 Mike

SQL> /*

SQL> 问题：1

SQL> 1. 如何获取前面三个版本？

SQL> 2. 如何获取该表过去的一个时间?

SQL> */

闪回版本查询

闪回版本查询，提供了一个审计行改变的查询功能，通过它可以查找到所有已经提交了的行记录。其语法格式如下：

selectcolumn_name[,column_name,...]

from table_name

versionsbetween [SCN|TIMESTAMP] [expr|MINVALUE]

and [epxr|MAXVALUE] as of [SCN|TIMESTAMP]expr;

备注：

其中：column_name列名；table_name表名；between...and时间段；SCN系统改变号；TIMESTAMP时间戳；ASOF表示恢复单个版本;MAXVALUE最大值；MINVALUE最小值；expr指定一个值或者表达式。

SQL> --执行闪回版本查询，获取所有的版本信息

SQL> selectvid,vname,versions_operation,versions_starttime,versions_endtime

2 from versions_table

3 versions between timestampminvalue and maxvalue

4 order by 1,4;

SQL> col VERSIONS_STARTTIME for a20

SQL> col VERSIONS_ENDTIME for a20

SQL> /

SQL> col vname for a8

SQL> /

SQL> col VERSIONS_STARTTIME for a30

SQL> col VERSIONS_ENDTIME for a30

SQL> /

SQL> set linesize 150

SQL> /

VID VNAME VVERSIONS_STARTTIME VERSIONS_ENDTIME

---------- -------- - ------------------------------

1Tom I 21-9月 -11 11.01.09 上午

2Mary I 21-9月 -11 11.01.24 上午 21-9月 -11 11.02.00 上午

2Mary123 U 21-9月 -11 11.02.00 上午

3Mike I 21-9月 -11 11.01.39 上午

SQL> /*

SQL> selectvid,vname,versions_operation,versions_starttime,versions_endtime

SQL> from versions_table

SQL> versions between timestamp minvalueand maxvalue

SQL> order by 1,4;

SQL> */

SQL> --通过闪回版本查询，可以获取某个表过去已经提交了的历史版本信息

SQL> host cls

SQL> --闪回事务查询

SQL> create table transaction_table

2 (tid number,tnamevarchar(20));

表已创建。

SQL> --第一个事务

SQL> insert into transaction_tablevalues(1,'Tom');

已创建 1 行。

SQL> insert into transaction_table values(2,'Mary');

已创建 1 行。

SQL> insert into transaction_tablevalues(3,'Mike');

已创建 1 行。

SQL> commit;

提交完成。

SQL> --第二个事务

SQL> update transaction_table settname='Mary123' where tid=2;

已更新 1 行。

SQL> delete from transaction_table wheretid=3;

已删除 1 行。

SQL> commit;

提交完成。

l闪回事务查询实际上闪回版本查询的一个扩充，通过它可以审计某个事务甚至撤销一个已经提交的事务。

l实现闪回事务查询，需要先了解flashback_transaction_query视图，从该视图中可以获取事务的历史操作记录以及撤销语句(UNDO_SQL)。

l使用闪回事务查询，可以了解某个表的历史操作记录，这个操作记录对应一个撤销SQL语句，如果想要撤销这个操作，就可以执行这个SQL语句。

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语句

注意，要查询flashback_transaction_query视图的信息，需要有select any transaction的权限。

SQL> --如何撤销第二个事务? ----闪回事务查询

SQL> --授权select any transaction给scott

SQL> conn / as sysdba

已连接。

SQL> grant select any transaction toscott;

授权成功。

SQL> conn scott/tiger

已连接。

SQL> /*

SQL> 闪回事务查询的步骤:1

SQL> 1. 通过闪回版本查询获取事务号(xid)

SQL> 2. 通过xid查询Flashback_transaction_query，获取undo_sql

SQL> */

SQL> select tid,tname,versions_operation,versions_starttime,versions_endtime,versions_xid

2 from transaction_table

3 versions between timestampminvalue and maxvalue

4 order by versions_xid;

TID TNAME V VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID

2 Mary123 U 21-9月 -11 11.13.24 上午 0400140020040000

3 Mike D 21-9月 -11 11.13.24 上午 0400140020040000

2 Mary I 21-9月 -11 11.12.39 上午 21-9月 -11 11.13.24 上午 0700250012040000

1 Tom I 21-9月 -11 11.12.39 上午 0700250012040000

3 Mike I 21-9月 -11 11.12.39 上午 21-9月 -11 11.13.24 上午 0700250012040000

SQL> -- 执行闪回事务查询

SQL> select operation,undo_sql

2 fromFlashback_transaction_query

3 where xid='0400140020040000';

OPERATION

--------------------------------

UNDO_SQL

------------------------------------------------------------------------------------------------------------------------------------------------------

DELETE

insert into"SCOTT"."TRANSACTION_TABLE"("TID","TNAME")values ('3','Mike');

UPDATE

update"SCOTT"."TRANSACTION_TABLE" set "TNAME" = 'Mary'where ROWID = 'AAANiBAAEAAAAG3AAB';

BEGIN

SQL> update"SCOTT"."TRANSACTION_TABLE" set "TNAME" = 'Mary'where ROWID = 'AAANiBAAEAAAAG3AAB';

已更新 1 行。

SQL> insert into"SCOTT"."TRANSACTION_TABLE"("TID","TNAME")values ('3','Mike');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from TRANSACTION_TABLE;

TID TNAME

---------- --------------------

1 Tom

2 Mary

3 Mike

SQL> host cls

SQL> spool off

lideguo1979

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

Oracle 之 闪回操作

SQL> */

SQL> select operation,undo_sql

Oracle 之闪回操作