标题: Flashback Queries Are Failing With ORA-1031 [打印本页] 作者: 刘泽宇 时间: 2025-11-16 17:59 标题: 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;
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;