Oracle进程相关的SQL脚本

werew

1、 求当前会话的SID，SERIAL#


select sid, serial# from v$session where audsid = sys_context('userenv', 'sessionid');

2、 查询session的OS进程ID


select p.spid "os thread", b.name "name-user", s.program, s.sid, s.serial#,s.osuser, s.machine

from v$process p, v$session s, v$bgprocess b

where p.addr = s.paddr  
and p.addr = b.paddr and (s.sid=&1 or p.spid=&1)  
union all  
select p.spid "os thread", s.username "name-user", s.program, s.sid,s.serial#, s.osuser, s.machine

from v$process p, v$session s

where p.addr = s.paddr  
and (s.sid=&1 or p.spid=&1)  
and s.username is not null;

3、根据sid查看对应连接正在运行的sql


select /*+ push_subq */  
command_type, sql_text, sharable_mem, persistent_mem, runtime_mem, sorts,  
version_count, loaded_versions, open_versions, users_opening, executions,  
users_executing, loads, first_load_time, invalidations, parse_calls,  
disk_reads, buffer_gets, rows_processed, sysdate start_time,  
sysdate finish_time, '>' || address sql_address, 'n' status  
from v$sqlarea  
where address = (select sql_address  
                 from v$session  
                 where sid = &sid );

4、查找object为哪些进程所用


select p.spid, s.sid, s.serial# serial_num, s.username user_name,a.type object_type, s.osuser os_user_name, a.owner,a.object object_name,  
decode(sign(48 - command), 1, to_char(command), 'action code #' || to_char(command)) action,  
p.program oracle_process, s.terminal terminal, s.program program,  
s.status session_status  
from v$session s, v$access a, v$process p  
where s.paddr = p.addr  
and s.type = 'user'  
and a.sid = s.sid  
and a.object = '&obj'  
order by s.username, s.osuser;

5、查看有哪些用户连接


select s.osuser os_user_name,  
decode(sign(48 - command),1,to_char(command),  
'action code #' || to_char(command)) action,  
p.program oracle_process, status session_status, s.terminal terminal,  
s.program program, s.username user_name,  
s.fixed_table_sequence activity_meter, '' query, 0 memory,  
0 max_memory, 0 cpu_usage, s.sid, s.serial# serial_num  
from v$session s, v$process p  
where s.paddr = p.addr  
and s.type = 'user'  
order by s.username, s.osuser

6、根据v.sid查看对应连接的资源占用等情况


select n.name, v.value, n.class, n.statistic#  
from v$statname n, v$sesstat v  
where v.sid = &sid  
and v.statistic# = n.statistic#  
order by n.class, n.statistic#

7、查询耗资源的进程（top session）


select s.schemaname schema_name,  
decode(sign(48 - command),  
1, to_char(command), 'action code #' || to_char(command)) action,  
status session_status, s.osuser os_user_name, s.sid, p.spid,  
s.serial# serial_num, nvl(s.username, '[oracle process]') user_name,  
s.terminal terminal, s.program program, st.value criteria_value  
from v$sesstat st, v$session s, v$process p  
where st.sid = s.sid  
and st.statistic# = to_number('38')  
and ('all' = 'all' or s.status = 'all')  
and p.addr = s.paddr  
order by st.value desc, p.spid asc, s.username asc, s.osuser asc;

8、查看锁（lock）情况

select /*+ rule */ls.osuser os_user_name, ls.username user_name,  
decode(ls.type,  
'rw', 'row wait enqueue lock', 'tm', 'dml enqueue lock',  
'tx', 'transaction enqueue lock', 'ul', 'user supplied lock')   
lock_type,o.object_name object,  
decode(ls.lmode,  
1, null, 2, 'row share', 3, 'row exclusive',  
4, 'share', 5, 'share row exclusive', 6, 'exclusive',null)   
lock_mode,o.owner, ls.sid, ls.serial# serial_num, ls.id1, ls.id2  
from sys.dba_objects o,  
(select s.osuser, s.username, l.type, l.lmode, s.sid, s.serial#, l.id1,l.id2  
from v$session s, v$lock l  
where s.sid = l.sid) ls  
where o.object_id = ls.id1  
and o.owner <> 'sys'  
order by o.owner, o.object_name;




