/*
|| Oracle 11g Flashback Data Archive Listing
||
|| Demonstrates Oracle Database 11g's Flashback Data Archive (FBDA) features, including:
|| - How to prepare FBDAs for database use
|| - How to add historical table data within FBDAs
|| - How to query historical FBDA data via Flashback Query, Flashback Version, and
||   Flashback Transaction queries
|| - How to manage data within FBDAs, including implementation of "digital shredding"
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various features of Oracle 11gR1
|| Flashback Data Archive features, and they should be carefully proofread before
|| being executed against any existing Oracle database to avoid potential damage!
*/

/*  
|| Listing 1:
|| Preparing a database to use FBDAs
*/

-----
-- Create FBDA administrator user account
-----
DROP USER fbda_admin CASCADE;
CREATE USER fbda_admin 
    IDENTIFIED BY fbda_admin
;
GRANT FLASHBACK ARCHIVE ADMINISTER TO fbda_admin;

-----
-- Grant appropriate Flashback privileges to other user accounts
-----
GRANT FLASHBACK ANY TABLE TO hr;
GRANT EXECUTE ON DBMS_FLASHBACK TO hr;
GRANT FLASHBACK ANY TABLE TO oe;
GRANT EXECUTE ON DBMS_FLASHBACK TO oe;
GRANT FLASHBACK ANY TABLE TO sh;
GRANT EXECUTE ON DBMS_FLASHBACK TO sh;

-----
-- Create a tablespace just for Flashback Data Archives
-----
DROP TABLESPACE fbda INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE fbda
    DATAFILE '/u01/app/oracle/oradata/ORCL/tsp_fdba01.dbf'
    SIZE 24M;

-----
-- Create a Flashback Data Archive that retains just 5 days' worth of history
-----
DROP FLASHBACK ARCHIVE fbda_1;
CREATE FLASHBACK ARCHIVE fbda_1
	TABLESPACE fbda
	QUOTA 1M 
	RETENTION 5 DAY;
    
GRANT FLASHBACK ARCHIVE ON fbda_1 TO hr;
GRANT FLASHBACK ARCHIVE ON fbda_1 TO oe;
GRANT FLASHBACK ARCHIVE ON fbda_1 TO sh;

-----
-- Create a Flashback Data Archive that retains a full year's worth of history
-----
DROP FLASHBACK ARCHIVE fbda_2;
CREATE FLASHBACK ARCHIVE fbda_2
    TABLESPACE fbda
    QUOTA 4M
    RETENTION 1 YEAR;

GRANT FLASHBACK ARCHIVE ON fbda_2 TO hr;
GRANT FLASHBACK ARCHIVE ON fbda_2 TO oe;
GRANT FLASHBACK ARCHIVE ON fbda_2 TO sh;
    
-----
-- Create a Flashback Data Archive that retains seven years worth of history
-----
DROP FLASHBACK ARCHIVE fbda_3;
CREATE FLASHBACK ARCHIVE fbda_3
	TABLESPACE fbda
	QUOTA 20M
	RETENTION 7 YEAR;

GRANT FLASHBACK ARCHIVE ON fbda_3 TO hr;
GRANT FLASHBACK ARCHIVE ON fbda_3 TO oe;
GRANT FLASHBACK ARCHIVE ON fbda_3 TO sh;

/* 
|| Listing 2:
|| Enabling and disabling FBDA for tables
*/

-----
-- Make Flashback Data Archive FBDA_2 the default FBDA
-----
ALTER FLASHBACK ARCHIVE fbda_2 SET DEFAULT;

-----
-- Change FBDA_1 into the default FBDA. Note that FBDA_1 and FBDA_2 will
-- simply exchange designations as the default FBDA
-----
ALTER FLASHBACK ARCHIVE fbda_1 SET DEFAULT;

-----
-- Enabling an existing table to use the default FBDA (fbda_1)
-----
ALTER TABLE hr.applicants FLASHBACK ARCHIVE;

-----
-- Enabling tables to use a specific FBDA
-----
ALTER TABLE hr.departments FLASHBACK ARCHIVE fbda_1;
ALTER TABLE hr.job_history FLASHBACK ARCHIVE fbda_2;
ALTER TABLE oe.customers   FLASHBACK ARCHIVE fbda_3;

-----
-- Disabling Flashback Data Archive for a specific table
-----
ALTER TABLE hr.departments NO FLASHBACK ARCHIVE;

/* 
|| Listing 3:
|| Delving into FBDA historical data
*/

-----
-- Make some changes to table HR.APPLICANTS so that "delta" records 
-- are generated in its corresponding FBDA object
-----

-- Round some Applicants' desired salary to the nearest thousand dollars
UPDATE hr.applicants
   SET salary_desired = ROUND(salary_desired, -3)
 WHERE MOD(applicant_id,5) = 0;

COMMIT;

-- Round some Applicants' desired salary to the nearest hundred dollars
UPDATE hr.applicants
   SET salary_desired = ROUND(salary_desired, -2)
 WHERE MOD(applicant_id,5) <> 0
   AND MOD(applicant_id,3) = 0;

