How to Use Flashback Time Travel in Autonomous Database

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.