Oracle Flashback Time Travel (formerly known as Flashback Data Archive) is a crucial member of Oracle Flashback Technology that allows archiving historical changes to designated tables automatically and transparently. It is designed to simplify the process of tracking and managing historical data within a database, making it easier to comply with regulatory requirements and perform historical data analysis. I’m glad to announce that this long-awaited feature is now available in Autonomous Database Serverless (ADB-S). In this blog post, we are going to review how we can use this feature in ADB-S from enabling it for a given table to running AS OF queries and changing the default archive retention period. Before we move onto our demonstration for this blog, I’d like to emphasize the main use cases and benefits of this feature in a bit more detail:
- Transparent Data Archiving: Flashback Time Travel operates transparently in the background, meaning that users can continue to work with the tables as they normally would without having to manage the archiving process manually. The archived data is stored separately and remains easily accessible.
- Retention of Historical Changes: Flashback Time Travel tracks all the changes made to a flashback archive enabled table’s rows, including inserts, updates, and deletes. These changes are stored in the archive along with the timestamp of each change.
- Simple Data Retrieval: You can easily access historical data using standard SQL queries. By specifying a timestamp or a time range, you can retrieve the data as it existed at those points in the past.
- Compliance and Auditing: Flashback Time Travel enables organizations to meet regulatory compliance requirements by preserving historical data in a tamper-proof manner. This is particularly valuable in industries with strict data retention policies, such as finance or healthcare.
- Data Analysis and Reporting: The ability to access historical data allows for deeper data analysis and trend analysis. Organizations can gain insights into historical patterns and make data-driven decisions based on long-term trends.
- Retention Policies: Organizations can define retention policies that specify how long data should be retained in the archive. Once data reaches its retention limit, it can be automatically purged or marked for deletion.
- Granular Access Control: Flashback Data Archive provides fine-grained access control, allowing administrators to control who can access the archived data and who can manage the archiving feature.
Now that we know a bit more about the core strengths of Flashback Time Travel, we can get started with our use case for this blog post. As mentioned earlier, we’ll be performing the following operations:
- Create a flashback archive enabled table
- Run a Query to Access Historical Data, and
- Change the retention period of the flashback archive
Create a Flashback Archive Enabled Table
Flashback Time Travel is available in every ADB-S instance by default. In other words, each ADB-S instance comes with a predefined flashback archive called flashback_archive so you don’t have to worry about creating one or managing tablespaces for it. However, this doesn’t mean that each user table is automatically enabled for flashback archive. If you want to use Flashback Time Travel for any user table, those tables must be enabled for flashback archive either during or after table creation. In our example below, we are going to be creating a table called EMP and enable the flashback archive during table creation:
create table emp ( empno number(4) constraint pk_emp primary key, ename varchar2(10), job varchar2(9), mgr number(4), hiredate date, sal number(7,2), comm number(7,2), deptno number(2) ) FLASHBACK ARCHIVE;
Run a Query to Access Historical Data
To demonstrate how we can access historical data using this feature, we are going to be inserting a set of rows to our table at two different times (e.g. t1 and t2). For our use case, we are only interested in the rows that are inserted after t1. In order to find out what those rows are, we will be running an AS OF query.
- Insert the first set of row at time t1:
insert into emp values (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
insert into emp values (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
insert into emp values (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
insert into emp values (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
insert into emp values (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
insert into emp values (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
insert into emp values (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
insert into emp values (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
insert into emp values (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
insert into emp values (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
insert into emp values (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
commit;
select count(*) from emp; COUNT(*) -------- 14
- Insert another row at time t2:
insert into emp values (8282,'Can','Analyst',7566,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,20);
commit;
select count(*) from emp; COUNT(*) -------- 15
Find the employees that are added before t2 (note that I just picked a timestamp that corresponds a point in time between t1 and t2):
select * from emp as of timestamp to_timestamp('2023-07-26 19:36:00', 'YYYY-MM-DD HH24:MI:SS');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ----- ------ ------ --------- ----- ------- ------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
As we can see, the query returns the 14 rows that were added before t2.
Change the Retention Period of the Flashback Archive
Before we conclude this blog post, I also want to share how you can change the default retention period of your flashback archive. By the default, the archive retention period is 60 days. However, we can change it at any time, which is an important flexibility for compliance and auditing reasons. In our example below, we’ll be changing our retention period to 180 days:
select FLASHBACK_ARCHIVE_NAME, RETENTION_IN_DAYS from DBA_FLASHBACK_ARCHIVE;
FLASHBACK_ARCHIVE_NAME RETENTION_IN_DAY
---------------------- ----------------
FLASHBACK_ARCHIVE 60
BEGIN
DBMS_CLOUD_ADMIN.SET_FLASHBACK_ARCHIVE_RETENTION (retention_days => 180);
END;
/
PL/SQL procedure successfully completed.
select FLASHBACK_ARCHIVE_NAME, RETENTION_IN_DAYS from DBA_FLASHBACK_ARCHIVE;
FLASHBACK_ARCHIVE_NAME RETENTION_IN_DAY
---------------------- ----------------
FLASHBACK_ARCHIVE 180
To summarize, Flashback Time Travel is a key feature of the Oracle Flashback Technology family as it allows automatic and transparent tracking of changes made to user tables. The ability to access historical data allows for deeper data and trend analysis for organizations, which especially important for compliance and auditing reasons in certain industries such as finance and healtcare. Furthermore, the best part is to be able to access the historical data via simple SQL queries as we have seen above. To learn more about Flashback Time Travel in ADB-S, check out our documentation.