For this first post, lets start with something easy that everyone that manages a DB should know.
You have been requested to kill everything that RMAN is running for whatever reason. It can be backup is no longer valid or it is generating impact in performance, which needs to be resolved right away. Do you want to waste time looking for all the sessions, creating sentences to run… etc etc, while you have someone breathing in your neck? I don’t think so…
SQL> select 'alter system kill session '''||b.sid||','||b.serial#||',@'||b.inst_id||''' IMMEDIATE;' as script
from gv$process a, gv$session b
where a.addr=b.paddr and client_info like 'rman%';
SCRIPT
---------------------------------------------------------------------------
alter system kill session '1663,57538,@1' IMMEDIATE;
alter system kill session '2345,59743,@4' IMMEDIATE;
alter system kill session '2996,500,@3' IMMEDIATE;
alter system kill session '116,29091,@3' IMMEDIATE;
alter system kill session '512,36666,@1' IMMEDIATE;
alter system kill session '1481,539,@2' IMMEDIATE;
alter system kill session '1521,55422,@2' IMMEDIATE;
alter system kill session '2024,19779,@5' IMMEDIATE;
alter system kill session '2812,4939,@2' IMMEDIATE;
....
126 rows selected.
SQL> You can tweak for anything you want. Do you need to kill a specific SQL_ID?
SQL> SELECT 'alter system kill session '''||s.sid||','||s.serial#||',@'||s.inst_id||''' immediate;' FROM gv$session s where SQL_ID = '&sql_id';
Comments
Thanks. After review grid document, I found this post and the Audit_Trail post. I monitor for all connection attempts, encryption level used during connections and also open/inactive sessions. It was the inactive sessions that highlighted the need to kill off inactive sessions regularly. I realized that a long standing control in Development might not work as intended. When third party support allowed to connect for the day — Development disables all accounts every night and expects vendor to request again if they need to work the next day. Thinking about your session kill idea, I thought there might be a case where a PL/SQL tab from that support person connected, and was ‘left open’ as the support person just kept opening tabs as they completed work for other clients. My audit trail will show session as inactive, but I assumed there was no reason for it to automatically disconnect unless I am missing something. Risk prevention: How to prevent the third party from gaining access even if original DB user was disabled? Wouldn’t re-authentication of disabled user be bypassed if third party support still had an inactive session defined (assumes site-to-site VPN connectivity was still working)? Development just disables the db user account until a new support access request comes in. I have solved the network side (no direct access, must go through local jump server with MFA, etc. and eliminates concern that third party support had any session to our DB as queries only run what’s allowed in controlled session on jump station or in scripts on DB servers that are vetted. However, I still feel it would be good to run a job to kill sessions for any user in the group of ‘vendor db users’ ‘just in case’. Your article “sql-how-to-kill-sessions-in-bulk” was just an extra on your blog that I found looking for 12c to 19c migration. Larry Background: Outside of server and DB log forwarding (MSSP/MDR), I created my own oversight to confirm what logon success/failures, what does open/inactive session look like over time, and what is the level of encryption current and legacy clients use to connect with (a pre-19c checklist for when all connections will be encrypted or firewalled). The audit_access_attempts database provided lots of insight (including IP address, etc.). See Header from export file: “TIMESTAMP”,”DBNAME”,”OS_PROCESS”,”OS_USERNAME”,”HOST”,”DB_USER”,”TERMINAL”,”Additional Info.”,”RETURNCODE”. It was what I expected it to be and ensured we knew of any connections (successful or not) and whether expected behaviors and all that good stuff was working. But then I started to audit sessions — a little trickier — I started to see inactive sessions from ‘disabled third party support accounts’ still present. So your article above was on point on how to kill sessions, I need the same but with a little more restrictions