« 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 |
||
(2 versions intermédiaires par le même utilisateur non affichées) | |||
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 == | ||
<syntaxhighlight lang="sql"> | |||
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; | |||
</syntaxhighlight><syntaxhighlight lang="sql"> | |||
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; | |||
</syntaxhighlight> | |||
== 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 89 : | Ligne 94 : | ||
and username is not null | and username is not null | ||
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
/