Solution for ORA-00904: DBMS_FLASHBACK: invalid identifier in Oracle

Solution for ORA-00904: DBMS_FLASHBACK: invalid identifier in Oracle


In this article, we will demonstrate a solution for ORA-00904: DBMS_FLASHBACK: invalid identifier in Oracle. This error reflects whenever the user tries to get access current system change number (SCN) without appropriate access. 


To eliminate this error ORA-00904, we will grant EXECUTE on package DBMS_FLASHBACK or SELECT access on dynamic view SYS.V_$DATABASE to the user. 


DBMS_FLASHBACK:
The Security model of this package, the user must have execute access on DBMS_FLASHBACK..
SYS.V_$DATABASE: This is a dynamic performance view where only SYS users or any other users with SYSDBA
privileges have access to  SYS.V_$DATABASE.


To complete this demonstration, we will complete it in three below section:


1. Errors

2. Solution
3. Alternate solution

1. Errors

In this section, the user TEST executes the command to find SCN and received the error ORA-00904 due to insufficient privileges.

 

1.1 Connect to TEST user: User connected to schema TEST

SQL> conn test/XXXXXXXX
Connected.
SQL> show user
USER is "TEST"


1.2. Find SCN: User executed commands to find SCN but received errors.

SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL
       *
ERROR at line 1:
ORA-00904: "DBMS_FLASHBACK"."GET_SYSTEM_CHANGE_NUMBER": invalid identifier

OR

SQL> SELECT CURRENT_SCN FROM V$DATABASE;
SELECT CURRENT_SCN FROM V$DATABASE
                        *
ERROR at line 1:
ORA-00942: table or view does not exist


2. Solution

In this section, we will grant require privileges to the user to fix this error.


2.1. Grant privileges:
We will grant require privileges to user TEST.

SQL> GRANT EXECUTE ON DBMS_FLASHBACK TO TEST;

Grant succeeded.

OR

SQL> GRANT READ ON SYS.V_$DATABASE TO TEST;

Grant succeeded.


2.2. Find SCN: Login to schema TEST and execute the command again to find SCN successfully.

SQL> conn test/XXXXXXXX
Connected.
SQL> show user
USER is "TEST"
SQL> SET NUMWIDTH 20
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1660301
OR

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
    1660308


3. Alternate solution

We have an alternate solution to get SCN using function TIMESTAMP_TO_SCN(SYSDATE) without grant any additional privileges.


3.1. Create user:
Create a new user TEST_SCN. 

SQL> CREATE USER TEST_SCN IDENTIFIED BY XXXXXXXX;

User created.

SQL> GRANT CREATE SESSION TO TEST_SCN;

Grant succeeded.


3.2 Find SCN: Login to schema TEST_SCN and execute commands to find SCN without grant any additional privileges.

SQL> conn test_scn/XXXXXXXX
Connected.
SQL> show user
USER is "TEST_SCN"
SQL> SELECT TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL;

TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
                  1660542


This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article
!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

 119 Total Views,  8 Views Today

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Spread the Knowledge!

Leave a Reply

Your email address will not be published. Required fields are marked *

18 − seventeen =

Follow us on Social Media
Open chat
1
Contact Us:
Hi,

Greetings of the day! How can we help you?

Thanks.
Team DBsGuru
Direct Call:+91-9310167776
Email: info@dbsguru.com

Share Learn Grow!


Click on the below technical group to join us to share expertise in PostgreSQL and MySQL on WhatsApp group. The purpose of this group is only help to the DBA community.

PostgreSQL Technical Group.

MySQL technical Group.

NOTE: Excuse us for spammer/promoter i.e don't join the group for spam, will be kicked off without warnings.

Thanks.
Team DBsGuru.

Share Learn Grow!

Welcome to DBsGuru! We wish you a very healthy day, hope and pray things to go in a good way for all of humanity. Stay safe!

We encourage technology experts to contribute share technical knowledge in form of writing technical articles/blogs, SQL commands for daily usage (basic to a high level), Carrier guidance on any technology, and become an author.

We have a ready platform for you with no profit no loss (as of now, in the future you may also earn revenue) if you are ready to contribute to writing articles, click on the registration link and the article will be published as an individual contributor on your name.

Click here for registration

Thanks,
Team DBsGuru
We Commit We Deliver