Flashback Queries Are Failing With ORA-1031 (Doc ID 946477.1)

Last updated on NOVEMBER 04, 2015

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.7 and later   [Release: 11.1 and later ]
Information in this document applies to any platform.

Symptoms

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

 


.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms