Problem solve Get help with specific problems with your technologies, process and projects.

Getting ORA-00604 error when connecting to database user

I am getting the following error: ORA-00604:error occurred at recursive sql level 1. This error occurred when I was trying to connect to the database user. But if I grant DBA role, that user is able to connect. System user is able to connect but scott is not able to connect. Oracle does a lot of work behind the scenes for you. It does this through its own SQL statements. Any SQL statement Oracle issues for you is a "recursive SQL" statement. There could be many SQL statements that are causing your problem. What I would do is to set SQL_TRACE=TRUE in my INIT.ORA and restart the database. I would then duplicate the ORA-604 error. This will generate many trace files in your USER_DUMP_DEST directory for all user processes. Immediately after the error, bring down the database and set SQL_TRACE=FALSE. You can then start your database again. Now dig through the trace files you just generated in your USER_DUMP_DEST directory to find the one that has the ORA-604 error. Right where the ORA-604 error is found will be the recursive SQL statement that had the problem and the "real" error that it experienced. Your solution will depend on the statement and the real error.

Dig Deeper on Oracle database design and architecture