COMMIT;

-----
-- Now flush the contents of the database buffer cache as well as the
-- current UNDO tablespace to insure that a Flashback Versions Query
-- executed against the contents of HR.APPLICANTS returns data from only
-- the corresponding Flashback Data Archive. Note that it's important to
-- wait to see the acknowledgment of the UNDO tablespace switch in the 
-- database's alert log before proceeding!
-----
DROP TABLESPACE smallundo INCLUDING CONTENTS AND DATAFILES;
CREATE UNDO TABLESPACE smallundo
    DATAFILE '/u01/app/oracle/oradata/ORCL/tsp_smallundo01.dbf'
    SIZE 2M;

ALTER SYSTEM CHECKPOINT;
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM SET UNDO_TABLESPACE = 'SMALLUNDO' SCOPE=BOTH;

-----
-- Query to view the contents of the actual FBDA table holding prior transaction
-- values that were originally stored in the UNDO tablespace and were "flushed"
-- to FBDA when that tablespace was switched
-----
TTITLE 'Sample Data from Flashback Data Archives|(From SYS_FBA_HIST_73218)'
COL applicant_id        FORMAT 99999        HEADING 'Appl|ID'
COL endscn              FORMAT 9999999      HEADING 'Ending|SCN'
COL last_name           FORMAT A20          HEADING 'Last Name'
COL gender              FORMAT A2           HEADING 'G|N|D|R'
COL salary_desired      FORMAT 999999.99    HEADING 'Salary|Desired'
COL job_desired         FORMAT A10          HEADING 'Job|Desired'

SELECT
     applicant_id
    ,endscn
    ,last_name
    ,gender
    ,salary_desired
    ,job_desired
  FROM HR.SYS_FBA_HIST_73218
WHERE applicant_id < 10
ORDER BY applicant_id, endscn;
TTITLE OFF

-----
-- Query to view the selected contents of table HR.APPLICANTS as of specific
-- time periods
-----
TTITLE 'Sample Data from HR.APPLICANTS|(Between Time Periods)'
COL applicant_id        FORMAT 99999        HEADING 'Appl|ID'
COL versions_endscn     FORMAT 9999999      HEADING 'Ending|SCN'
COL last_name           FORMAT A20          HEADING 'Last Name'
COL gender              FORMAT A2           HEADING 'G|N|D|R'
COL salary_desired      FORMAT 999999.99    HEADING 'Salary|Desired'
COL job_desired         FORMAT A10          HEADING 'Job|Desired'
SELECT
     applicant_id
    ,VERSIONS_ENDSCN
    ,last_name
    ,gender
    ,salary_desired
    ,job_desired
  FROM hr.applicants
   VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2008-12-04 04:28','yyyy-mm-dd hh24:mi') 
                          AND TO_TIMESTAMP('2008-12-04 04:55','yyyy-mm-dd hh24:mi')
 WHERE APPLICANT_ID < 10
 ORDER BY applicant_id, versions_endscn;
TTITLE OFF


/* 
|| Listing 4:
|| Utilizing FBDA Historical Data for Error Correction
*/

-----
-- An end user is asked to delete any applicants with an application
-- date of November 10, 2008, but then uses an "American" instead of 
-- "European" date format ...
-----
DELETE FROM hr.applicants
 WHERE application_date <= TO_DATE('11-10-2008','dd-mm-yyyy');
 
COMMIT;

-----
-- ... and now, of course, we're expected to recover the deleted 
-- information. Thank goodness for FBDA!
-----
INSERT INTO hr.applicants
SELECT *
  FROM hr.applicants
   VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2008-12-04 10:00','yyyy-mm-dd hh24:mi') 
                          AND MAXVALUE
 WHERE VERSIONS_OPERATION = 'D';

COMMIT;

/* 
|| Listing 5:
|| Maintaining FBDAs
*/

-----
-- Purge any data from an existing FBDA that's more than 1 day old
-----
ALTER FLASHBACK ARCHIVE fbda_1
    PURGE BEFORE TIMESTAMP(SYSTIMESTAMP - INTERVAL '1' DAY);

-----
-- Modify an existing FBDA's space quota, lowering it to only 2MB
-----
ALTER FLASHBACK ARCHIVE fbda_1
    MODIFY TABLESPACE fbda QUOTA 2M;

-----
-- Reduce an existing FBDA's retention period to 90 days
-----
ALTER FLASHBACK ARCHIVE fbda_3
    MODIFY RETENTION 90 DAY;

-----
-- Add a new tablespace to an existing FBDA without specifying any space
-- quota. This will allow the FBDA to use all of the available space in
-- the added tablespace
-----
DROP TABLESPACE fbda_extd INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE fbda_extd
    DATAFILE '/u01/app/oracle/oradata/ORCL/tsp_fdba_extd01.dbf'
    SIZE 16M;

ALTER FLASHBACK ARCHIVE fbda_1
    ADD TABLESPACE fbda_extd;

-----
-- Drop an existing FBDA. Note that the corresponding tablespaces 
-- are still present, however!
-----
DROP FLASHBACK ARCHIVE fbda_1;