Flashback Queries Are Failing With ORA-1031

刘泽宇

现象：
When enabling flashback data archive mode on a table, the users should be able to flashback query the table as long as they have the SELECT and FLASHBACK privileges for the table. However, this
does not work:

conn / as sysdba

drop user flash cascade;
drop user flash2 cascade;

create user flash identified by flash;
create user flash2 identified by flash2;

grant connect, resource to flash,flash2;
grant flashback any table to flash2;
create table flash.test(col1 number, col2 varchar2(100));
grant select on flash.test to flash2;

create flashback archive default testflash tablespace users retention 1
month;

alter table flash.test flashback archive;


insert into flash.test values(1,'rec1');
insert into flash.test values(2,'rec2');
commit;

--restart the database to make sure that the old data is not in the UNDO tablespace anymore

conn flash2/flash2

select * from flash.test as of timestamp (systimestamp - interval '1' minute);
select * from flash.test as of timestamp (systimestamp - interval '1' minute)
*
ERROR at line 1:
ORA-01031: insufficient privileges


原因：
To be able to use the flashback query feature, when the historical data must be retrieved from the archive data tables rather than from the UNDO tablespace, an user must be able to query the flashback archive tables. The privileges to read these flashback archive tables must be managed internally. However this does not happen. The problem was diagnosed in Bug 8791750 - SELECT AS OF TIMESTAMP FAILS WITH ORA-1031


处理方法：
The problem can be solved if  Patch 8791750 is installed. This bug 8791750 fix is included in 11.2.0.2 and 12.1 DB

If there is no patch available then we can temporarily grant "SELECT ANY TABLE" to the user executing the flashback query or we can grant directly the SELECT privilege on the three archive tables created for the table on which we enabled the flashback queries.

To find out the tables one has to do the following :

SQL> select * from sys_fba_trackedtables where obj# > 0;


OBJ# FA# DROPSCN OBJNAME OWNERNAME FLAGS SPARE
---- --- ------- ------- --------- ----- -----
62697 1  0       EMP     FLASH     0

Get the obj# from the above query and then run:

select table_name from dba_tables where table_name like 'SYS_FBA%<obj#>';

SQL> select table_name from dba_tables where table_name like 'SYS_FBA%62697';

TABLE_NAME
------------------------------
SYS_FBA_DDL_COLMAP_62697
SYS_FBA_TCRV_62697
SYS_FBA_HIST_62697