« Active session who wait events » : différence entre les versions
Aller à la navigation
Aller à la recherche
Aucun résumé des modifications |
Aucun résumé des modifications |
||
Ligne 84 : | Ligne 84 : | ||
== wait event == | == wait event == | ||
<syntaxhighlight lang="sql"> | |||
select a.sid,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser, | |||
substr(b.program||b.module,1,15) program,substr(b.machine,1,22) machine, | substr(b.program||b.module,1,15) program,substr(b.machine,1,22) machine, | ||
a.event,a.p1,b.sql_hash_value | a.event,a.p1,b.sql_hash_value | ||
Ligne 94 : | Ligne 95 : | ||
order by 6 | order by 6 | ||
/ | / | ||
</syntaxhighlight> | |||
[[Catégorie:Oracle]] | [[Catégorie:Oracle]] |
Dernière version du 23 mars 2023 à 15:03
set linesize 1000;
set pagesize 1000;
select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,25) box,
substr(b.username,1,25) username,
-- b.server,
substr(b.osuser,1,10) os_user,
substr(b.program,1,40) program,
to_date(b.LOGON_TIME,'DD-MM-YYYY HH24:MI:SS')
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by spid;
strong text
SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,
SID,
serial#,
MACHINE,
SES.sql_id, SES.sql_exec_start, SES.prev_exec_start ,
--REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,
SQL.SQL_TEXT STMT,
ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
|| ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
|| ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09')) RUNT
FROM V$SESSION SES,
V$SQL SQL
--V$SQLtext_with_newlines SQL
--V$SQLtext SQL
where SES.STATUS = 'ACTIVE'
and SES.USERNAME is not null
and SES.SQL_ADDRESS = SQL.ADDRESS
and SES.SQL_HASH_VALUE = SQL.HASH_VALUE
--and Ses.AUDSID <> userenv('SESSIONID')
order by runt desc, 1--,sql.piece;
version with spid/linux
SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,
SES.SID,
P.spid,
P.Program,
SES.serial#,
MACHINE,
SES.sql_id, SES.sql_exec_start, SES.prev_exec_start ,
--REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,
SQL.SQL_TEXT STMT,
ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
|| ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
|| ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09')) RUNT
FROM V$SESSION SES,
v$process p,
V$SQL SQL
--V$SQLtext_with_newlines SQL
--V$SQLtext SQL
where SES.STATUS = 'ACTIVE'
and SES.USERNAME is not null
and SES.SQL_ADDRESS = SQL.ADDRESS
and SES.SQL_HASH_VALUE = SQL.HASH_VALUE
and SES.paddr = p.addr
--and Ses.AUDSID <> userenv('SESSIONID')
order by runt desc, 1--,sql.piece;
select x.sid
,x.serial#
,x.username
,x.sql_id
,x.sql_child_number
,optimizer_mode
,hash_value
,address
,sql_text
from v$sqlarea sqlarea
,v$session x
where x.sql_hash_value = sqlarea.hash_value
and x.sql_address = sqlarea.address
and x.username is not null;
wait event
select a.sid,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser,
substr(b.program||b.module,1,15) program,substr(b.machine,1,22) machine,
a.event,a.p1,b.sql_hash_value
from v$session_wait a,V$session b
where b.sid=a.sid
and a.event not in('SQL*Net message from client','SQL*Net message to client',
'smon timer','pmon timer')
and username is not null
order by 6
/