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:
3. Alternate solution
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
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
We are sorry that this post was not useful for you!
Let us improve this post!
Tell us how we can improve this post?
Hello and welcome to DBsGuru,
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!
Share Learn Grow!