Setup FDA
CREATE TABLESPACE fda_data DATAFILE SIZE 1G AUTOEXTEND ON NEXT 1G;
CREATE flashback archive default FDA_1YEAR tablespace fda_data retention 1 year;
exec dbms_flashback_archive.set_context_level(level=> 'ALL');
SELECT
flashback_archive_name,
flashback_archive#,
tablespace_name,
quota_in_mb
FROM
dba_flashback_archive_ts
ORDER BY
flashback_archive_name;
SELECT
owner_name,
flashback_archive_name,
flashback_archive#,
retention_in_days,
TO_CHAR(create_time,'DD-MON-YYYY HH24:MI:SS') AS create_time,
TO_CHAR(last_purge_time,'DD-MON-YYYY HH24:MI:SS') AS last_purge_time,
status
FROM
dba_flashback_archive
ORDER BY
owner_name,
flashback_archive_name;
Add Tables to FDA
BEGIN
DBMS_FLASHBACK_ARCHIVE.register_application(
application_name => 'PARIS_APP',
flashback_archive_name => 'FDA_1YEAR');
END;
/
BEGIN
DBMS_FLASHBACK_ARCHIVE.add_table_to_application (
application_name => 'PARIS_APP',
table_name => 'APP_DIRECTORIES_AND_PARMS',
schema_name => 'OPTIT');
END;
/
SELECT
a.appname,
b.faname
FROM
sys_fba_app a
JOIN sys_fba_fa b ON a.fa# = b.fa#;
SELECT
a.appname,
c.owner AS table_owner,
c.object_name AS table_name
FROM
sys_fba_app a
JOIN sys_fba_app_tables b ON a.app# = b.app#
JOIN dba_objects c ON b.obj# = c.object_id
ORDER BY
1,
2,
3;
Enable FDA by Application
BEGIN
DBMS_FLASHBACK_ARCHIVE.enable_application(
application_name => 'PARIS_APP');
END;
/
SELECT
owner_name,
table_name,
flashback_archive_name,
archive_table_name,
status
FROM
dba_flashback_archive_tables
ORDER BY
owner_name,
table_name;
Purge FDA
alter flashback archive FDA_1YEAR purge ALL;
Disable FDA by Application
BEGIN
DBMS_FLASHBACK_ARCHIVE.disable_application(
application_name => 'PARIS_APP');
END;
/
Grant FDA read access to a user
grant EXECUTE ON DBMS_FLASHBACK_ARCHIVE to USER1;
grant FLASHBACK any TABLE to USER1;
Show all Changes
SELECT
adap.versions_startscn,
adap.versions_starttime,
adap.versions_endscn,
adap.versions_endtime,
adap.versions_xid,
adap.versions_operation,
CASE
WHEN adap.versions_xid IS NOT NULL THEN dbms_flashback_archive.get_sys_context(adap.versions_xid,'USERENV','SESSION_USER')
END
session_user,
CASE
WHEN adap.versions_xid IS NOT NULL THEN dbms_flashback_archive.get_sys_context(adap.versions_xid,'USERENV','HOST')
END
host,
CASE
WHEN adap.versions_xid IS NOT NULL THEN dbms_flashback_archive.get_sys_context(adap.versions_xid,'USERENV','MODULE')
END
module,
CASE
WHEN adap.versions_xid IS NOT NULL THEN DBMS_FLASHBACK_ARCHIVE.get_sys_context(adap.versions_xid,'USERENV','CLIENT_IDENTIFIER')
END
client_identifier,
adap.*
FROM
optit.app_directories_and_parms VERSIONS BETWEEN TIMESTAMP minvalue AND maxvalue adap
WHERE
adap.app_database = 'PARIS'
AND adap.app_alias = 'BIPub'
AND adap.app_dir_parm_no = 4
ORDER BY
adap.versions_startscn NULLS LAST;
