Seems that Toad has truncate problem in sqlplus editor (sorry if this is somewhere repeated bug):
Here is query:
set pagesize 1000
set linesize 170
column since format a17
column logon format a20
column event format a25 truncate
column machine format a25 truncate
column stt format a3
column sts format a3
column username format a10 truncate
column n format 9999.99
break on machine on report skip 1
compute sum of sess_count on machine
compute sum of sess_count on report
set time on
spool client.txt append
select ‘CFROM’ tag,
to_char(sysdate,‘hh24:mi:ss’) when,
machine,
event,
seconds_in_wait sec_wait,
sql_id,
prev_sql_id,
count(*) sess_count,
to_char(sysdate - (SECONDS_IN_WAIT / (60 * 60 * 24)),‘dd.mm.yy hh24:mi:ss’) since,
– next two lines useful if trying to predict a concurrent spike. 1200 being 20 minutes
– Left over from site specific issue but could be useful.
– next is last active time + 20 minutes
– n is a count down to next predicted spike
– to_char(sysdate - ( (seconds_in_wait ) / (60 * 60 * 24)) + (1200 / (60 * 60 * 24)),‘dd-mon-yy hh24:mi:ss’) NEXT ,
– ((sysdate - (sysdate - ( (seconds_in_wait ) / (60 * 60 * 24)) + (1200 / (60 * 60 * 24)) ) ) * (60 * 60 * 24) ) / 60 n,
username,
substr(status,1,3) sts,
substr(state,1,3) stt
from v$session
where 1=1 --sid=&1
group by machine,
event,
seconds_in_wait,
sql_id,
prev_sql_id,
username,
substr(status,1,3),
substr(state,1,3)
order by machine,
username,
event,
seconds_in_wait,
sql_id,
prev_sql_id,
substr(status,1,3),
substr(state,1,3)
;
Toad output:
unknown command “sqlplus” - rest of line ignored.
unknown command “sqlplus” - rest of line ignored.
unknown command “sqlplus” - rest of line ignored.
old: select ‘CFROM’ tag,
to_char(sysdate,‘hh24:mi:ss’) when,
machine,
event,
seconds_in_wait sec_wait,
sql_id,
prev_sql_id,
count() sess_count,
to_char(sysdate - (SECONDS_IN_WAIT / (60 * 60 * 24)),‘dd.mm.yy hh24:mi:ss’) since,
– next two lines useful if trying to predict a concurrent spike. 1200 being 20 minutes
– Left over from site specific issue but could be useful.
– next is last active time + 20 minutes
– n is a count down to next predicted spike
– to_char(sysdate - ( (seconds_in_wait ) / (60 * 60 * 24)) + (1200 / (60 * 60 * 24)),‘dd-mon-yy hh24:mi:ss’) NEXT ,
– ((sysdate - (sysdate - ( (seconds_in_wait ) / (60 * 60 * 24)) + (1200 / (60 * 60 * 24)) ) ) * (60 * 60 * 24) ) / 60 n,
username,
substr(status,1,3) sts,
substr(state,1,3) stt
from v$session
where 1=1 --sid=&1
group by machine,
event,
seconds_in_wait,
sql_id,
prev_sql_id,
username,
substr(status,1,3),
substr(state,1,3)
order by machine,
username,
event,
seconds_in_wait,
sql_id,
prev_sql_id,
substr(status,1,3),
substr(state,1,3)
new: select ‘CFROM’ tag,
to_char(sysdate,‘hh24:mi:ss’) when,
machine,
event,
seconds_in_wait sec_wait,
sql_id,
prev_sql_id,
count() sess_count,
to_char(sysdate - (SECONDS_IN_WAIT / (60 * 60 * 24)),‘dd.mm.yy hh24:mi:ss’) since,
– next two lines useful if trying to predict a concurrent spike. 1200 being 20 minutes
– Left over from site specific issue but could be useful.
– next is last active time + 20 minutes
– n is a count down to next predicted spike
– to_char(sysdate - ( (seconds_in_wait ) / (60 * 60 * 24)) + (1200 / (60 * 60 * 24)),‘dd-mon-yy hh24:mi:ss’) NEXT ,
– ((sysdate - (sysdate - ( (seconds_in_wait ) / (60 * 60 * 24)) + (1200 / (60 * 60 * 24)) ) ) * (60 * 60 * 24) ) / 60 n,
username,
substr(status,1,3) sts,
substr(state,1,3) stt
from v$session
where 1=1 --sid=%
group by machine,
event,
seconds_in_wait,
sql_id,
prev_sql_id,
username,
substr(status,1,3),
substr(state,1,3)
order by machine,
username,
event,
seconds_in_wait,
sql_id,
prev_sql_id,
substr(status,1,3),
substr(state,1,3)
TAG WHEN MACHINE EVENT SEC_WAIT SQL_ID
PREV_SQL_ID SESS_COUNT SINCE USERNAME STS STT
CFROM 14:17:59 AGROKOR\A-BJOVCIC SQL*Net message from client 2270 09zkyck6mgr5s
09zkyck6mgr5s 1 20.02.14 13:40:09 SYS INA WAI
****************************************************************
----------
sum
1
…