Flashback Table To Scn Fails With ORA ORA-01031 (Doc ID 1956473.1)

Last updated on JUNE 10, 2021

Applies to:

Symptoms

On : 11.2.0.4 version, Database Security

When attempting to flashback a table using a user A
the following error occurs.

ERROR
-----------------------
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges



STEPS
-----------------------
The issue can be reproduced at will with the following steps:

1) connect system/<password>

create user xxxxxx_xxx identified by xxxx;
grant connect,resource to xxxxxx_xxx;
create table xxxxxx_xxx.t (t number);
alter table xxxxxx_xxx.t enable row movement;
select current_scn from v$database;
insert into xxxxxx_xxx.t values (1);
create user a identified by <password>;
grant connect to a;
grant select, insert, update,delete, flashback on xxxxxx_xxx.t to a;

2)connect a/a

flashback table xxxxx_xxx.t to scn xxxxx;

Cause

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