/*
|| Script: MonitorFDBA.sql
||
|| Purpose: Contains queries for monitoring Flashback Data Archives
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various features of Oracle 11g
|| Flashback Data Archive, and they should be carefully proofread before
|| executing it against any existing Oracle database to avoid potential damage!
*/

-----
-- What Flashback Data Archives currently exist?
-----
TTITLE 'Current Flashback Data Archives|(From DBA_FLASHBACK_ARCHIVE)'
COL flashback_archive_name  FORMAT A12  	HEADING 'Flashback|Archive'
COL status                  FORMAT A10  	HEADING 'Status'
COL retention_in_days       FORMAT 9999999	HEADING 'Retention|(in Days)'
COL create_dtm              FORMAT A11  	HEADING 'Created On'
COL last_purge_dtm          FORMAT A11  	HEADING 'Last Purged'
SELECT 
     flashback_archive_name
    ,status
    ,retention_in_days
    ,TO_CHAR(create_time, 'mm-dd-yyyy hh24:mi:ss') create_dtm
    ,TO_CHAR(last_purge_time, 'mm-dd-yyyy hh24:mi:ss') last_purge_dtm
  FROM dba_flashback_archive
 ORDER BY 1
;
TTITLE OFF

-----
-- Which tablespaces are used for FDA purposes?
-----
TTITLE 'Corresponding Tablespaces for Flashback Data Archives|(From DBA_FLASHBACK_ARCHIVE_TS)'
COL flashback_archive_name  FORMAT A12  	HEADING 'Flashback|Archive'
COL tablespace_name         FORMAT A15  	HEADING 'Corresponding|Tablespace'
COL quota_in_mb             FORMAT A12		HEADING 'Tablespace|Quota (MB)'
SELECT
     flashback_archive_name
    ,tablespace_name
    ,quota_in_mb
  FROM dba_flashback_archive_ts
 ORDER BY 1
; 
TTITLE OFF

-----
-- What's the name of the corresponding archive table for the 
-- table whose historical data is being retained?
-----
TTITLE 'Tables Using Flashback Data Archives For Historical Retention|(From DBA_FLASHBACK_ARCHIVE)'
COL flashback_archive_name  FORMAT A12  	HEADING 'Flashback|Archive'
COL owner_name              FORMAT A12  	HEADING 'Table|Owner'
COL table_name              FORMAT A20  	HEADING 'Table Name'
COL archive_table_name      FORMAT A30  	HEADING 'FBDA Object Name'
SELECT 
     flashback_archive_name
    ,owner_name
    ,table_name
    ,archive_table_name
  FROM dba_flashback_archive_tables
  ORDER BY 1, 2, 3
;
TTITLE OFF