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;