Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Procedure for Logging SQL*Net/Break Sessions

Naveen BApr 12 2016 — edited Apr 28 2016

Purpose of Procedure: Kill SQL*Net/Break sessions and log them into separate table. This will be run using SYS account only.

If I remove all cursor2 related code to insert records into table, it works fine.

Issue: Need to troubleshoot this procedure quickly. Keep getting the (PLS-00103: Encountered the symbol "end-of-file") error.

/* Code attached below. */

-- Table Creation code

CREATE TABLE V$SESSION_LOG

(

   SID            NUMBER,

   SERIAL#        NUMBER,

   USERNAME       VARCHAR2 (30 CHAR),

   STATUS         VARCHAR2 (8 CHAR),

   OSUSER         VARCHAR2 (30 CHAR),

   EVENT          VARCHAR2 (64 CHAR),

   LOG_DATE       TIMESTAMP (6) WITH TIME ZONE,

   COMMENTS       VARCHAR2 (50 CHAR),

   SQL_FULLTEXT   CLOB

);

--------------------------------------------------------

--  DDL for Procedure SESS_KILL

--------------------------------------------------------

SET DEFINE OFF;

CREATE OR REPLACE PROCEDURE "SESS_KILL" AS

--DECLARE

v_sid number;

v_serial# number;

v_username varchar2(30);

v_count number;

-- Cursor 1

    CURSOR checkuser IS

    select sid,serial#,username

    from v$session

    WHERE USERNAME='PI_USER' and STATUS = 'INACTIVE'

    AND EVENT = 'SQL*Net break/reset to client';

-- Cursor 2

    CURSOR counts IS

    select count(1) into v_count from V$SESSION

    where USERNAME = 'PI_USER' and STATUS = 'INACTIVE'

    and EVENT in ('SQL*Net break/reset to client');

-- Code to Open Cursor 2 and insert record into V$SESSION_LOG table

    BEGIN

    OPEN counts;

    LOOP

    EXIT when counts%NOTFOUND;

        IF v_count>0 THEN

            -- insert event record into log table along with current timestamp

            INSERT INTO V$SESSION_LOG (SID, SERIAL#, USERNAME, STATUS, OSUSER, EVENT, LOG_DATE, SQL_FULLTEXT)

                SELECT  a.SID, a.SERIAL#, a.USERNAME, a.STATUS, a.OSUSER, a.EVENT,

                        SYSTIMESTAMP, b.SQL_FULLTEXT

                    FROM V$SESSION a

                    LEFT OUTER JOIN V$SQL b

                    ON a.SQL_ID = b.SQL_ID

                    WHERE a.USERNAME='PI_USER'

                    AND a.STATUS = 'INACTIVE'

                    AND a.EVENT IN ('SQL*Net break/reset to client');

            ELSE

           -- Insert null record into table if event not found along with timestamp. If event found then insert 0 records using this statement                                   

                INSERT INTO V$SESSION_LOG (SID, SERIAL#, USERNAME, STATUS,

                                OSUSER, EVENT, LOG_DATE, SQL_FULLTEXT)

                        SELECT null, null, null, null, null, 'SQL*Net break/reset to client', SYSTIMESTAMP,

  null  from dual

                        WHERE NOT EXISTS (

                        SELECT  a.SID, a.SERIAL#, a.USERNAME, a.STATUS, a.OSUSER, a.EVENT,

                                SYSTIMESTAMP, b.SQL_FULLTEXT

                            FROM V$SESSION a

                            LEFT OUTER JOIN V$SQL b

                            ON a.SQL_ID = b.SQL_ID

                            WHERE a.USERNAME='PI_USER'

                            AND a.STATUS = 'INACTIVE'

                            AND a.EVENT IN ('SQL*Net break/reset to client'));

        END IF;

    END LOOP;

    CLOSE counts;

BEGIN

OPEN checkuser;

  LOOP

    FETCH checkuser into v_sid,v_serial#,v_username;

  EXIT when checkuser%NOTFOUND;

  IF v_username is not null THEN

    execute immediate 'alter system kill session '||chr(39)||v_sid||','||v_serial#||chr(39);

  END IF;

  END LOOP;

CLOSE checkuser;

END SESS_KILL;

This post has been answered by AndrewSayer on Apr 27 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 26 2016
Added on Apr 12 2016
21 comments
1,027 views