« Active session who wait events » : différence entre les versions
Aller à la navigation
Aller à la recherche
Page créée avec « <code>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... » |
Aucun résumé des modifications |
||
Ligne 1 : | Ligne 1 : | ||
<syntaxhighlight lang="sql"> | |||
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' | |||
'''strong text''' | order by spid; | ||
</syntaxhighlight>'''strong text'''<syntaxhighlight lang="sql"> | |||
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; | |||
</syntaxhighlight> | |||
== version with spid/linux == | == version with spid/linux == | ||
Ligne 64 : | Ligne 66 : | ||
<nowiki> </nowiki>order by runt desc, 1--,sql.piece;</code> | <nowiki> </nowiki>order by runt desc, 1--,sql.piece;</code> | ||
< | <select x.sid | ||
,x.serial# | ,x.serial# | ||
,x.username | ,x.username | ||
Ligne 80 : | Ligne 82 : | ||
== wait event == | == 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, | 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 |
Version du 23 mars 2023 à 14:59
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 /