Code:
C:\WINNT\system32>sqlplus
SQL*Plus: Release 9.2.0.3.0 - Production on Tue Sep 30 21:29:27 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name: appdev@nick817
Enter password:
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
With the Partitioning option
JServer Release 8.1.7.4.1 - Production
[email protected]L> column status format a10
[email protected]L> set feedback off
[email protected]L> set serveroutput on
[email protected]L>
[email protected]L> select username, sid, serial#, process, status
2 from v$session
3 where username is not null
4 /
USERNAME SID SERIAL# PROCESS STATUS
------------------------------ ---------- ---------- --------- ----------
SCOTT 8 31 3548:1896 INACTIVE
APPDEV 9 23 964:1776 ACTIVE
[email protected]L>
[email protected]L> column username format a20
[email protected]L> column sql_text format a55 word_wrapped
[email protected]L>
[email protected]L> set serveroutput on size 1000000
[email protected]L> declare
2 x number;
3 begin
4 for x in
5 ( select username||'('||sid||','||serial#||
6 ') ospid = ' || process ||
7 ' program = ' || program username,
8 to_char(LOGON_TIME,' Day HH24:MI') logon_time,
9 to_char(sysdate,' Day HH24:MI') current_time,
10 sql_address, LAST_CALL_ET
11 from v$session
12 where status = 'ACTIVE'
13 and rawtohex(sql_address) <> '00'
14 and username is not null order by last_call_et )
15 loop
16 for y in ( select max(decode(piece,0,sql_text,null)) ||
17 max(decode(piece,1,sql_text,null)) ||
18 max(decode(piece,2,sql_text,null)) ||
19 max(decode(piece,3,sql_text,null))
20 sql_text
21 from v$sqltext_with_newlines
22 where address = x.sql_address
23 and piece < 4)
24 loop
25 if ( y.sql_text not like '%listener.get_cmd%' and
26 y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
27 then
28 dbms_output.put_line( '--------------------' );
29 dbms_output.put_line( x.username );
30 dbms_output.put_line( x.logon_time || ' ' ||
31 x.current_time||
32 ' last et = ' ||
33 x.LAST_CALL_ET);
34 dbms_output.put_line(
35 substr( y.sql_text, 1, 250 ) );
36 end if;
37 end loop;
38 end loop;
39 end;
40 /
--------------------
APPDEV(9,23) ospid = 964:1776 program = sqlplus.exe
Tuesday 21:29 Tuesday 21:29 last et = 0
SELECT USERNAME || '(' || SID || ',' || SERIAL# || ') ospid = ' || PROCESS || ' program = '
|| PROGRAM USERNAME,TO_CHAR(LOGON_TIME,' Day HH24:MI') LOGON_TIME,TO_CHAR(SYSDATE,' Day HH24:MI')
CURRENT_TIME,SQL_ADDRESS,LAST_CALL_ET FROM V$SESSI
[email protected]L>
[email protected]L>
[email protected]L>
[email protected]L> /
--------------------
APPDEV(9,23) ospid = 964:1776 program = sqlplus.exe
Tuesday 21:29 Tuesday 21:30 last et = 0
SELECT USERNAME || '(' || SID || ',' || SERIAL# || ') ospid = ' || PROCESS || ' program = '
|| PROGRAM USERNAME,TO_CHAR(LOGON_TIME,' Day HH24:MI') LOGON_TIME,TO_CHAR(SYSDATE,' Day HH24:MI')
CURRENT_TIME,SQL_ADDRESS,LAST_CALL_ET FROM V$SESSI
[email protected]L>
[email protected]L>
[email protected]L> select username||'('||sid||','||serial#||')' username,
2 module,
3 action,
4 client_info
5 from v$session
6 where module||action||client_info is not null;
USERNAME MODULE
-------------------- ------------------------------------------------
ACTION CLIENT_INFO
-------------------------------- ----------------------------------------------------------------
SCOTT(8,31) SQL*Plus
APPDEV(9,23) SQL*Plus
Reply With Quote