9、查看等待（wait）情况


select ws.class, ws.count count, sum(ss.value) sum_value  
from v$waitstat ws, v$sysstat ss  
where ss.name in ('db block gets', 'consistent gets')  
group by ws.class, ws.count;




10、求process/session的状态


select p.pid, p.spid, s.program, s.sid, s.serial# from v$process p, v$session s where s.paddr = p.addr;

11、求谁阻塞了某个session(10g)


select sid, username, event, blocking_session, seconds_in_wait, wait_time from v$session where state in ('waiting') and wait_class != 'idle';

12、查会话的阻塞


select /*+ rule */lpad(' ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username user_name,o.owner, o.object_name, s.sid, s.serial#  
from v$locked_object l, dba_objects o, v$session s  
where l.object_id = o.object_id  
and l.session_id = s.sid  
order by o.object_id, xidusn desc;

col username format a15  
col lock_level format a8  
col owner format a18  
col object_name format a32  
select /*+ rule */s.username,decode(l.type, 'tm', 'table lock', 'tx', 'row lock', null) lock_level,o.owner, o.object_name, s.sid, s.serial#  
from v$session s, v$lock l, dba_objects o  
where l.sid = s.sid  
and l.id1 = o.object_id(+)  
and s.username is not null;

13、求等待的事件及会话信息/求会话的等待及会话信息


select se.sid, s.username, se.event, se.total_waits, se.time_waited,se.average_wait  
from v$session s, v$session_event se  
where s.username is not null  
and se.sid = s.sid  
and s.status = 'active'  
and se.event not like '%sql*net%'  
order by s.username;

select s.sid, s.username, sw.event, sw.wait_time, sw.state,sw.seconds_in_wait  
from v$session s, v$session_wait sw  
where s.username is not null  
and sw.sid = s.sid  
and sw.event not like '%sql*net%'  
order by s.username;

14、求会话等待的file_id/block_id


col event format a24  
col p1text format a12  
col p2text format a12  
col p3text format a12  
select sid, event, p1text, p1, p2text, p2, p3text, p3  
from v$session_wait  
where event not like '%sql%'  
and event not like '%rdbms%'  
and event not like '%mon%'  
order by event;

select name, wait_time  
from v$latch l  
where exists (select 1  
from (select sid, event, p1text, p1, p2text, p2, p3text, p3  
from v$session_wait  
where event not like '%sql%'  
and event not like '%rdbms%'  
and event not like '%mon%') x  
where x.p1 = l.latch#);

15、求会话等待的对象


col owner format a18  
col segment_name format a32  
col segment_type format a32  
select owner, segment_name, segment_type  
from dba_extents  
where file_id = &file_id  
and &block_id between block_id and block_id + blocks - 1;

16、求出某个进程，并对它进行跟踪


select s.sid, s.serial#  
from v$session s, v$process p  
where s.paddr = p.addr  
and p.spid = &1;  
exec dbms_system.set_sql_trace_in_session(&1, &2, true);  
exec dbms_system.set_sql_trace_in_session(&1, &2, false);

17、求当前session的跟踪文件


select p1.value || '/' || p2.value || '_ora_' || p.spid || '.ora' filename   
from v$process p, v$session s, v$parameter p1, v$parameter p2  
where p1.name = 'user_dump_dest'  
and p2.name = 'instance_name'  
and p.addr = s.paddr  
and s.audsid = userenv('sessionid')  
and p.background is null  
and instr(p.program, 'cjq') = 0;




18、求出锁定的对象


select do.object_name, session_id, process, locked_mode from v$locked_object lo, dba_objects do where lo.object_id = do.object_id;