ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Administration - Session [转]

Administration - Session [转]

原创 Linux操作系统 作者:todayboy 时间:2012-01-17 15:52:19 0 删除 编辑

source:http://www.shutdownabort.com/dbaqueries/Administration_Session.php

 

Administration - Session


Show all connected users
set lines 100 pages 999
col ID format a15
select username
,      sid || ',' || serial# "ID"
,      status
,      last_call_et "Last Activity"
from   v$session
where  username is not null
order by status desc
,        last_call_et desc
/
set lines 100 pages 999 col ID format a15 select username , sid || ',' || serial# "ID" , status , last_call_et "Last Activity" from v$session where username is not null order by status desc , last_call_et desc /
Time since last user activity
set lines 100 pages 999
select username
,      floor(last_call_et / 60) "Minutes"
,      status
from   v$session
where  username is not null
order by last_call_et
/
set lines 100 pages 999 select username , floor(last_call_et / 60) "Minutes" , status from v$session where username is not null order by last_call_et /
Sessions sorted by logon time
set lines 100 pages 999
col ID		format a15
col osuser	format a15
col login_time	format a14
select 	username
,	osuser
,	sid || ',' || serial# "ID"
,	status
,	to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
,	last_call_et
from	v$session
where	username is not null
order	by login_time
/
set lines 100 pages 999 col ID format a15 col osuser format a15 col login_time format a14 select username , osuser , sid || ',' || serial# "ID" , status , to_char(logon_time, 'hh24:mi dd/mm/yy') login_time , last_call_et from v$session where username is not null order by login_time /
Show user info including os pid
col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a40
select	s.sid || ',' || s.serial# "SID/SERIAL"
,	s.username
,	s.osuser
,	p.spid "OS PID"
,	s.program
from	v$session s
,	v$process p
Where	s.paddr = p.addr
order 	by to_number(p.spid)
/
col "SID/SERIAL" format a10 col username format a15 col osuser format a15 col program format a40 select s.sid || ',' || s.serial# "SID/SERIAL" , s.username , s.osuser , p.spid "OS PID" , s.program from v$session s , v$process p Where s.paddr = p.addr order by to_number(p.spid) /
Show a users current sql
Select sql_text
from   v$sqlarea
where  (address, hash_value) in
(select sql_address, sql_hash_value 
        from v$session
        where username like '&username')
/
Select sql_text from v$sqlarea where (address, hash_value) in (select sql_address, sql_hash_value from v$session where username like '&username') /
Session status associated with the specified os process id
select	s.username
,	s.sid
,	s.serial#
,	p.spid
,	last_call_et
,	status
from 	V$SESSION s
,	V$PROCESS p
where	s.PADDR = p.ADDR
and	p.spid='&pid'
/
select s.username , s.sid , s.serial# , p.spid , last_call_et , status from V$SESSION s , V$PROCESS p where s.PADDR = p.ADDR and p.spid='&pid' /
All active sql
set feedback off
set serveroutput on size 9999
column username format a20
column sql_text format a55 word_wrapped
begin
  for x in
   (select username||'('||sid||','||serial#||') spid = '|| process ||
    ' program = ' || program username,
    to_char(LOGON_TIME,' Day HH24:MI') logon_time,
    to_char(sysdate,' Day HH24:MI') current_time,
    sql_address,
    sql_hash_value
   from v$session
   where status = 'ACTIVE'
   and rawtohex(sql_address) <> '00'
   and username is not null ) loop
   for y in (select sql_text
   from v$sqlarea
   where address = x.sql_address ) loop
   if ( y.sql_text not like '%listener.get_cmd%' and
    y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
    dbms_output.put_line( '--------------------' );
    dbms_output.put_line( x.username );
    dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
    dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
   end if;
  end loop;
 end loop;
end;
/
set feedback off set serveroutput on size 9999 column username format a20 column sql_text format a55 word_wrapped begin for x in (select username||'('||sid||','||serial#||') spid = '|| process || ' program = ' || program username, to_char(LOGON_TIME,' Day HH24:MI') logon_time, to_char(sysdate,' Day HH24:MI') current_time, sql_address, sql_hash_value from v$session where status = 'ACTIVE' and rawtohex(sql_address) <> '00' and username is not null ) loop for y in (select sql_text from v$sqlarea where address = x.sql_address ) loop if ( y.sql_text not like '%listener.get_cmd%' and y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then dbms_output.put_line( '--------------------' ); dbms_output.put_line( x.username ); dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value); dbms_output.put_line( substr( y.sql_text, 1, 250 ) ); end if; end loop; end loop; end; /
Display any long operations
set lines 100 pages 999
col username format a15
col message format a40
col remaining format 9999
select	username
,	to_char(start_time, 'hh24:mi:ss dd/mm/yy') started
,	time_remaining remaining
,	message
from	v$session_longops
where	time_remaining = 0
order by time_remaining desc
/
set lines 100 pages 999 col username format a15 col message format a40 col remaining format 9999 select username , to_char(start_time, 'hh24:mi:ss dd/mm/yy') started , time_remaining remaining , message from v$session_longops where time_remaining = 0 order by time_remaining desc /
List open cursors per user
set pages 999
select 	sess.username
,	sess.sid
,	sess.serial#
,	stat.value cursors
from	v$sesstat stat
,	v$statname sn
,	v$session sess
where	sess.username is not null
and	sess.sid = stat.sid
and	stat.statistic# = sn.statistic#
and	sn.name = 'opened cursors current'
order by value
/
set pages 999 select sess.username , sess.sid , sess.serial# , stat.value cursors from v$sesstat stat , v$statname sn , v$session sess where sess.username is not null and sess.sid = stat.sid and stat.statistic# = sn.statistic# and sn.name = 'opened cursors current' order by value / Or alternatively...
set lines 100 pages 999
select count(hash_value) cursors
,      sid
,      user_name
from   v$open_cursor
group by 
       sid
,      user_name
order by 
       cursors
/
set lines 100 pages 999 select count(hash_value) cursors , sid , user_name from v$open_cursor group by sid , user_name order by cursors /

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7583803/viewspace-715020/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 大表分区
请登录后发表评论 登录
全部评论

注册时间:2009-02-24

  • 博文量
    75
  • 访问量
    243128