Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Help on PL-SQL

  1. #1
    Join Date
    Mar 2006
    Posts
    9

    Question Unanswered: Help on PL-SQL

    Please help on PL-SQL

    Recently, I enabled the resource limit to minimize its usage (cuz of so many
    inactive sessions). However, after ## idle-time, the session is DISCONNECTED
    from database, but the session is still existed.
    Therefore, I need to create a ORACLE job to "kill" those SNIPED
    sessions as follow:
    Code:
    CREATE OR REPLACE PACKAGE SNIPED_SESSIONS
    AS
    PROCEDURE KILL_ON_SID;
    END SNIPED_SESSIONS;
    
    CREATE OR REPLACE PACKAGE BODY SNIPED_SESSION
    AS
      PROCEDURE KILL_ON_SID
      AS
             CursorID INTEGER;
             Returned INTEGER;
             KillStatement varchar2(100);
          CURSOR AltSysCmd IS
            SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||''';'
            FROM V$SESSION
            WHERE STATUS='SNIPED' and USERNAME is not NULL;
      BEGIN
          OPEN AltSysCmd;
          LOOP
          FETCH AltSysCmd into KillStatement;
          EXIT WHEN AltSysCmd%NOTFOUND;
          CursorID :=dbms_sql.open_cursor;
          dbms_sql.parse(CursorID,KillStatement,dbms_sql.v7);
          Returned :=dbms_sql.execute(CursorID);
          dbms_sql..close_cursor(CursorID);
          END LOOP;
          CLOSE AltSysCmd;
    
      END KILL_ON_SID;
    END SNIPED_SESSION;
    Compilation works fine. Executed with no error, but all SNIPED sessions still remained.

    What did I do wrong? Please suggest. Thanks.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    Unix system?
    The sessions will show up until some type of command is run inside those sessions.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    What happens when you execute, from the terminal, the command itself for one of the sniped sessions ? are they gone or do they still show up until some time ?

  4. #4
    Join Date
    Mar 2006
    Posts
    9
    Yes .. it is on UNIX system.

    When I ran it
    Code:
    exec SNIPED_SESSION.KILL_ON_SID;
    
    PL/SQL procedure successfully completed.
    However, those SNIPED sessions are still existed -- not mark as KILLED or remove completely from v$session.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Please answer the question, what happens if you type the command into sql*plus. Not the procedure, but the actual alter system command?

    Also, I would write your procedure a little different.

    Code:
    CREATE OR REPLACE PACKAGE SNIPED_SESSIONS
    AS
    PROCEDURE KILL_ON_SID;
    END SNIPED_SESSIONS;
    
    CREATE OR REPLACE PACKAGE BODY SNIPED_SESSION
    AS
      PROCEDURE KILL_ON_SID
      AS
          CURSOR AltSysCmd IS
            SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||''' KillStatement
            FROM V$SESSION
            WHERE STATUS='SNIPED' and USERNAME is not NULL;
      BEGIN
          FOR PNT IN AltSysCmd LOOP
            EXECUTE IMMEDIATE PNT.KillStatement;
         END LOOP;
      END KILL_ON_SID;
    END SNIPED_SESSION;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Mar 2006
    Posts
    9
    If I use the command:
    Code:
    ALTER SYSTEM KILL SESSION '123,12345';
    The session with SID=123, SERIAL# = 12345 will be removed from v$session.

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    why not spool to a file and then have Unix cron run:
    kill -9 [processid]
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Quote Originally Posted by sbinh
    If I use the command:
    Code:
    ALTER SYSTEM KILL SESSION '123,12345';
    The session with SID=123, SERIAL# = 12345 will be removed from v$session.
    They will not be removed until PMON do so. They will be marked SNIPED, but they can no longer do anything on the database except get an error message. If you want to be removed from v$session entirely, you have to kill the process the session has at the OS level.

  9. #9
    Join Date
    Mar 2006
    Posts
    9
    Thanks all for your help.

    The problem is that I cannot log into client machines to kill those processes.
    People are using client applicationts (several diffent ones) that connect into database to update data.
    My guess is that v$session won't be cleaned out until users close applications from their sites.

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    They are not talking about killing the processes on the client machine, they are talking about killing the process on the server. Every user connection spawns a seperate process to handle the connection. Kill the process and the user is gone. This wouldn't be needed if Oracle would get off it's lazy butt and impliment a forced kill that wouldn't require that the client acknowledge first. This has been requested by the oracle community since oracle 7.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    You might also try

    ALTER SYSTEM DISCONNECT SESSION 'x,y' IMMEDIATE;

    This is a bit of a guess though as I haven't set up your scenario to test it.

  12. #12
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by The_Duck
    why not spool to a file and then have Unix cron run:
    kill -9 [processid]
    The "-9" option seems very popular. Is it necessary here?

  13. #13
    Join Date
    Mar 2006
    Posts
    9
    Quote Originally Posted by WilliamR
    You might also try

    ALTER SYSTEM DISCONNECT SESSION 'x,y' IMMEDIATE;

    This is a bit of a guess though as I haven't set up your scenario to test it.

    Tried this and it returned ORA-00031 error: "session has marked as killed."

    By looking at above ORA 31 error, it says multiple "alter system kill session" executions would help to kill the session faster ....

  14. #14
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Can you show the commands you used so I can set up a test?

    In 9.2.0.1 the resource profile seems to be ignored. Most likely I am missing something. I tried:

    Code:
    SQL> CREATE PROFILE test_timeout LIMIT IDLE_TIME 2 CONNECT_TIME 2;
    
    Profile created.
    
    SQL> CREATE USER fred IDENTIFIED BY fred
      2  DEFAULT TABLESPACE users                
      3  PROFILE test_timeout;
    
    User created.
    
    SQL> GRANT CREATE SESSION TO fred;
    
    Grant succeeded.
    Then in another session:

    Code:
    /Users/williamr: sqlplus fred/fred@dev
    
    SQL*Plus: Release 9.2.0.1.0 - Developer's Release on Sat Mar 25 09:36:17 2006
    
    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
    
    
    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.1.0 - Developer's Release
    With the Partitioning and Oracle Data Mining options
    
    
    SQL> select * from user_resource_limits;
    
    RESOURCE_NAME                    LIMIT
    -------------------------------- ----------------------------------------
    COMPOSITE_LIMIT                  UNLIMITED
    SESSIONS_PER_USER                UNLIMITED
    CPU_PER_SESSION                  UNLIMITED
    CPU_PER_CALL                     UNLIMITED
    LOGICAL_READS_PER_SESSION        UNLIMITED
    LOGICAL_READS_PER_CALL           UNLIMITED
    IDLE_TIME                        2
    CONNECT_TIME                     2
    PRIVATE_SGA                      UNLIMITED
    
    9 rows selected.
    
    SQL> set time on
    09:36:30 SQL> 
    09:36:31 SQL> 
    09:50:17 SQL> select * from dual;
    
    D
    -
    X
    
    1 row selected.
    
    09:50:22 SQL>
    Fred was still connected over 10 minutes later despite having a 2 minute timeout.

    btw with ALTER SYSTEM DISCONNECT SESSION, I was trying this advice, although I'm not convinced it's as simple as that.
    Last edited by WilliamR; 03-25-06 at 06:08.

  15. #15
    Join Date
    Mar 2006
    Posts
    9
    You also need to enable resource limit:

    alter system set resource_limit=true;

    Without it, idle_time would not be in effect.

Posting Permissions

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