10 Replies Latest reply on Nov 26, 2012 4:10 AM by Maahjoor

    ORA-00018.Maximum number of sessions exceed

    Maahjoor
      Dear all,

      i have a windows 2003 server, oracle 10g release 2.
      300 concurent users login.

      my SESSIONS parameter =800
      my PROCESSES parameter=723.

      after 2 days or 3 days, i got the follwing message when i try to connect.

      ORA-00018.Maximum number of sessions exceed.

      even i restart my server, i sometime got this number.

      what is the problem? i am realy in very bad situation, users cannot connected,

      thanks
        • 1. Re: ORA-00018.Maximum number of sessions exceed
          Osama_Mustafa
          First Close all the duplicate thread , mark them as answered Please.
          ORA-00018.Maximum number of sessions exceed
          to solve this issue
           Alter system set sessions=<New Number> scope=spfile;
           Alter system set processes=<New Number> scope=spfile;
          
           Bounce Database
          • 2. Re: ORA-00018.Maximum number of sessions exceed
            Maahjoor
            thanks oasam,
            but please have a second look at my post,

            i said i have 800 sessions parameter set.
            and 300 are concurent users.
            even i restart the database,

            please do.
            • 3. Re: ORA-00018.Maximum number of sessions exceed
              Osama_Mustafa
              - Are you using any application ?
              - Post 50 lines from the alert<SID>.log

              also notice what i said first , close the duplicate thread don't leave them_

              Edited by: Osama_mustafa on Nov 25, 2012 9:17 AM
              • 4. Re: ORA-00018.Maximum number of sessions exceed
                Girish Sharma
                Please post output of below commands :

                select value from v$parameter where name = 'sessions';
                select count(*) from v$session;
                select * from v$resource_limit;

                If you see output of 3rd command as equal values in all columns then its correct and Oracle will return ORA-00018.

                So, V$SESSION is wrong! It does not report all sessions really in use. If you look into V$SESSION view text (with help of V$FIXED_VIEW_DEFINITION), you'll see that V$SESSION reports only USER and BACKGROUND sessions.

                But there's a 3rd type of a session - a RECURSIVE session, which is used for recursive data dictionary calls as explained above. V$SESSION doesn't show these.

                So, what's the moral of this story?

                Oracle uses recursive sessions for recursive data dictionary operations
                These sessions are also taken from session state object array controlled by sessions parameter
                V$SESSION does not show recursive sessions, but V$RESOURCE_LIMIT tells you the truth about session state object array utilization
                If you hit the ORA-00018 error, then make your sessions parameter array larger or configure your application to use less connections or sessions

                Note that in Oracle 11.2 the automatic calculation of sessions parameter has changed and many more session state objects are allocated for a given number of processes by default.

                Source:http://tech.e2sn.com/oracle/oracle-internals-and-architecture/recursive-sessions-and-ora-00018-maximum-number-of-sessions-exceeded

                Regards
                Girish Sharma
                • 5. Re: ORA-00018.Maximum number of sessions exceed
                  Maahjoor
                  yes, you are on the right direction.
                  out asp.net developer has a reply from the forum that you are opening recursive sessions.

                  so your desired info is below. kindly guide me. i have just restarted the database.


                  SQL> select value from v$parameter where name = 'sessions';

                  VALUE
                  --------------------------------------
                  800


                  SQL> select count(*) from v$session;

                  COUNT(*)
                  ----------
                  25

                  SQL> select * from v$resource_limit;

                  RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU LIMIT_VALU
                  ------------------------------ ------------------- --------------- ---------- ----------
                  processes 26 30 723 723
                  sessions 32 129 800 800
                  enqueue_locks 14 22 9746 9746
                  enqueue_resources 14 37 3740 UNLIMITED
                  ges_procs 0 0 0 0
                  ges_ress 0 0 0 UNLIMITED
                  ges_locks 0 0 0 UNLIMITED
                  ges_cache_ress 0 0 0 UNLIMITED
                  ges_reg_msgs 0 0 0 UNLIMITED
                  ges_big_msgs 0 0 0 UNLIMITED
                  ges_rsv_msgs 0 0 0 0
                  gcs_resources 0 0 0 0
                  gcs_shadows 0 0 0 0
                  dml_locks 0 42 3520 UNLIMITED
                  temporary_table_locks 0 0 UNLIMITED UNLIMITED
                  transactions 1 5 880 UNLIMITED
                  branches 0 0 880 UNLIMITED
                  cmtcallbk 0 2 880 UNLIMITED
                  sort_segment_locks 0 1 UNLIMITED UNLIMITED
                  max_rollback_segments 11 11 880 65535
                  max_shared_servers 3 3 5 5
                  parallel_max_servers 0 2 40 3600
                  • 6. Re: ORA-00018.Maximum number of sessions exceed
                    Maahjoor
                    sorry for the disign, how could i post so it would looks like it is in the sql plus.
                    • 7. Re: ORA-00018.Maximum number of sessions exceed
                      Hoek
                      how could i post so it would looks like it is in the sql plus.
                      Use the {noformat}
                      {noformat} tag if you want to post formatted code.
                      See #7, 8 and 9 @ {message:id=9360002}                                                                                                                                                                                                                                                                                                                                                                            
                      • 8. Re: ORA-00018.Maximum number of sessions exceed
                        Girish Sharma
                        In asp.net whenever a user populates its data controls i.e. data reader or data adapter etc. first they creates session and then that control get populated from data of table/view/MV etc. In this duration, if user closes the application, it done not mean that session also has been closed untill and unless in the application there is no that control's.close() (control.dispose() do not also close the session) and connection.close() finds. This is biggest reason of reaching session maximum limit and then ORA-00018.

                        - So, ask application developer that they should have ensure about proper closing the session.
                        - In the connection string's property they can off the connection pooling also.

                        As a DBA, you can do following :

                        1.- alter system set resource_limit = true;
                        OR
                        Set the parameter in the parameter file and restart the database.

                        2. Create the profile for monitoring idle time:
                        - create profile profile_name limit idle_time no_of_minutes;
                        Example:
                        - create profile idletime limit idle_time 10;

                        3. Attach the profile to desired user:

                        - alter user user_name profile profile_name;

                        Example:

                        - alter user test profile idletime;

                        This forces any Oracle user sessions, which have been inactive for greater than no_of_minutes, to be disconnected from the database. Any uncommitted transaction will be rolled back. When the idle time has passed, the session will be suspended.

                        Regards
                        Girish Sharma
                        • 9. Re: ORA-00018.Maximum number of sessions exceed
                          JohnWatson
                          Girish Sharma wrote:
                          - create profile idletime limit idle_time 10;
                          - alter user user_name profile profile_name;

                          This forces any Oracle user sessions, which have been inactive for greater than no_of_minutes, to be disconnected from the database. Any uncommitted transaction will be rolled back. When the idle time has passed, the session will be suspended.
                          I don't think this will actually do what OP requires. In the release I've just tested (11.2.0.3) the session survives as an inactive session indefinitely. It is terminated and removed from v$session only when the user returns and attempts to do something. Furthermore, if the session is in a transaction, the limit is not enforced.
                          In earlier releases (if I remember correctly) profiles were even less use: the session would survive as a "sniped" session, which you would have to kill manually. I suppose that the logic behind a sniped session remaining is that a sniper kills from a distance; you have to pick up the bodies later.
                          Actually to remove idle sessions after a time period, I think one needs to use the Resource Manager or (in 11.x) a database resident connection pool.
                          1 person found this helpful
                          • 10. Re: ORA-00018.Maximum number of sessions exceed
                            Maahjoor
                            you are simply genious man!!

                            you realy really genious , what you eat?

                            thanks a very lot, i was not so clearing in question, but you are very clear while answering.

                            it was exactly as you said, the recursive approach, and the closing of sesions.
                            our developer open the session, but not closing it. and due to the code bug in asp.net, if some exception occur, the application try to connect in a loop manner.
                            so very soon the maximum number of sessions exceed the limit.

                            i will consider you suggesions regarding the profile and resource limit also. i will difinitly apply it.

                            thank you so much for the time to give to me.
                            live long sharma ji.