Demonstration
Create a flashback administrator and a flashback user
connect &syspw@&db as sysdba
create user fba_admin
identified by fba_admin
quota unlimited on users
;
grant
flashback archive administer,
-- create tablespace,
-- drop tablespace,
connect
to
fba_admin;
grant
execute on dbms_flashback_archive
to
fba_admin;
create user fba_user
identified by fba_user
quota unlimited on users;
grant
connect,
create table,
create procedure,
create any context
to
fba_user;
grant
execute on dbms_flashback -- so that fba_user can execute _commit.sql
to
fba_user
;
Create a dedicated tablespace
A dedicataed
tablespace for the flashback archive is created.
It is assmed that
db_create_file_dest is set, for example with
alter system set db_create_file_dest = '/opt/oracle/oradata' scope=both;.
create tablespace tq84_fba_ts datafile size 4g autoextend off;
--
-- Prevent ORA-01950: The user FBA_ADMIN has insufficient quota on tablespace TQ84_FBA_TS.
-- when FBA_ADMIN executes CREATE FLASHBACK ARCHIVE using the new tablespace:
--
alter user fba_admin quota unlimited on tq84_fba_ts;
--
-- The user also needs a quota on the tablespace
--
alter user fba_user quota unlimited on tq84_fba_ts;
Create a flashback archive
First we need to create a flashback archive. The archive resides in a
tablespace which needs to be named (here:
data) when the flashback archive is created:
connect fba_admin/fba_admin@&db
create flashback archive
-- default -- Make this flashback archive the default archive / must be logged in as sysdba for this option (ORA-55611)
tq84_fba -- Name of the archive
-- tablespace users -- Name of the tablespace where archive will reside
tablespace tq84_fba_ts --(User executing statement needs some quota on tablespace, otherwise ORA-01950)
quota 4g -- 4 GB is more than sufficient for this demo.
retention 1 year -- FDA guarantees to store data for 1 year
-- optimize data -- License for advanced compression required!
;
grant flashback archive on tq84_fba to fba_user;
Create tables
We also need some tables for the demonstration. With the flashback archive … clause, we could put the tables into the archive as they're created, but for this demonstration, we also create («register») an application, see next step.
connect fba_user/fba_user@&db
create table tq84_config_opt (
nam varchar2(20),
id integer,
--
constraint tq84_config_pk primary key (id)
)
-- flashback archive tq84_fba -- Commented because tables are added using 'application'
;
create table tq84_config_val (
val number(7,2) not null,
opt_id not null,
--
constraint tq84_config_val_pk primary key (opt_id),
constraint tq84_config_val_fk foreign key (opt_id) references tq84_config_opt
)
-- flashback archive tq84_fba -- Commented because tables are added using 'application'
;
Register an application
It provides an umbrella to manage all (i. e. in this demo: two) tables together:
connect fba_admin/fba_admin@&db
begin
sys.dbms_flashback_archive.register_application(
application_name => 'tq84_fba_demo_app',
flashback_archive_name => 'tq84_fba'
);
end;
/
The registered application is stored in SYS.SYS_FBA_APP.
Add tables to application
The tables are added to the application.
begin
dbms_flashback_archive.add_table_to_application(
application_name => 'TQ84_FBA_DEMO_APP', -- must be uppercase
table_name => 'TQ84_CONFIG_OPT',
schema_name => 'FBA_USER'
);
dbms_flashback_archive.add_table_to_application(
application_name => 'TQ84_FBA_DEMO_APP',
table_name => 'TQ84_CONFIG_VAL',
schema_name => 'FBA_USER'
);
end;
/
The added tables can be found in SYS.SYS_FBA_APP_TABLES (but not yet in dba_flashback_archive_tables.
select
tab.name tabname,
app.appname
from
sys.sys_fba_app app join
sys.sys_fba_app_tables a2t on app.app# = a2t.app# join
sys.obj$ tab on a2t.obj# = tab.obj#
;
Enable application
By enabling the application, the tables become subject to flashback queries:
begin
dbms_flashback_archive.enable_application('TQ84_FBA_DEMO_APP');
end;
/
With this step,
- the tables are now found in
dba_flashback_archive_tables.
- objects such as
SYS_FBA_DDL_COLMAP_n, SYS_FBA_TCRV_IDX1_n and SYS_FBA_HIST_n are created
Wait
In some cases, I had to increase this duration, then again, sometimes it works by just using 2 seconds.
I was not able to figure out the mechanics behind this observerion, let alone why it was necessary.
argument 1 prompt "Sleep duration in seconds"
@_blue-text "sleeping for &1 seconds."
begin
--
-- Taking a nap seems necessary to prevent
-- ORA-08186: invalid timestamp specified
-- when using the versions timestamp timestamp '…' clause.
--
dbms_session.sleep(&1);
end;
/
Context package
A nice feature of flashback archive is that it also tracks the values of context variables. The following package and context allows to set such values:
connect fba_user/fba_user@&db
create or replace package tq84_fba_ctx_pkg authid definer as
procedure ctx_val(nam in varchar2, val in varchar2);
end tq84_fba_ctx_pkg;
/
create or replace package body tq84_fba_ctx_pkg as
procedure ctx_val (nam in varchar2, val in varchar2) as begin
dbms_session.set_context('tq84_fba_ctx', lower(nam), val);
end ctx_val;
end tq84_fba_ctx_pkg;
/
create or replace context tq84_fba_ctx using tq84_fba_ctx_pkg;
_commit.sql
The value of these variables is later used when querying the data for a given timestamp.
Because the time resolution of timestamps
is three seconds when using flashback queries, the script also sleeps for a certain amount when commiting. In addition, it adds six seconds to the timestamp of the commit because it turned out that this is the value actually needed when doing
as of timestamp flashback queries:
argument 1 prompt "commit id"
@_blue-text "Commit (&1) and determine timestamp and scn"
column scn new_value scn_&1
column ts new_value ts_&1
set termout off
select
to_char(systimestamp + interval '6' second, 'yyyy-mm-dd hh24:mi:ss') ts,
dbms_flashback.get_system_change_number scn
from
dual;
set termout on
begin
commit;
dbms_session.sleep(6);
end;
/
Set context level
We set the context level to ALL in order to track all context values:
connect fba_admin/fba_admin@&db
begin
dbms_flashback_archive.set_context_level('ALL' );
-- dbms_flashback_archive.set_context_level('NONE');
end;
/
Set a context value
We also set the value of the context name ctx-1 to yes:
connect fba_user/fba_user@&db
begin
tq84_fba_ctx_pkg.ctx_val('ctx-1', 'yes');
end;
/
Initial insert
The initial values for the tables. Note the typo (Penalyt rather than Penalty):
begin
insert into tq84_config_opt (nam, id) values ('Interest Rate' , 1);
insert into tq84_config_opt (nam, id) values ('Credit Limit' , 2);
insert into tq84_config_opt (nam, id) values ('Debit Limit' , 3);
insert into tq84_config_opt (nam, id) values ('Transaction Fee' , 4);
insert into tq84_config_opt (nam, id) values ('Wire Transfer Fee' , 5);
insert into tq84_config_opt (nam, id) values ('Overdraft Penalyt' , 6); -- Note the typo
insert into tq84_config_opt (nam, id) values ('ACH Transfer Limit' , 7);
insert into tq84_config_val (val, opt_id) values ( 3.50, 1);
insert into tq84_config_val (val, opt_id) values ( 5000.00, 2);
insert into tq84_config_val (val, opt_id) values ( 2000.00, 3);
insert into tq84_config_val (val, opt_id) values ( 2.50, 4);
insert into tq84_config_val (val, opt_id) values ( 15.00, 5);
insert into tq84_config_val (val, opt_id) values ( 35.00, 6);
insert into tq84_config_val (val, opt_id) values (10000.00, 7);
end;
/
@ _commit init
Change the context value
begin
tq84_fba_ctx_pkg.ctx_val('ctx-1', 'no');
end;
/
Fix typo
The typo (Penalyt) is fixed:
begin
tq84_fba_ctx_pkg.ctx_val('change reason', 'fix typo');
update tq84_config_opt set nam = 'Overdraft Penalty' where id = 6;
end;
/
@ _commit fix_typo
Decrease debit limit
The debit limit is decreased from 2000 to 1500:
begin
tq84_fba_ctx_pkg.ctx_val('change reason', 'Decrease debit limit');
update tq84_config_val set val = 1500.00 where opt_id = 3;
end;
/
@ _commit decrease_debit_limit
Wait 5 minutes
Oracle documentation (again under Flashback Time Travel Restrictions) also recommends to wait 5 minutes before using Flashback query on the table:
begin
dbms_session.sleep(5*60);
end;
/
Queries
The following queries provide an idea how the tables might be queried:
select
opt.nam,
val.val
from
tq84_config_opt opt join
tq84_config_val val on opt.id = val.opt_id
;
@_blue-text "select all versions in tq84_config_val"
select
val.*,
val.versions_starttime,
val.versions_endtime,
val.versions_operation,
val.versions_startscn,
val.versions_endscn,
val.versions_xid
from
tq84_config_val versions between scn minvalue and maxvalue val
;
@_blue-text "select all versions in tq84_config_opt"
select
opt.*,
opt.versions_starttime,
opt.versions_endtime,
opt.versions_operation,
opt.versions_startscn,
opt.versions_endscn,
opt.versions_xid
from
tq84_config_opt versions between scn minvalue and maxvalue opt
;
@_blue-text "select data as of ts_init (&ts_init)"
prompt @|cyan,intensity_bold Note the typo Penalyt|@
select
opt.nam,
val.val
from
tq84_config_opt as of timestamp timestamp '&ts_init' opt join
tq84_config_val as of timestamp timestamp '&ts_init' val on opt.id = val.opt_id
;
@_blue-text "select data as of ts_fix_typo (&ts_fix_typo)"
prompt @|cyan,intensity_bold The typo should be fixed (Penalyt -> Penalty), but Debit Limit is still 2000|@
select
opt.nam,
val.val
from
tq84_config_opt as of timestamp timestamp '&ts_fix_typo' opt join
tq84_config_val as of timestamp timestamp '&ts_fix_typo' val on opt.id = val.opt_id
;
@_blue-text "select data as of ts_decrease_debit_limit (&ts_decrease_debit_limit)"
prompt @|cyan,intensity_bold Debit Limit is now decreased to 1500|@
select
opt.nam,
val.val
from
tq84_config_opt as of timestamp timestamp '&ts_decrease_debit_limit' opt join
tq84_config_val as of timestamp timestamp '&ts_decrease_debit_limit' val on opt.id = val.opt_id
;
-- @_blue-text "select change reason context value"
--
-- select
-- val.*,
-- dbms_flashback_archive.get_sys_context(val.versions_xid, 'tq84_fba_ctx','change reason') AS change_reason
-- from
-- tq84_config_opt versions between scn minvalue and maxvalue val
-- ;
Execution plan (regular)
explain plan for
select *
from
tq84_config_val;
select * from table(dbms_xplan.display(format => 'basic'));
--
-- ---------------------------------------------
-- | Id | Operation | Name |
-- ---------------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | TABLE ACCESS FULL| TQ84_CONFIG_VAL |
-- ---------------------------------------------
Execution plan (as of query)
explain plan for
select *
from
tq84_config_val as of timestamp systimestamp - interval '5' minute;
select * from table(dbms_xplan.display(format => 'basic'));
--
-- ----------------------------------------------------------------------------
-- | Id | Operation | Name |
-- ----------------------------------------------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | VIEW | |
-- | 2 | UNION-ALL | |
-- | 3 | FILTER | |
-- | 4 | PARTITION RANGE SINGLE | |
-- | 5 | TABLE ACCESS FULL | SYS_FBA_HIST_268912 |
-- | 6 | FILTER | |
-- | 7 | NESTED LOOPS OUTER | |
-- | 8 | TABLE ACCESS FULL | TQ84_CONFIG_VAL |
-- | 9 | TABLE ACCESS BY INDEX ROWID BATCHED| SYS_FBA_TCRV_268912 |
-- | 10 | INDEX RANGE SCAN | SYS_FBA_TCRV_IDX1_268912 |
-- ----------------------------------------------------------------------------
Special test: Add column
It possible to add columns to version enabled tables.
Even though the history contains null values in the added columns, a not null constraint can be put on the column as soon as it is populated in the current version:
alter table tq84_config_opt add category varchar2(10);
update tq84_config_opt set category = case
when nam like '%Limit%' then 'limit'
when nam like '%Fee%' then 'fee'
when nam like '%Penalty%' then 'fee'
else '?' end;
@_commit added_column
alter table tq84_config_opt modify category not null;
Cleanup
Drop the application, the flashback archives and the tables that were used for this demonstration:
connect fba_admin/fba_admin@&db
begin
dbms_flashback_archive.remove_table_from_application('TQ84_FBA_DEMO_APP', 'TQ84_CONFIG_OPT', 'FBA_USER');
dbms_flashback_archive.remove_table_from_application('TQ84_FBA_DEMO_APP', 'TQ84_CONFIG_VAL', 'FBA_USER');
end;
/
declare
cnt integer;
begin
dbms_flashback_archive.drop_application('TQ84_FBA_DEMO_APP');
end;
/
drop flashback archive tq84_fba;
connect fba_user/fba_user@&db
drop table tq84_config_val;
drop table tq84_config_opt;
connect &syspw@&db as sysdba
begin
--
-- Does this sleep prevent the following errors?
-- ORA-00604: Error occurred at recursive SQL level 1. Check subsequent errors.
-- ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "FBA_USER"."SYS_FBA_TCRV_98060"
dbms_session.sleep(5);
end;
/
drop tablespace tq84_fba_ts including contents;
drop user fba_user cascade;
drop user fba_admin cascade;