【赛迪网－it技术报道】进程相关：

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、查会话的阻塞

col user_name format a32

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;