A flashback data archive consists of multiple tablespaces and stores historic data from all transactions against tracked tables. The data is stored on the internal history tables. It provides long-term storage of undo data, allowing undo-based flashback operations to be performed over an extended period. To maintain the strict protection on the internal historical table and maintain the historical data for short and long periods, Flash Data Archive is an option that helps recall without restoring the historical backup. <\/strong><\/p>\n Required Privileges to execute<\/strong><\/p>\n Context information is stored with the transaction data, you need to use the DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL procedure, passing one of the following parameter values.<\/p>\n In this case use ALL, to capture the USERENV and custom context values.<\/p>\n `CONN sys@surya AS SYSDBA`<\/p>\n `EXEC DBMS_FLASHBACK_ARCHIVE.set_context_level(‘ALL’);`<\/p>\n Testing And Implementation<\/strong><\/p>\n In the following example, I am enabling the FDA at tablespace level, and setting the retention for specific period for multiple tablespaces. Also retrieving data from flashback data archive which has been deleted between the retention period. The intent is to get the historical data easily with FAD. Without enabling this feature, we have to restore the entire database to get the historical data. Something that adds to complexity is case of a large database system.<\/p>\n Example- Command for creating a tablespace<\/strong><\/p>\n `SQL> CREATE TABLESPACE FBA DATAFILE size 500m autoextend on next 100m ;`<\/p>\n Tablespace created.<\/p>\n \u2022 Run the following command for creating a default flashback data archive (FDA)<\/p>\n `SQL> CREATE FLASHBACK ARCHIVE DEFAULT FLA1 TABLESPACE FBA QUOTA 500M RETENTION 1 YEAR;`<\/p>\n *Flashback archive created.*<\/p>\n \u2022 Complete the following step for Creating Non-Default FDA<\/p>\n `SQL> CREATE FLASHBACK ARCHIVE FLA2 TABLESPACE users QUOTA 400M RETENTION 6 MONTH;`<\/p>\n *Flashback archive created.*<\/p>\n \u2022 Get the list of FDAs created <\/p>\n SELECT owner_name, FROM dba_flashback_archive<\/p>\n ORDER BY owner_name, flashback_archive_name;<\/p>\n OWNER_NAME FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME LAST_PURGE_TIME STATUS \n\u2022 Setting Default FDA and get the details <\/p>\n `SQL> ALTER FLASHBACK ARCHIVE FLA1 SET DEFAULT ;`<\/p>\n Flashback archive altered.<\/p>\n \n`SELECT flashback_archive_name,` FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB FLA2 2 USERS 400<\/p>\n \nSQL> `SELECT *` \nTABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS ### Perform the following test and to see how it works Session altered.<\/p>\n SQL> SYSDATE SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;<\/p>\n \nGET_SYSTEM_CHANGE_NUMBER #### Let us try to delete record and update on employees table.<\/p>\n \nSQL> `DELETE FROM HR.EMPLOYEES WHERE EMPLOYEE_ID=192;`<\/p>\n 1 row deleted.<\/p>\n SQL> commit;<\/p>\n Commit complete.<\/p>\n SQL> COMMIT;<\/p>\n `UPDATE HR.EMPLOYEES SET SALARY=12500 WHERE EMPLOYEE_ID=168;`<\/p>\n COMMIT;<\/p>\n `UPDATE HR.EMPLOYEES SET SALARY=12550 WHERE EMPLOYEE_ID=168;`<\/p>\n COMMIT; SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL><\/p>\n Commit complete.<\/p>\n – Follow the steps for comparing data using FDA <\/p>\n SQL> `SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME` EMPLOYEE_ID FIRST_NAME LAST_NAME Here you can see the deleted row from FDA. You can also get the data with VERSION_STARTSCN pseudcoumns.<\/p>\n #### \u2022 Data with given SCN<\/p>\n SQL> `COL VERSIONS_STARTTIME FORMAT A40` `WHERE`<\/p>\n ` EMPLOYEE_ID=168; `<\/p>\n VERSIONS_STARTTIME VERSIONS_STARTSCN FIRST_NAME LAST_NAME SALARY You can see the row with different version for same data in salary column as we have done update statement on salary column with 500 value difference.<\/p>\n \n### Restriction and Workaround for DDL statement (capture the evolution of a recording)<\/p>\n **Disassociate \/ Associate**<\/p>\n For more complex DDL \u2013 upgrades, split table, etc. \u2013 the Disassociate and Associate PL\/SQL procedures can be used to temporarily disable Flashback Data Archive on specified tables. The Associate procedure enforces schema integrity after association: the base table and history table schemas must be the same. The Disassociate and Associate procedures require the FLASHBACK ARCHIVE ADMINISTER privilege.<\/p>\n \n#### In the following example you can see how we can perform a DDL activity on FDA tables for historical data. Creating a demo table EMPLOYEES_FBA adding constraints.<\/p>\n SQL> `CREATE TABLE HR.EMPLOYEES_FBA AS SELECT * FROM HR.EMPLOYEES;`<\/p>\n Table created.<\/p>\n SQL> `ALTER TABLE HR.EMPLOYEES_FBA ADD CONSTRAINT employee_pk PRIMARY KEY (employee_id);`<\/p>\n Table altered. SQL> `ALTER TABLE HR.EMPLOYEES_FBA FLASHBACK ARCHIVE;`<\/p>\n Table altered.<\/p>\n SQL> `UPDATE HR.EMPLOYEES_FBA SET SALARY=10000 WHERE EMPLOYEE_ID=203;`<\/p>\n 1 row updated.<\/p>\n `COMMIT;`<\/p>\n Commit complete.<\/p>\n #### When disabling and enabling the table constraints, the ORA-55610 prompt prevents the historical track data table<\/p>\n SQL> `ALTER TABLE HR.EMPLOYEES_FBA DISABLE CONSTRAINT EMPLOYEE_PK;`<\/p>\n \nTable altered.<\/p>\n SQL> SQL> `ALTER TABLE HR.EMPLOYEES_FBA ENABLE CONSTRAINT EMPLOYEE_PK;` \n#### Now how to proceed when we are getting these restrictions<\/p>\n *Note: Addition of any constraint on a table (Primary Key, Unique Key, Foreign Key or Check Constraint) will cause you to be unable to automatically read the historical data, without directly accessing the underlining SYS_FBA_ archive tables.* SQL> `SELECT * FROM DBA_FLASHBACK_ARCHIVE_TABLES WHERE TABLE_NAME=’EMPLOYEES_FBA’;`<\/p>\n \nTABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS \n#### With the help of DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA we can achieve this.<\/p>\n `SQL> EXEC DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA(‘HR’,’EMPLOYEES_FBA’);`<\/p>\n PL\/SQL procedure successfully completed.<\/p>\n #### Now again try to enable the constraints<\/p>\n SQL> `ALTER TABLE HR.EMPLOYEES_FBA ENABLE CONSTRAINT EMPLOYEE_PK;`<\/p>\n Table altered.<\/p>\n #### Now reenable the restriction with the help of DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA<\/p>\n SQL> `EXEC DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA(‘HR’,’EMPLOYEES_FBA’);`<\/p>\n PL\/SQL procedure successfully completed.<\/p>\n **Purging the historical data for a specific time.** Flashback archive altered.<\/p>\n #### Disable FDA SQL> `ALTER TABLE HR.EMPLOYEES_FBA NO FLASHBACK ARCHIVE;`<\/p>\n #### Dropping FDA<\/p>\n SQL> `DROP FLASHBACK ARCHIVE FLA1;` Conclusion <\/strong><\/p>\n The Flashback Data Archive feature provides a centralized and integrated interface to manage and retain data history, and automated, policy-based management that provides the dramatic solution for database administration where tracking of this historical data to comply with new regulations or adapt to changing business needs.<\/p>\n","protected":false},"excerpt":{"rendered":" Total Recall (Flashback Data Archive)<\/p>\n","protected":false},"author":2,"featured_media":7040,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26147","content_type":"blog","created_by":"10130","authored_on":"2023-10-12T12:01:24+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Rackspace Technology Staff","seo_description":"Importance of data purging for database performance. Maintain health and efficiency of databases with data purging.\r\n","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26147-insights_image-database-technical-blog-2-insights.jpg","field_partners":"","rxt_platforms":"AWS Data","product":"","publisheddate":"2022-03-18T12:01:24","rxt_solutions":"Multicloud","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"Total Recall (Flashback Data Archive) | Rackspace Technology","field_topics":"","field_slug":"\/total-recall-flashback-data-archive","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-7038","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs\/7038","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/comments?post=7038"}],"version-history":[{"count":2,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs\/7038\/revisions"}],"predecessor-version":[{"id":8765,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs\/7038\/revisions\/8765"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/media\/7040"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/media?parent=7038"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/categories?post=7038"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/tags?post=7038"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}![\"Flashback](\"\/wp-content\/uploads\/2026\/blog_article_images\/26147-body-Picture1_0.png\")
<\/p>\n\n
\n
\n flashback_archive_name,
\n
\n flashback_archive#,
\n
\n retention_in_days,
\n
\n TO_CHAR(create_time, ‘DD-MON-YYYY HH24:MI:SS’) AS create_time,
\n
\n TO_CHAR(last_purge_time, ‘DD-MON-YYYY HH24:MI:SS’) AS last_purge_time,
\n status<\/p>\n
\n———- ———————- —————— —————– ——————– ——————– ——-
\nSYS FLA1 1 365 16-DEC-2021 19:28:53 16-DEC-2021 19:28:53 DEFAULT
\nSYS FLA2 2 180 16-DEC-2021 19:29:14 16-DEC-2021 19:29:14<\/p>\n
\n `flashback_archive#,`
\n `tablespace_name,`
\n `quota_in_mb`
\nFROM `dba_flashback_archive_ts`
\nORDER BY `flashback_archive_name; ` <\/p>\n
\n———————- —————— ————— ———–
\nFLA1 1 FBA 500<\/p>\n
\n` FROM DBA_FLASHBACK_ARCHIVE_TABLEs`
\n` WHERE TABLE_NAME=’EMPLOYEES’`
\n `AND OWNER_NAME=’HR’ ;`<\/p>\n
\n————– ———- ———————- ———————– ————-
\nEMPLOYEES HR FLA1 SYS_FBA_HIST_92593 ENABLED<\/p>\n
\nSQL> ALTER SESSION SET NLS_DATE_FORMAT=’YYYY\/MM\/DD HH24:MI:SS’;<\/p>\n
\nSQL> SELECT SYSDATE FROM DUA;<\/p>\n
\n——————-
\n2021\/12\/16 19:39:31<\/p>\n
\n————————
\n 1964623<\/p>\n
\nSQL>
\nSQL>
\nSQL> `UPDATE HR.EMPLOYEES SET SALARY=12000 WHERE EMPLOYEE_ID=168;`<\/p>\n
\n1 row updated.<\/p>\n
\nCommit complete.<\/p>\n
\n1 row updated.<\/p>\n
\nCommit complete.<\/p>\n
\n1 row updated.<\/p>\n
\n `FROM HR.EMPLOYEES`
\n `AS OF TIMESTAMP TO_TIMESTAMP(‘2021\/12\/16 19:39:31′,’YYYY\/MM\/DD HH24:MI:SS’)`
\n`MINUS`
\n`SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME`
\n `FROM HR.EMPLOYEES; `<\/p>\n
\n———– ——————– ————————-
\n 192 Sarah Bell<\/p>\n
\n`SELECT VERSIONS_STARTTIME,`
\n ` VERSIONS_STARTSCN,`
\n `FIRST_NAME,`
\n `LAST_NAME,`
\n `SALARY`
\n `FROM HR.EMPLOYEES VERSIONS BETWEEN TIMESTAMP`
\n` TO_TIMESTAMP(‘2021\/12\/16 19:39:31′,’YYYY\/MM\/DD HH24:MI:SS’) AND`
\n `SYSTIMESTAMP`<\/p>\n
\n——————————– —————– ———- ——— ——
\n16-DEC-21 07.40.08.000000000 PM 1964648 Lisa Ozer 12500
\n16-DEC-21 07.40.08.000000000 PM 1964646 Lisa Ozer 12000
\n Lisa Ozer 11500
\n16-DEC-21 07.40.08.000000000 PM 1964650 Lisa Ozer 12550<\/p>\n\n
\n
\n#### Enable FDA on demo tables and updating some records.<\/p>\n
\n`ALTER TABLE HR.EMPLOYEES_FBA ENABLE CONSTRAINT EMPLOYEE_PK`
\n*
\nERROR at line 1:
\nORA-55610: Invalid DDL statement on history-tracked table<\/p>\n
\n*You need to be very careful with constraint management and the historical tracking of tables*<\/p>\n
\n————– ———- ———————- ———————– ————-
\nEMPLOYEES_FBA HR FLA1 SYS_FBA_HIST_93946 ENABLED<\/p>\n
\nSQL> `ALTER FLASHBACK ARCHIVE FLA1 PURGE BEFORE TIMESTAMP (SYSTIMESTAMP – INTERVAL ‘1’ DAY);`<\/p>\n
\nSQL> `ALTER TABLE HR.EMPLOYEES NO FLASHBACK ARCHIVE;`<\/p>\n
\n <\/p>\n