Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Privilege required for "as of timestamp" clause

969019Jan 28 2013 — edited Jan 28 2013
When I try following SQL to retrieve records in some table XXXXX in the past point in time:
select * from XXXXX as of timestamp systimestamp-(6/24);

I am receiving following error:
ORA-01031: insufficient privileges

Without "as of timestamp" clause, it does run fine.
Which privilege do I have to receive for above to work?
Thank you in advance.

Comments

sb92075
How do I ask a question on the forums?
2176552

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#i2065706
Nicosa-Oracle
Hi,

I guess your user needs the flashback privilege granted : 
grant flashback on your_table to your_user;
unknown-7404
Note the last paragraph in the SQL Reference doc for the SELECT statement:
>
Prerequisites

For you to select data from a table or materialized view, the table or materialized view must be in your own schema or you must have the SELECT privilege on the table or materialized view.

For you to select rows from the base tables of a view:

•You must have the SELECT privilege on the view, and

•Whoever owns the schema containing the view must have the SELECT privilege on the base tables.

The SELECT ANY TABLE system privilege also allows you to select data from any table or any materialized view or the base table of any view.

To issue an Oracle Flashback Query using the flashback_query_clause, you must have the SELECT privilege on the objects in the select list. In addition, either you must have FLASHBACK object privilege on the objects in the select list, or you must have FLASHBACK ANY TABLE system privilege.
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 25 2013
Added on Jan 28 2013
3 comments
10,394 views