q_sessions

admin · 发表于 2020-9-11 15:16:10

--查询当前会话设置最大参数和当前连接数，使用率
SET TERMOUT OFF
SET ECHO OFF
SET FEEDBACK ON
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 2000
SET TIMING OFF
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS BREAKS COMPUTES
COLUMN max_sess_allowed FORMAT 9,999,999 HEADING 'Max sessions allowed'
COLUMN num_sessions FORMAT 9,999,999,999 HEADING 'Number of sessions'
COLUMN pct_utl FORMAT a19 HEADING 'Percent Utilization'
COLUMN username FORMAT a15 HEADING 'Oracle User'
COLUMN num_user_sess FORMAT 9,999,999 HEADING 'Number of Logins'
COLUMN count_a FORMAT 9,999,999 HEADING 'Active Logins'
COLUMN count_i FORMAT 9,999,999 HEADING 'Inactive Logins'
SELECT
TO_NUMBER(a.value) max_sess_allowed
, TO_NUMBER(count(*)) num_sessions
, LPAD(ROUND((count(*)/a.value)*100,0) || '%', 19) pct_utl
FROM
v$session b
, v$parameter a
WHERE
a.name = 'sessions'
GROUP BY
a.value;
<font color="Red">--查询当前会话详细信息，以type分类汇总</font>
BREAK on report
COMPUTE sum OF num_user_sess count_a count_i ON report
SELECT
lpad(nvl(sess.username, '[B.G. Process]'), 15) username
, count(*) num_user_sess
, nvl(act.count, 0) count_a
, nvl(inact.count, 0) count_i
FROM
v$session sess
, (SELECT count(*) count, nvl(username, '[B.G. Process]') username
FROM v$session
WHERE status = 'ACTIVE'
GROUP BY username) act
, (SELECT count(*) count, nvl(username, '[B.G. Process]') username
FROM v$session
WHERE status = 'INACTIVE'
GROUP BY username) inact
WHERE
nvl(sess.username, '[B.G. Process]') = act.username (+)
and nvl(sess.username, '[B.G. Process]') = inact.username (+)
GROUP BY
sess.username
, act.count
, inact.count
/
--查询当前会话详细信息，以用户分类的汇总
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN instance_name FORMAT a8 HEADING 'Instance'
COLUMN sid FORMAT 999999 HEADING 'SID'
COLUMN serial_id FORMAT 99999999 HEADING 'Serial ID'
COLUMN session_status FORMAT a9 HEADING 'Status'
COLUMN oracle_username FORMAT a18 HEADING 'Oracle User'
COLUMN os_username FORMAT a18 HEADING 'O/S User'
COLUMN os_pid FORMAT a8 HEADING 'O/S PID'
COLUMN session_terminal FORMAT a10 HEADING 'Terminal' TRUNC
COLUMN session_machine FORMAT a30 HEADING 'Machine' TRUNC
COLUMN session_program FORMAT a40 HEADING 'Session Program' TRUNC
BREAK ON instance_name SKIP PAGE
SELECT
i.instance_name instance_name
, s.sid sid
, s.serial# serial_id
, s.status session_status
, s.username oracle_username
, s.osuser os_username
, p.spid os_pid
, s.terminal session_terminal
, s.machine session_machine
, s.program session_program
FROM
gv$session s
INNER JOIN gv$process p ON (s.paddr = p.addr AND s.inst_id = p.inst_id)
INNER JOIN gv$instance i ON (p.inst_id = i.inst_id)
WHERE
s.status = 'ACTIVE'
AND s.username IS NOT null
ORDER BY
i.instance_name
, s.sid;
--按功能排序
select inst_id,count(*) from gv$session where type='USER' group by inst_id;
select machine,count(*) from gv$session where type='USER' group by machine;
select program,count(*) from gv$session where type='USER' group by program;
select username,count(*) from gv$session where type='USER' group by username;
--僵尸进程
select p.spid from v$process p where not exists(select spid from v$session where paddr=p.addr);
--杀掉符合定义的会话
select 'alter system kill session ''' || sid || ',' || serial# || ''';'
from v$session
where sid in
(select sid from v$session where sql_id='3d8tczpt1v7vj' or sql_id='ajvc4f9ccyzbx')
/
select 'alter system kill session ''' || sid || ',' || serial# || ''';'
from v$session
where sid in
(select sid from v$session where event='direct path read' and username='SCYW')
/
select spid from v$process
where addr in
(select paddr from v$session where username='ODS_DD_PDWYXJC')
/
--根据pid找出该pid的session信息和sql信息
select se.username, se.machine, sq.cpu_time, sq.sql_text
from v$process p, v$session se, v$sqlarea sq
where p.addr = se.PADDR
and se.sql_hash_value = sq.hash_value
and p.addr = '&pid'
/
SELECT
s.sid sid
, s.serial# serial_id
, s.status session_status
, s.username oracle_username
, s.osuser os_username
, p.spid os_pid
, s.program session_program
FROM
v$process p
, v$session s
WHERE
p.addr (+) = s.paddr
and s.username='&uname'
ORDER BY
sid
/
<font color="Red">--当前用户的会话信息</font>
set serveroutput on
set linesize 200 pagesize 1400
declare
l_audsid number;
l_sid number;
l_serial number;
l_module varchar2(128);
l_pid number;
l_spid number;
l_trace varchar2(2000);
l_user varchar2(128);
l_machine varchar2(128);
begin
DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'Teapot_Trace_Purpose',
action_name => 'ACTIVE');
select audsid, sid, SERIAL#, module,username,machine
into l_audsid, l_sid, l_serial, l_module,l_user,l_machine
from v$session
where sid = (select distinct sid from v$mystat);
select pid, spid
into l_pid, l_spid
from v$process
where addr = (select paddr
from v$session
where sid = l_sid
and serial# = l_serial);
SELECT d.VALUE || '/' || LOWER(RTRIM(i.INSTANCE, CHR(0))) || '_ora_' ||
p.spid || '.trc'
into l_trace
FROM (SELECT p.spid
FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = 1
AND s.SID = m.SID
AND p.addr = s.paddr) p,
(SELECT t.INSTANCE
FROM v$thread t, v$parameter v
WHERE v.NAME = 'thread'
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER(v.VALUE))) i,
(SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;
dbms_output.enable(9999999);
dbms_output.put_line('===================USER ENV=======================');
dbms_output.put_line(' USERNAME=' || l_user);
dbms_output.put_line(' SID=' || l_sid || ' SERIAL#=' || l_serial);
dbms_output.put_line(' AUDSID=' || l_audsid || ' MODULE#=' || l_module);
dbms_output.put_line(' PID=' || l_pid || ' SPID#=' || l_spid);
dbms_output.put_line(' USER_MACHINE=' || l_machine);
dbms_output.put_line(' TRACE_FILE_LOCATION=' || l_trace);
dbms_output.put_line('==================================================');
commit;
end;
/
--杀会话
select 'alter system kill session ''' || sid || ',' || serial# || ''';'
from v$session
where username='MCTP';
set pagesize 2000
SELECT '! kill -9 '||p.spid FROM v$session s INNER JOIN v$process p ON (s.paddr = p.addr) WHERE s.username ='MCTP' and s.status='INACTIVE';
--当前连接信息
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN instance_name FORMAT a8 HEADING 'Instance'
COLUMN thread# FORMAT 999999 HEADING 'Thread#'
COLUMN cur_processes FORMAT 999999 HEADING 'Cur_Processes'
COLUMN cfg_value FORMAT a15 HEADING 'Cfg_Value'
COLUMN pct_usage FORMAT a18 HEADING 'Pct_Usage'
SELECT
a.instance_name
, a.thread#
, TO_CHAR(a.count) cur_processes
, b.value cfg_value
, TO_CHAR(ROUND(100*(a.count / b.value), 2)) pct_usage
FROM
(select count(*) count, a1.inst_id, a2.instance_name, a2.thread#
from gv$session a1
, gv$instance a2
where a1.inst_id = a2.inst_id
group by a1.inst_id
, a2.instance_name
, a2.thread#) a
, (select value, inst_id from gv$parameter where name='processes') b
WHERE
a.inst_id = b.inst_id
ORDER BY
a.instance_name;

复制代码

		自动登录	找回密码
密码			立即注册

[日常管理] q_sessions