4 Replies Latest reply on Jul 14, 2010 1:54 AM by baskar.l

    ORA-00604: error occurred at recursive SQL level 1

    cptkirkh
      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