--杀锁表会话
SELECT OBJECT_NAME, MACHINE, 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;
--alter system kill session '138,22031';
--查询本机会话号
SELECT * FROM V$MYSTAT WHERE ROWNUM <=1
--查询会话号、主机名
select sid,SERIAL#,machine from v$session ;
--当前登陆的用户的终端IP
select SYS_CONTEXT('USERENV','IP_ADDRESS') from dual;
--通过会话号(sid)查找sql语句
select sql_text
from v$session s,v$sqltext t
where t.address=s.sql_address AND s.sid=118
order by t.piece
--查看当前会话的SID，SERIAL
SELECT Sid, Serial# FROM V$session WHERE Audsid = Sys_Context('USERENV',
'SESSIONID');
--查看数据库锁
select https://www.doczj.com/doc/5d2096611.html,ername,a.osuser,a.sid,a.serial#,b.spid
from v$session a,v$process b
where a.paddr=b.addr and https://www.doczj.com/doc/5d2096611.html,ername is not null;
--用下面的语句查询产生阻塞的会话
SELECT *
FROM V$LOCK
WHERE TYPE = 'TX'
AND LMODE > 0
AND BLOCK = 1;
--监控当前数据库谁在运行什么SQL语句
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
--查看表空间使用率
SELECT d.tablespace_name "Name", d.status "Status",
TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99,999,990.900') "Size (M)",
TO_CHAR (NVL (a.BYTES - NVL (f.BYTES, 0), 0) / 1024 / 1024,
'99999999.999'
) USE,
TO_CHAR (NVL ((a.BYTES - NVL (f.BYTES, 0)) / a.BYTES * 100, 0),
'990.00'
) "Used %"
FROM SYS.dba_tablespaces d,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY')
UNION ALL
SELECT d.tablespace_name "Name", d.status "Status",
TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99,999,990.900') "Size (M)",
TO_CHAR (NVL (t.BYTES, 0) / 1024 / 1024, '99999999.999') USE,
TO_CHAR (NVL (t.BYTES / a.BYTES * 100, 0), '990.00') "Used %"
FROM SYS.dba_tablespaces d,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_temp_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (bytes_cached) BYTES
FROM v$temp_extent_pool
GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management LIKE 'LOCAL'
AND d.CONTENTS LIKE 'TEMPORARY';
--根据SID查询具体的SQL语句：
SELECT A.SQL_FULLTEXT
FROM V$SQLAREA A, V$SQL B, V$SESSION S
WHERE A.SQL_ID = B.SQL_ID
AND B.ADDRESS = S.SQL_ADDRESS
AND S.SID = &VSID;
--
通过会话id查找进程号
select * from v$session a,v$process b where a.paddr=b.addr ;
--查看数据库锁
SELECT DECODE (l.request, 0, 'Holder: ', 'Waiter: ') ROLE, https://www.doczj.com/doc/5d2096611.html,ername,
p.spid, l.inst_id, l.SID, v.serial#, l.id1, l.id2, l.lmode,
l.request, l.TYPE
FROM gv$session v, gv$lock l, gv$process p
WHERE (l.id1, l.id2, l.TYPE) IN (SELECT id1, id2, TYPE
FROM gv$lock
WHERE request > 0)
AND v.inst_id = p.inst_id
AND v.inst_id = l.inst_id
AND v.SID = l.SID
AND v.paddr = p.addr
ORDER BY l.id1, l.request;
--查看数据库中目前有哪些锁，如果某个进程被别的进程阻塞了，在"blocked_by_session"列上有阻塞它的进程的sid。
SELECT A.SESSION_ID,
C.SERIAL#,
B.OBJECT_NAME,
DECODE(A.LOCKED_MODE,
0, 'None',
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S/Row-X',
6, 'Exclusive') LOCK_MODE,
D.BLOCKED_BY_SESSION,
A.ORACLE_USERNAME,
A.OS_USER_NAME,
C.MACHINE,
C.PROGRAM
FROM V$LOCKED_OBJECT A,
DBA_OBJECTS B,
V$SESSION C,
(SELECT B.SESSION_ID BLOCKED,
B.BLOCKING_OTHERS,
A.SESSION_ID BLOCKED_BY_SESSION,
A.BLOCKING_OTHERS
FROM DBA_LOCKS A, DBA_LOCKS B
WHERE A.BLOCKING_OTHERS = 'Blocking'
AND A.MODE_HELD NOT IN ('none', 'Null')
AND B.MODE_REQUESTED <> 'None'
AND A.LOCK_TYPE = B.LOCK_TYPE
AND A.LOCK_ID1 = B.LOCK_ID1
AND A.LOCK_ID2 = B.LOCK_ID2) D
WHERE A.SESSION_ID = D.BLOCKED(+)
AND A.OBJECT_ID = B.OBJECT_ID
AND A.SESSION_ID = C.SID
AND D.BLOCKED_BY_SESSION IS NOT NULL;
--查找引起锁的进程和语句，以及被锁的对象
select o.object_id Locked_Object_id,
t.owner,
t.OBJECT_TYPE,
t.OBJECT_NAME,
S.hash_value,
S.Sql_text,
s.sid
from v$lock L, v$locked_object o, dba_objects t, v$open_cursor S
where L.type = 'TX'
and L.lmode = 0
and L.sid = o.session_id
and o.object_id = t.object_id
and L.sid = S.sid;
--在应用程序出现不响应时，可以考虑通过下面三个语句查找锁的会话号(sid)，再通过最后一个查找锁的sql语句
--1、Show all sessions waiting for any lock:
select event,p1,p2,p3 from v$session_wait
where wait_time=0 and event='enqueue';
--2、Show sessions waiting for a TX lock:
select * from v$lock where type='TX' and request>0;
--3、Show sessions holding a TX lock:
select * from v$lock where type='TX' and lmode>0;
--4、通过会话号(sid)查找sql语句
select sql_text
from v$session s,v$sqltext t
where t.address=s.sql_address AND s.sid=会话
号
order by t.piece
--通过系统进程号查看使用该进程的sql语句，会话id，sql的hash值
select sq.sql_text,
s.sid,
s.serial#,
s.schemaname,
s.terminal,
s.osuser,
s.sql_id,
s.sql_hash_value,
pro.spid
from v$process pro, v$session s, v$sqltext sq
where s.paddr = pro.addr
and s.sql_id = sq.sql_id
and sq.hash_value = s.sql_hash_value
and pro.spid = '168120'
order by sq.piece
--根据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 );
--查找object为哪些进程所用
SELECT p.Spid, s.Sid, s.Serial# Serial_Num, https://www.doczj.com/doc/5d2096611.html,ername 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 https://www.doczj.com/doc/5d2096611.html,ername, s.Osuser;
--根据v.sid查看对应连接的资源占用等情况
SELECT https://www.doczj.com/doc/5d2096611.html,, 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#
--生成kill session的sql，sql_id可以通过oracle的EM控制台等方式获取
select 'alter system kill session '||''''||sid||','||serial#||'''' || ';' from v$session
where sql_id='d9n3z7v4h213k';
--批量杀死某一条件的所有会话
--(1)通过终端机器名关联
select 'alter system kill session '||''''||sid||','||serial#||'''' || ';' from v$session
where machine='终端机器名' ;
--(2)通过操作系统进程关联，查找相应的会话id和sql_id号
select s.sid,s.serial#,s.sql_id,p.spid 系统进程号 from v$session s,v$process p
where s.paddr=p.addr and p.spid='系统进程号';
--通过sql_id关联，拼出杀进程语句，对于上一个语句杀sid无效的情况下使用
select 'alter system kill session '||''''||s.sid||','||s.serial#||'''' || ';','kill -9 '||p.spid 系统进程号
from v$session s,v$process p where s.sql_id='d9n3z7v4h213k' and s.paddr=p.addr;
--杀掉正在使用某个过程（UG_RPT_AC_XX）的会话，下面语句分别在两个节点同时执行，编译存储过程时非常有用。
DECLARE
CURSOR sss
IS
SELECT 'alter system kill session ' || '''' || s.SID || ',' || s.serial# || '''' || ' immediate' sql_str
FROM gv$access a, gv$session s
WHERE a.OBJECT LIKE '%UG_RPT_AC_XX%' AND a.inst_id = s.inst_id AND a.SID = s.SID AND a.inst_id = 1;
BEGIN
FOR rec IN sss
LOOP
BEGIN
EXECUTE IMMEDIATE rec.sql_str;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END LOOP;
END;
DECLARE
CURSOR sss
IS
SELECT 'alter system kill session ' || '''' || s.SID || ',' || s.serial# || '''' || ' immediate' sql_str
FROM gv$access a, gv$session s
WHERE a.OBJECT LIKE '%UG_RPT_AC_XX%' AND a.inst_id = s.inst_id AND a.SID = s.SID AND a.inst_id = 2;
BEGIN
FOR rec IN sss
LOOP
BEGIN
EXECUTE IMMEDIATE rec.sql_str;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END LOOP;
END;
--占用资源相关查询
--看数据库启动以来占时特长的语句
select inst_id,
elapsed_time / 1000000 elapsed_seconds,
sql_text,
executions
from gv$sqlarea
order by elapsed_time desc;
--通过会话等待类查看详细的等待信息
select event, total_waits, time_waited from v$system_event e, v$event_name n
where n.event_id = e.event_id and n.wait_class_id = '2723168908';
--查看全局等待事件
select * from gv$session_wait
where event not in ('SQL*Net message from client',
'Streams AQ: waiting for messages in the queue',
'rdbms ipc message', 'ASM background timer',
'Streams AQ: waiting for time management or cleanup tasks',
'DIAG idle wait', 'pmon timer','PX Deq: reap credit','PX Deq: Execution Msg');
--查看当前会话顺序读的等待次数和等待时间
SELECT sid, total_waits, time_waited
FROM v$session_event
WHERE event='db file sequential read'
and total_waits>0
ORDER BY 3,2;
--查询耗资源的进程（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(https://www.doczj.com/doc/5d2096611.html,ername, '[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, https://www.doczj.com/doc/5d2096611.html,ername ASC, s.Osuser ASC
--获得PGA占用高的会话的SQL
select sql_text, b.sid,name,value
from v$statname n,v$sesstat s, v$sqltext a,v$session b
where n.STATISTIC# = s.STATISTIC# and https://www.doczj.com/doc/5d2096611.html, like 'session pga memory%' and b.sid=s.sid and a.hash_value = b.sql_hash_value and rownum<20
order by value desc ;
--查看PGA实际占用的情况
select sid,name,value from v$statname n,v$sesstat s where n.STATISTIC# = s.STATISTIC# and name like 'session%memory%' order by 3 asc;
--看实际占用的总量：
select sum(value)/1024/1024 Mb
from
v$sesstat
s, v$statname n
where
n.STATISTIC# = s.STATISTIC# and
name = 'session pga memory';
--触发器记录登陆客户端Ip
使用sys用户创建表和触发器
create table login_history
(username varchar2(60),
machine varchar2(60),
login_time date,
ip varchar2(50)
);
create or replace trigger login_log
after logon on database
begin
insert into login_history
select username, machine, sysdate, sys_context('userenv', 'ip_address')
from v$session
where audsid = userenv('sessionid');
commit;
end;
--查询login_history就可以了，因为这张表是sys创建，其他用户查看需要sys授权用sys用户授予powermeter用户查看此表的权限
grant select on login_history to powermeter;
--查询登陆客户端ip
select * from login_history;