Schema的回滚快照

2019年12月24日 07:48:07Linux大全评论229 views阅读模式

多天前，我们的一个客户不小心在网站上做了一个＂删除＂操作．但他发现他无法确认被他删除的对象是否真的应该被删除．于是求助于我们，希望能看到“删除”操作之前的界面。

我们首先想到的是借助于闪回查询（Flashback Query）。但是，他的这个简单的删除操作实际上在后台数据库当中删除了十多张表的相关数据。而且用于在页面上显示这些对象的代码的逻辑也相当复杂。因此，闪回查询无助于他的要求。我们最终利用expdp的flashback_time参数，将这个schema的数据导出，再导入一个测试环境完成他的请求。

我当时想到，如果Oracle在会话级别提供一个参数flashback_scn/flashback_time控制这个会话的所有查询都闪回到某一个时间点，那这个问题就很容易解决：只要建立一个新的连接，当连上数据库后就修改该参数，就可以查询到该时间点的快照了。

于是我又想，能否找到一个方法，模拟实现出一个schema的闪回快照呢。最终，我找到一个不完善的方法：建立一个新的schema，在该schema当中，为每个源schema的表建立一个视图，在试图中引入闪回查询。并且引入一个“全局变量”来控制视图的闪回时间/scn。

以下就是代码

-- ################################################################################

-- #

-- # $Id: schema_snapshot.sql

-- #

-- # File: $RCSfile: schema_snapshot.sql,v $

-- # Description: create a snapshot for a schema

-- # Usage: sqlplus -s /nolog @schema_snapshot <existing_schema_name> <snapshot_schema_name>

-- # Created: 07/02/2014

-- # Author: Wei Huang

-- # User run as: / as sysdba (OS user should be oracle owner)

-- # Parameters: 1: existing schema name

-- # Parameters: 2: new schema name

-- # Parameters: 3: snapshot timestamp

-- #

-- # History

-- # Modified by When Why

-- # ----------- ------- ----------------------------------------------------

-- ################################################################################

prompt Usage: @schema_snapshot <existing_schema_name> <snapshot_schema_name>

prompt Description: create a snapshot for a schema

prompt

declare

sql_str varchar2(4000);

c number;

begin

select count(1) into c from dba_users where username = upper('&2');

if c = 0 then

execute immediate 'create user &2 identified by &2';

execute immediate 'grant connect,resource to &2';

sql_str := q'[

CREATE OR REPLACE PACKAGE &2.var_pkg IS

var varchar2(255);

PROCEDURE set_var(val varchar2);

function get_var return varchar2;

END var_pkg ;

CREATE OR REPLACE PACKAGE BODY &2.var_pkg IS

PROCEDURE set_var(val varchar2) IS

BEGIN

var := val;

end set_var;

function get_var return varchar2

BEGIN

return var;

END get_var;

END var_pkg;

]';

execute immediate sql_str;

for q in (select 'grant select,flashback on '||owner||'.'||table_name||' to '||upper('&2')||';' str from dba_tables where owner=upper('&1')) loop

execute immediate q.str;

end loop;

for q in (select 'create or replace view '||upper('&2')||'.V_'||table_name||' as select * from '||owner||'.'||table_name||' as of timestamp to_timestamp(var_pkg.get_var,''yyyymmddhh24miss'');' from dba_tables where owner=upper('&1') loop

execute immediate q.str;

end loop;

for q in (select 'create or replace synonym '||upper('&2')||'.'||table_name||' for '||upper('&2')||'.V_'||table_name||';' from dba_tables where owner=upper('&1') loop