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

May 26, 2021
()

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.

Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

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?

<strong>Hello and welcome to DBsGuru,</strong>DBsGuru is a group of experienced DBA professionals and serves databases and their related community by providing technical blogs, projects, training. Technical blogs are the source of vast information not about databases but its related product like middleware, PL/SQL, replication methodology, and so on.Thanks for the visits!<strong>Share Learn Grow!</strong>

2 Comments

Leave a Reply

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