数据库连接情况查询相关sql语句

--将口令有效期默认值180天修改成“无限制”
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

--查询用户对应表空间
select username,default_tablespace from dba_users where username='BXCWGZ';

--查询数据库当前进程的连接数：

select count(*) from v$process;

select * from v$process;

--查看数据库当前会话的连接数：

select count(*) from v$session;

--查询版本
select * from v$version;

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

select count(*) 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 schemaname,count(*)from v$session group by schemaname;

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

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

--查看当前不为空的连接

select * from v$session where username is not null

--清理无效会话
select 'alter system disconnect session ''' ||sid || ', ' || serial# || '''' || ' immediate;' from v$session S where S.last_call_et >= 7200 and S.STATUS = 'INACTIVE';

alter system disconnect session 'sid , serial' immediate;

--查看不同用户的连接数

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

SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
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

--
SELECT s.username,
s.status,
s.machine,
osuser,
spid,
TO_CHAR(logon_time, 'dd/mm/yyyy hh24:mi:ss') logon_time,
last_call_et idle_time,
TO_CHAR(TRUNC(last_call_et / 3600, 0)) || ' ' || ' HRS ' ||
TO_CHAR(TRUNC((last_call_et - TRUNC(last_call_et / 3600, 0) * 3600) / 60,
0)) || ' MINS' idle_time_hour_minute,
module
FROM v$session s, v$process p
WHERE TYPE = 'USER'
-- and s.USERNAME='用户名'
AND p.addr = s.paddr
ORDER BY last_call_et desc;

--command execute
show parameter processes;
show parameter sessions;
--sessions=(1.1*processes+5)

--游标统计
SELECT MAX(A.VALUE) AS HIGHEST_OPEN_CUR, P.VALUE AS MAX_OPEN_CUR
FROM V$SESSTAT A, V$STATNAME B, V$PARAMETER P
WHERE A.STATISTIC# = B.STATISTIC#
AND B.NAME = 'opened cursors current'
AND P.NAME = 'open_cursors'
GROUP BY P.VALUE;

--漏出游标
SELECT A.VALUE, S.USERNAME, S.SID, S.SERIAL#
FROM V$SESSTAT A, V$STATNAME B, V$SESSION S
WHERE A.STATISTIC# = B.STATISTIC#
AND S.SID = A.SID
AND B.NAME = 'opened cursors curent';

--游标信息
--session cursor cache hits就是系统在高速缓存区中找到相应cursors的次数,
--parse count(total)就是总的解析次数，二者比值越高，性能越好
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%cursor%';
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%parse%';

--'session_cached_cursors' 的使用情况
SELECT 'session_cached_cursors' PARAMETER,
LPAD(VALUE, 5) VALUE,
DECODE(VALUE, 0, ' n/a', TO_CHAR(100 * USED / VALUE, '990') || '%') USAGE
FROM (SELECT MAX(S.VALUE) USED
FROM V$STATNAME N, V$SESSTAT S
WHERE N.NAME = 'session cursor cache count'
AND S.STATISTIC# = N.STATISTIC#),
(SELECT VALUE FROM V$PARAMETER WHERE NAME = 'session_cached_cursors')
UNION ALL
SELECT 'open_cursors',
LPAD(VALUE, 5),
TO_CHAR(100 * USED / VALUE, '990') || '%'
FROM (SELECT MAX(SUM(S.VALUE)) USED
FROM V$STATNAME N, V$SESSTAT S
WHERE N.NAME IN
('opened cursors current', 'session cursor cache count')
AND S.STATISTIC# = N.STATISTIC#
GROUP BY S.SID),
(SELECT VALUE FROM V$PARAMETER WHERE NAME = 'open_cursors');

--查看session级的等待事件
select s.username, s.program, s.status, se.event, se.total_waits, se.total_timeouts, se.time_waited, se.average_wait
from v$session s, v$session_event se
where s.sid = se.sid
and se.event not like 'SQl*Net%'
and s.status = 'ACTIVE'
and s.username is not null

--查看占用系统io较大的session
select se.sid, se.serial#, pr.spid, se.username, se.status, se.terminal, se.program, se.module, se.sql_address, st.event,
st.p1text, si.physical_reads, si.block_changes
from v$session se, v$session_wait st, v$sess_io si, v$process pr
where st.sid = se.sid
and st.sid = si.sid
and se.paddr = pr.addr
and se.sid > 6
and st.wait_time = 0
and st.event not like '%SQL%'
order by physical_reads desc

--找出耗cpu较多的session
select a.sid, spid, status, substr(a.program, 1, 40) prog, a.terminal, osuser, value / 60 / 100 value
from v$session a, v$process b, v$sesstat c
where c.statistic# = 12
and c.sid = a.sid
and a.paddr = b.addr
order by value desc

--oracle中查询被锁的表并释放session
select a.owner, a.object_name, b.xidusn, b.xidslot, b.xidsqn, b.session_id, b.oracle_username, b.os_user_name, b.process,
b.locked_mode, c.machine, c.status, c.server, c.sid, c.serial#, c.program
from all_objects a, v$locked_object b, sys.gv_$session c
where (a.object_id = b.object_id)
and (b.process = c.process)
order by 1
/*
alter system killsession'sid,serial#'
alter systemkillsession'379,21132'
alter systemkillsession'374,6938'
*/

--查询锁表、锁表用户信息并删除
select * from v$locked_object;
SELECT sid, serial#, username, osuser FROM v$session where sid = 25;
ALTER SYSTEM KILL SESSION '25,393';
--或者
alter user fair_value account unlock;