DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: ora-00020 error - # of processses exceeded

  1. #1
    Join Date
    Jul 2003
    Posts
    136

    Question ora-00020 error - # of processses exceeded

    I am getting
    ora-00020 error - # of processses exceeded
    Oracle 8.1.6 resides on win200ok server.

    I have increased the # of processes in init file from 200 to 600. It worked for 2 days fine, now its exceeding 600 proceeses and giving me ora-00020 error again.

    I am using profiles to apply limits

    sessions_per_user 8
    cpu_per_session 1000000
    cpu_per_call 1000000
    connect_time unlimited
    idle_time 60
    logical_reads_per_session unlimited
    logical_reads_per_call unlimited
    composite_limit 2000000
    private_sga unlimited

    Still ora-00020 error happening. Is there anything apart from digging code to find any loose/open processes.

    Any help is much appreciated.

    -D

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well you have to check with your developers if they close sessions when they finish any query or transaction

  3. #3
    Join Date
    Jul 2003
    Posts
    136
    Originally posted by pando
    well you have to check with your developers if they close sessions when they finish any query or transaction
    They are closing sessions - well most of the times. Do I need to move to MTS settings is there any more things I can try.

    -D

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    if they close sessions there is no way you would get this kind of errors

  5. #5
    Join Date
    Jun 2001
    Location
    California
    Posts
    124
    Thats for sure developer or user are not closing sessions. Though you can implement DCD (Dead Connection Detection).Oracle recommends a value of approximately 10 (minutes) for the SQLNET.EXPIRE_TIME parameter. If it is not specified, or set to 0, then broken connections may be maintained indefinitely.

    HTH

  6. #6
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    hi

    i am also getting this sort of errors.

    the oracle is clearing the connection
    but the os doesn't kill the that connection.

    when you look at the status of the session
    they are shown as SNIPED.

    even the kill from oracle
    (alter system kill session .........)
    has no effect.

    then i had to generate the script to generate
    a batch to use the orakill utility.

    you can try that.

    if you want i will send.


    -Raja

  7. #7
    Join Date
    Jul 2003
    Posts
    136
    Originally posted by rajabalachandra
    hi

    then i had to generate the script to generate
    a batch to use the orakill utility.

    you can try that.

    if you want i will send.


    -Raja
    Yes, Please send me the script. Thanks
    -D

  8. #8
    Join Date
    Jan 2001
    Posts
    3,134
    Is re-booting and option?
    I think I saw this same thing in development once and a re-boot solved it.
    One way or another your sessions are not getting cleared.

    MH
    I remember when this place was cool.

  9. #9
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    What do you see in v$session and v$process ??
    Are you using WIN performance monitor or Compaq Insight Manager ?

    This may be of help.
    http://metalink.oracle.com/metalink/..._id=213004.995
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  10. #10
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    the content of my batch file raja.bat
    =====================================


    sqlplus scott@his/tiger @C:\HISKILL.SQL

    c:\SNIPKILL.bat



    the content of my script file c:\hiskill.sql
    ============================================

    SET HEAD OFF;

    SPOO C:\SNIPKILL.BAT;

    SELECT 'ORAKILL HIS ' , B.SPID
    FROM V$SESSION A, V$PROCESS B
    WHERE A.PADDR = B.ADDR
    AND A.STATUS='SNIPED';

    SPOO OFF;


    EXIT;



    the sample output from the spool c:\snipkill.bat
    ================================================

    ORAKILL HIS 770
    ORAKILL HIS 545
    ORAKILL HIS 378
    ORAKILL HIS 534
    ORAKILL HIS 339
    ORAKILL HIS 703
    ORAKILL HIS 748
    ORAKILL HIS 600


    8 rows selected.



    i run the batch file raja.bat.

    it executes the script hiskill.sql and the
    batch file (snipkill.bat) spooled by
    the the script.

    in this script 'ORAKILL' is the utility you
    can find in the \bin directory.

    'HIS' is my database SID.
    (You have to replace with your database sid).

    the executing user should have select privilege over
    v$session and v$process.


    Note: You can run this batch file as scheduled task also.


    -Raja

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width