Posted on

How to Kill a Session in Oracle RAC Database

If you need to kill a session in Oracle RAC database then first you need to know the SID, Serial# and the instance where the session is running.

You can get that info depending on what you are given, let’s see some options.

If you know the SID, then you can get the other values with this query

select sid,serial#,inst_id 
from gv$session 
where sid='1195';

Now let’s suppose that you have the serial# then use this query

select sid,serial#,inst_id, sql_id 
from gv$session 
where serial#='15148';

In case you have the username, use this query, you could add the logon_time to the query

select sid,serial#,inst_id 
from gv$session 
where USERNAME='username';
Continue reading “How to Kill a Session in Oracle RAC Database”
Posted on

How to Change AWR Retention Period in Oracle Database

You can first check the AWR Retention Period in Oracle with this

set lines 200
col SNAP_INTERVAL format a20
col RETENTION format a20
col SRC_DBNAME format a20

select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL        CON_ID   SRC_DBID SRC_DBNAME
---------- -------------------- -------------------- ---------- ---------- ---------- --------------------
3847XX0535 +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT             0 3847XX0535

You can see that the interval is every hour and the retention is just 8 days.

You can change the retention to 30 days with this (30*24*60)

execute dbms_workload_repository.modify_snapshot_settings(retention => 43200);

If you also want to change the SNAP_INTERVAL, you can change it like that for taking snaps every 30 mins

execute dbms_workload_repository.modify_snapshot_settings(interval => 30);