Re: SQL Execution status check

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 06 Oct 1998 13:25:30 GMT
Message-ID: <361a18dc.3205799@192.86.155.100>

A copy of this was sent to ritu_at_orion.3dcube.com (Ritu Raj) (if that email address didn't require changing) On 6 Oct 1998 04:59:00 GMT, you wrote:

>
>Hello everyone,
>
>I am running Oracle 7.3.2 on a Sequent/ptx environment. Is there any
>way to check what SQLs are being executed on the database server from
>UNIX or SQL command line?
>
>Thanks for any help in this area.

I like to use the following sh script. It shows who is logged in and what if any SQL they are currently executing (filters out SQL from Context and 'plex')...

• bof ------------------------------------------ #!/bin/sh

if [ "$1" = "" ]
then

    cat << EOF
usage: showsql un/pw

example: showsql scott/tiger

description: shows current sessions and what sql they are executing EOF
    exit
fi

sqlplus -s $1 <<EOF
column status format a10
set feedback off
set serveroutput on

select username, sid, serial#, process, status from v\$session
where username is not null
/

column username format a20
column sql_text format a55 word_wrapped

begin

    for x in
    ( select username||'('||sid||','||serial#||') ospid = ' || process || ' pro gram = ' || program username,

     to_char(LOGON_TIME,' Day HH24:MI') logon_time,
     to_char(sysdate,' Day HH24:MI') current_time,
             sql_address
        from v\$session
       where status = 'ACTIVE'
         and rawtohex(sql_address) <> '00'
         and username is not null ) loop
        for y in ( select sql_text
                     from v\$sqlarea
                    where address = x.sql_address ) loop
            if ( y.sql_text not like '%listener.get_cmd%' and
                 y.sql_text not like '%plex.accept_client%' and
                 y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
                dbms_output.put_line( '--------------------' );
                dbms_output.put_line( x.username );
                dbms_output.put_line( x.logon_time || ' ' || x.current_time);
                dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
            end if;
        end loop;

    end loop;
end;
/

column username format a15 word_wrapped column module format a15 word_wrapped
column action format a15 word_wrapped
column client_info format a30 word_wrapped

select username||'('||sid||','||serial#||')' username,

       module,
       action,
       client_info

from v\$session
where module is not null;
EOF

• eof --------------------------------------

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Oct 06 1998 - 08:25:30 CDT