Q

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.
This was first published in December 2003

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close