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