Martins Blog

Use Flashback Time Travel with Autonomous Database

This is a very short post, mainly for my own benefit, to summarise how to use Flashback Time Travel (previously named Flashback Data Archive or Total Recall) in Autonomous Database 23ai – Serverless (ADB-S from now on).

At the time of writing the database was patched to Release Update 7:

SQL> select
  2     version_full
  3  from
  4*   product_component_version;

VERSION_FULL    
_______________ 
23.7.0.25.02

If you haven’t used Flashback Time Travel before, I would like to invite you to read up on the feature in this blog post and the ADB-S documentation. You may want to pay particular attention to the restrictions.

I never use the ADMIN user for anything but administrative work in Autonomous Database. Just as I don’t work as root in Linux. My application user is typically called EMILY, and it’s granted the following privileges/roles:

As per the documentation, an ADB-S instance comes with one (and only ever one) Flashback Archive defined:

SQL> select
  2    flashback_archive_name,
  3    status
  4  from
  5*   dba_flashback_archive;

FLASHBACK_ARCHIVE_NAME    STATUS     
_________________________ __________ 
FLASHBACK_ARCHIVE         DEFAULT

Initially no other user has access to it, apart from ADMIN. The retention time is also a little short, you may want to bump that number using DBMS_CLOUD_ADMIN.

If you don’t grant your user access to the Flashback Archive, you will get this error:

SQL> create table emp (
  2    empno    number(4) constraint pk_emp primary key,
  3    ename    varchar2(10),
  4    job      varchar2(9),
  5    mgr      number(4),
  6    hiredate date,
  7    sal      number(7,2),
  8    comm     number(7,2),
  9*   deptno   number(2) ) FLASHBACK ARCHIVE;

Error starting at line : 1 in command -
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
Error report -
ORA-55620: No privilege to use Flashback Archive

If indeed the user should be allowed to use the Flashback Data Archive, ADMIN can fix this error like so:

SQL> sho user
USER is "ADMIN"

SQL> grant flashback archive on FLASHBACK_ARCHIVE to emily;

Grant succeeded.

Now EMILY has the necessary rights to make use of the Flashback Archive. Depending on how you created your user, you may need to explicitly allocate a tablespace quota to avoid an ORA-1950:

SQL> create table emp (
  2    empno    number(4) constraint pk_emp primary key,
  3    ename    varchar2(10),
  4    job      varchar2(9),
  5    mgr      number(4),
  6    hiredate date,
  7    sal      number(7,2),
  8    comm     number(7,2),
  9*   deptno   number(2) ) FLASHBACK ARCHIVE;

Error starting at line : 1 in command -
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
Error report -
ORA-01950: The user EMILY has insufficient quota on tablespace DATA.

This error is simple to fix using an alter user EMILY quota ... on DATA statement, executed by ADMIN.

Happy Archiving!