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?
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
