Oracle学习笔记之分享一些常用的运维命令

在使用TOP命令来查看CPU或内存使用率异常的进程，再根据PID查找对应的oracle session
select a.sid,a.serial#,a.sql_id,a.machine,a.program,a.status,c.sql_text
from gv$session a, gv$process b, gv$sql c where a.paddr=b.addr and b.spid in (xxx,xxx) and a.sql_id = c.sql_id;

可以用来查询数据库当前的等待事件
select inst_id,event,program,machine,sql_id from gv$session where wait_class <> 'Idle' order by event;

如果某一等待事件造成了数据库运行严重缓慢，那么在执行alter system kill session命令可以无法成功，这时候就需要在OS层面来杀死进程
select 'kill -9 ' || spid from v$process where addr in (select paddr from v$session where event='latch: buffer cache chains'));

在v$session视图中无法查看连接用户的IP，可以通过下面的方法实现这一目的 方法一 create table login_history ( username varchar2(60), machine varchar2(60), event varchar2(60), program varchar2(100), sql_id varchar2(40), login_time date, ip varchar2(50) ); create or replace trigger login_log after logon on database begin insert into login_history select username, machine, event, program,sql_id, sysdate, sys_context('userenv', 'ip_address') from v$session
where audsid = userenv('sessionid');
commit;
end;
/
方法二
select utl_inaddr.get_host_address(t.machine),t.* FROM v$session t;

通过dba_hist_active_sess_history视图，来查询过去某个时间段内发生某个等待事件的信息，主要是查询历史等待事件的sql_id
select SESSION_ID,SESSION_TYPE,MACHINE,PROGRAM,sql_id,BLOCKING_SESSION,BLOCKING_SESSION_STATUS
from dba_hist_active_sess_history
where sample_time > to_date('2020-06-23 09:00:00','yyyy-mm-dd hh24:mi:ss')
and sample_time < to_date('2020-06-23 09:30:00','yyyy-mm-dd hh24:mi:ss')
and wait_class<>'Idle'
and event like 'latch: cache buffers chains%'
group by SESSION_ID,SESSION_TYPE,MACHINE,PROGRAM,sql_id,BLOCKING_SESSION,BLOCKING_SESSION_STATUS order by SESSION_ID,BLOCKING_SESSION;