session - 墨天轮文档

session

一天i

822

2页

2次

2020-06-25

5墨值下载

--查看会话数

select count(*) from v$session;

--查看进程数

select count(*) from v$process;

--查看数据库的并发连接数

select * from v$session where status='ACTIVE';

--查看当前数据库建立的会话

SELECT SID,SERIAL#,USERNAME,PROGRAM,MACHINE,STATUS FROM V$SESSION;

--查看数据库允许的最大连接数

SELECT value FROM V$PARAMETER WHERE NAME='processes'

--查看数据库允许的最大会话数

SELECT value FROM V$PARAMETER WHERE NAME='sessions'

--查看后台正在运行着的 sql 语句

select a.program,b.spid,c.sql_text from v$session a,v$process b,v$sqlarea c

where a.paddr=b.addr and a.sql_hash_value=c.hash_value and a.username is not

null;

查询所有数据库的连接数

select schemaname,count(*)from v$session group by schemaname;

查询终端用户使用数据库的连接情况。

select osuser,schemaname,count(*) fromv $session group by schemaname,osuser;

查看当前不为空的连接

select * from v$session where username is not null

查看不同用户的连接数

select username,count(username) from v$session where username is not null group

by username

select

substr(s.username,1,18) username,

s.sid,s.serial#,s.machine,y.sql_text

from v$session s,v$process p,v$transaction t,v$rollstat r,v$rollname n,v$sql y

where s.paddr = p.addr

and s.taddr = t.addr (+)

and t.xidusn = r.usn (+)

and r.usn = n.usn (+)

and s.username is not null

and s.sql_address=y.address

--and s.sid=56

order by s.sid,s.serial#,s.username,s.status

查询 oracle 正在执行的资源

SELECT 'Lock' "Status",

a.username "用户名", a.sid "SID", a.serial# "SERIAL#",

b.type "锁类型",

DECODE(b.lmode, 1, 'No Lock', 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share', 5,

'Share Row Exclusive', 6, 'Exclusive', 'NONE') "占用的模式",

DECODE(b.request, 1, 'No Lock', 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share',

5, 'Share Row Exclusive', 6, 'Exclusive', 'NONE') "请求的模式",

c.object_name "对象名",

c.owner "对象所有者", c.object_type "对象类型",

b.id1 "资源 ID1", b.id2 "资源 ID2",b.ctime "ctime(秒) ",

'ALTER SYSTEM KILL SESSION '''||a.sid||','||a.serial#||''';' "kill Session ",

'kill -9 '||d.spid "Kill Process (Unix Linux)",

'orakill '||f.instance_name||' '||d.spid "Kill Process (Windows)"

FROM v$session a, v$lock b, v$locked_object b1, dba_objects c, v$process d,

v$instance f

WHERE a.type <> 'BACKGROUND'

AND a.sid = b.sid

AND b.request = 0

AND d.addr = a.paddr

AND b1.session_id = a.sid

AND b1.object_id = c.object_id

AND f.status = 'OPEN'

AND f.database_status = 'ACTIVE'

order by b.ctime

of 2

5墨值下载

oracle

关注

评论