Flashback Snapshot of Schema

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2015-11-03 21:41:06

Share to 

Few days ago, one of my clients performed a "drop" action occidentally on the web site. But he could not confirm what he dropped is correct or not. He want to see the screen before he dropped the items.  The first thought we got is to utilize flashback query. However, this simple action actually deleted important data from many tables (over 10), and the code of displaying the i is fairly complex. Flashback query doesn't help in this case. We finally exported entire schema using expdp with flashback_time option, and imported it to a test environment. I ever thought if oracle provided a session parameter flashback_scn/flashback_time to allow the user flashback query all of data to a specified scn/timestamp in the session, it will make things simple. What we need to do is to create a new connection, and change this parameter after connected to database. Then I was thinking can I find a workaround way? I finally got one. I can build a new schema, then create a set of views referring to the existing schema, involving the flashback query feature. And I can use a "global parameter" to control the flashback scn/timestamp. Here is the code. SQL代码 -- ################################################################################   -- #   -- #         $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   -- #   -- #  Copyright (c) 2014 Wei Huang   -- #   -- # 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     IS    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         execute immediate q.str;       end loop;          &2.var_pkg.set_var('&3');     end if;   end;   /   This will generate the code to create a "snapshot schema". Clients connecting this schema will query all the data before the specified time. Of course, if there are procedures/views in the existing schema, they should be created in the new schema referring to those synonyms.