ORA-00604: error occurred at recursive SQL level 1
This morning my DB quit accepting logins. After bouncing it everything was fine but i would like to n know why. The alert log listed the following:
Errors in file f:\oracle\product\10.2.0\admin\porky\bdump\porky_smon_4036.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00018: maximum number of sessions exceeded
So i went to that file and this was just the beginnign of a large file:
Dump file f:\oracle\product\10.2.0\admin\porky\bdump\porky_smon_4036.trc
Fri Jul 09 08:51:43 2010
ORACLE V10.2.0.3.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Release 10.2.0.3.0 - Production
Windows Server 2003 Version V5.2 Service Pack 2
CPU : 4 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:874M/3967M, Ph+PgF:2872M/5863M, VA:301M/3031M
Instance name: porky
Redo thread mounted by this instance: 1
Oracle process number: 8
Windows thread id: 4036, image: ORACLE.EXE (SMON)
*** SERVICE NAME:(SYS$BACKGROUND) 2010-07-09 08:51:43.348
*** SESSION ID:(164.1) 2010-07-09 08:51:43.348
*** 2010-07-09 08:51:43.348
SMON: following errors trapped and ignored:
ORA-00018: maximum number of sessions exceeded
*** 2010-07-09 08:51:44.380
SMON: following errors trapped and ignored:
ORA-00604: error occurred at recursive SQL level 1
ORA-00018: maximum number of sessions exceeded
*** 2010-07-09 08:51:46.005
SMON: following errors trapped and ignored:
ORA-00604: error occurred at recursive SQL level 1
ORA-00018: maximum number of sessions exceeded
*** 2010-07-09 08:51:47.020
SMON: following errors trapped and ignored:
ORA-00604: error occurred at recursive SQL level 1
ORA-00018: maximum number of sessions exceeded
*** 2010-07-09 08:51:48.036
SMON: following errors trapped and ignored:
ORA-00604: error occurred at recursive SQL level 1
ORA-00018: maximum number of sessions exceeded
What is the best way to find out what was the issue? I know it was just the max number of sessions was reached but is there a way to find out why? Also for sessions in the parameter file i read that i should put 2.5*max_users. Is that how i should configure that number? I read that you should take 2.5* the max_utilization number found for sessions when you run 'select * from v$resource_limit' Is that correct? Thanks