Oracle Flashback Schema - do-it-yourself

Oracle's Flashback functionality is a really great feature and it comes in all shapes and sizes except "schema". If you are writing a test suite and want exactly the same data conditions per test, it would be fantastic to build the schema and fill it with initial data then run 100 different tests, which always reset to the initial data to the original timestamp. That would be "Flashback Schema", which neither 10g nor 11g have on offer. But hey folks, let's do-it-ourselves!

The following code is a simple implementation of Flashback Schema with some restrictions regarding the kind of objects, which can be flashed:

CREATE OR REPLACE procedure Flashback_Schema( p_flashback2date in DATE ) AS

--

-- Author: Paul Lewin / 20th March 2009

-- Version 1.1 / 25th March 2009

-- Description:  Flashback an Oracle schema to a past point-in-time

-- Field of use: Not for production but for testing and simulation (QA)

--

-- Setup the instance:

--  Set the retention period for the instance before use (in seconds) 86400 = 24 Hrs

--  Make sure the UNDO-Tablespace is large enough to handle all changes for 24 Hrs

--  ALTER SYSTEM SET UNDO_RETENTION = 86400;

--  ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;

--

-- Setup the user (as System):

--  Grant execute on DBMS_FLASHBACK to username;

--  Grant FLASHBACK ANY TABLE to username;

--  Grant execute any procedure to username;

--  Grant SELECT ANY TRANSACTION to username;

--

-- Calling:

--  Use the following call in each schema to reset the data to a specific point-in-time

--  EXEC Flashback_Schema_To_Timestamp( TO_DATE('18.03.2009 10:50','DD.MM.YYYY HH24:MI') );

--

-- Restrictions:

--  This will not work on schemas, which include materialized views.

--  Truncating tables will make this functionality in-operable.

--  Using alter table or tablespace commands will also make this functionality in-operable.

 

-- Cursor builds a list of execute immediate commands to enable row movement

CURSOR c_cmd_move_enable is

  select

    'alter table '||table_name||' enable row movement'

  from

    user_tables

  where

    temporary='N'

  and

    row_movement='DISABLED';

 

-- Cursor builds a list of execute immediate commands to revoke previously enabled row movement

CURSOR c_cmd_move_disable is

  select

    'alter table '||table_name||' disable row movement'

  from

    user_tables

  where

    temporary='N'

  and

    row_movement='DISABLED';

 

-- Get a list of tables in the current schema

CURSOR c_cmd_flashback_list is

  select

    table_name

  from

    user_tables

  where

    temporary='N'

  and

    table_name not in (select mview_name from user_mviews)

  Order By

    table_name;

 

v_cmd varchar2(250 char);

v_cmd_flashback varchar2(32000 char) := 'flashback table ';

v_flashback_date date;

v_table_cnt number := 0;

v_reset_enable := 0;

 

Begin

               /* Default flashback by 1 hour */

  if p_flashback2date is NULL

  then

    v_flashback_date := sysdate - (1/24); /* flashback 1 hour */

  else

    v_flashback_date := p_flashback2date;

  end if;

 

  Open c_cmd_move_enable;

  Open c_cmd_move_disable;

  Open c_cmd_flashback_list;

 

  /* All tables must have row movement allowed! */

  loop

    FETCH c_cmd_move_enable into v_cmd;

    EXIT WHEN c_cmd_move_enable%NOTFOUND;

--  dbms_output.put_line( 'EXECUTE IMMEDIATE ' || v_cmd );

    EXECUTE IMMEDIATE v_cmd;

    v_reset_enable := 1;

  end loop;

 

  /* Get a list of tables in this schema */

  /* in a comma separated list with 6 names per line */

  loop

    FETCH c_cmd_flashback_list into v_cmd;

    EXIT WHEN c_cmd_flashback_list%NOTFOUND;

   

    if v_table_cnt > 0

    then

                              v_cmd_flashback := v_cmd_flashback || ', ' || v_cmd;

      if mod(v_table_cnt,6) = 0

      then

                              v_cmd_flashback := v_cmd_flashback || chr(13);

      end if;

    else

                              v_cmd_flashback := v_cmd_flashback || v_cmd; -- first table doesn't need a comma

    end if;

   

    v_table_cnt := v_table_cnt + 1;

  end loop;

  v_cmd_flashback := v_cmd_flashback || ' TO TIMESTAMP :1';

                             

  /* Flashback all tables in one transaction */

--dbms_output.put_line( 'EXECUTE IMMEDIATE ' || v_cmd_flashback || ' using parameter ' || to_char(v_flashback_date, 'DD.MM.YYYY HH24:MI') );

  EXECUTE IMMEDIATE v_cmd_flashback using v_flashback_date;

 

  /* restore row movement to all tables which previously had row movement allowed */

  loop

    FETCH c_cmd_move_disable into v_cmd;

    EXIT WHEN c_cmd_move_disable%NOTFOUND;

    v_reset_enable := 0;

--  dbms_output.put_line( 'EXECUTE IMMEDIATE ' || v_cmd );

    EXECUTE IMMEDIATE v_cmd;

  end loop;

 

  Close c_cmd_flashback_list;

  Close c_cmd_move_disable;

  Close c_cmd_move_enable;

 

EXCEPTION

    WHEN OTHERS THEN

        IF c_cmd_flashback_list%ISOPEN

        THEN

            CLOSE c_cmd_flashback_list;

        END IF;

        IF c_cmd_move_disable%ISOPEN

        THEN

            CLOSE c_cmd_move_disable;

        END IF;

        IF c_cmd_move_enable%ISOPEN

        THEN

            IF v_reset_enable = 1

            THEN

               /* restore row movement to all tables which previously had row movement allowed */

              loop

                FETCH c_cmd_move_disable into v_cmd;

                EXIT WHEN c_cmd_move_disable%NOTFOUND;

             

--              dbms_output.put_line( 'EXECUTE IMMEDIATE ' || v_cmd );

                EXECUTE IMMEDIATE v_cmd;

              end loop;

            END IF;

            CLOSE c_cmd_move_enable;

        END IF;

        RAISE;

End;

/