You Asked
I have taken a subset of your showsql.sql, made a couple of changes, including your "p" procedure, hoping to get it to capture the whole SQL statement.
But it seems like I am hitting a limit, even after substringing 250 bytes chunks off and printing them. It will only display the SQL statement out to about 2000 bytes, or so.
Can you tell me if I can extend this in any way to get more output?
SET feedback OFF
set serveroutput on size 1000000
set autotrace OFF
set pagesize 40
set linesize 140
set echo OFF
column status format a10
column username format a15
column logon_time format a12
column username format a20
column sql_text format a55 word_wrapped
DECLARE
x NUMBER;
LONGTEXT VARCHAR2(32767);
BEGIN
FOR x IN
( SELECT username||'('||sid||','||serial#||') ospid=' ||
process || ' ' || 'osuser=' || osuser ||
' program=' || decode(nvl(program,' '),' ','?',program) Username,
to_char(LOGON_TIME,' Dy-HH24:MI') Logon_Time,
to_char(sysdate,' Dy-HH24:MI') Current_Time,
sql_address,
'Last_Call_ET=' || LAST_CALL_ET Last_Call_ET,
'Min=' || to_char(LAST_CALL_ET / 60,'FM9999.9') Last_Min
FROM v$session
WHERE status = 'ACTIVE'
AND rawtohex(sql_address) <> '00'
AND username IS NOT NULL
ORDER BY last_call_et )
LOOP
FOR y IN ( SELECT Sql_Text
FROM v$sqlarea
WHERE address = x.sql_address)
LOOP
IF ( y.sql_text not like '%LAST_CALL_ET%')
THEN
DBMS_OUTPUT.PUT_LINE( '--------------------' );
DBMS_OUTPUT.PUT_LINE( x.username || ' ' ||
x.logon_time || ' ' ||
x.current_time || ' ' ||
x.last_call_et || ' ' ||
x.last_min);
LONGTEXT := y.sql_text;
BEGIN
LOOP
EXIT WHEN LONGTEXT IS NULL;
DBMS_OUTPUT.PUT_LINE( SUBSTR( LONGTEXT, 1, 250 ) );
LONGTEXT := SUBSTR( LONGTEXT, 251 );
END LOOP;
DBMS_OUTPUT.PUT_LINE( SUBSTR( LONGTEXT, 1, 250 ) );
END;
END IF;
END LOOP;
END LOOP;
END;
/
and Tom said...
you have to use a different v$ view, v$sqlarea only has so much. Here is my most recent incarnation of this:
column username format a15 word_wrapped
column module format a15 word_wrapped
column action format a15 word_wrapped
column client_info format a15 word_wrapped
column status format a10
column sid_serial format a15
set feedback off
set serveroutput on
set feedback on
select username, ''''||sid||','||serial#||'''' sid_serial, status , module, action, client_info,
last_call_et
from v$session
where username is not null
/
set feedback off
column username format a20
column sql_text format a55 word_wrapped
set serveroutput on size 1000000
declare
x number;
pid number;
procedure p ( p_str in varchar2 )
is
l_str long := p_str;
begin
loop
exit when l_str is null;
dbms_output.put_line( substr( l_str, 1, 250 ) );
l_str := substr( l_str, 251 );
end loop;
end;
begin
for x in
( select username||'('||sid||','||serial#||
') ospid = ' || process ||
' command = ' || command ||
' 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, sid, paddr
from v$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null order by last_call_et )
loop
select spid into pid from v$process where addr = x.paddr;
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username || ' dedicated server=' || pid );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
for y in ( select sql_text
from v$sqltext_with_newlines
where address = x.sql_address
order by piece )
loop
p( y.sql_text );
end loop;
end loop;
end;
/
set feedback on
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment