My Oracle Support Banner

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

Last updated on MARCH 17, 2019

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 11.1 to 11.2]
Information in this document applies to any platform.


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

alter table flash.test flashback archive; 

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

--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 view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.