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;
/
