Search code examples
databaseoracle-databaseflashback

Oracle Flashback on System Tables

Is there a possibility to use the Oracle Flashback on System tables, such as the user_tables or cols?

I was able to successfully issue Flashback queries against the tables I created, but when I tried the same against System tables, I get ORA-01031: insufficient privileges. Does it require granting of special privileges?

Solution

  • This is expected, as the view that your are accessing using AS OF query is based on system tables owned by SYS, and are prevented from being accessed by the grants like flashback any tableor select any table.

    You need to set O7_DICTIONARY_ACCESSIBILITY to TRUE to enable a user to flashback system tables. 

    SQL> conn jay/jay
Connected.
SQL> SELECT * FROM user_tables
AS OF TIMESTAMP
TO_TIMESTAMP('2017-03-21 06:45:00', 'YYYY-MM-DD HH24:MI:SS'); 
SELECT * FROM user_tables
              *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size          2253784 bytes
Variable Size         327158824 bytes
Database Buffers       79691776 bytes
Redo Buffers            4268032 bytes
Database mounted.
Database opened.

SQL> conn jay/jay
Connected.

SQL> SELECT * FROM user_tables
AS OF TIMESTAMP
TO_TIMESTAMP('2017-03-21 06:45:00', 'YYYY-MM-DD HH24:MI:SS');

    Tested in Oracle 11.2.0.4.

    Note: Read the documentation before enable it.

    Reference:O7_DICTIONARY_ACCESSIBILITY