OracleSessions
Generate kill statements for a user's sessions
Prompts for a username and prints one ALTER SYSTEM KILL SESSION per session, including the instance on RAC. Nothing is killed until you run the output.
Not yet verified. How scripts are tested
1CLEAR COLUMNS2SET LINESIZE 120 PAGESIZE 0 TRIMOUT ON TAB OFF VERIFY OFF FEEDBACK OFF3 4SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ',@' || inst_id5 || ''' IMMEDIATE; -- ' || status || ' ' || program AS kill_cmd6FROM gv$session7WHERE username = UPPER('&username')8 AND type = 'USER';9 10SET PAGESIZE 100 FEEDBACK ONSave it as ora-kill-gen.sql and run it with SQL> @ora-kill-gen.
More Oracle scripts: Sessions
- Long operations with time remaining (all instances)Long operations still running on any instance, with percent done and elapsed, remaining and estimated total minutes. RMAN is left out because it has…
- SQL active for 30 minutes or more (all instances)Active application sessions whose current call has run for at least 1,800 seconds, with instance, program, OS process ID, SQL_ID, plan hash value and…
- Active user sessionsUser sessions doing work right now on every instance: who, from which OS user and machine, the module, what they're waiting on, the SQL_ID, when they…
- Session count by user and machineWho is connected, from where, and how many are active. The quickest way to spot a connection pool that has run away.
- Everything about one sessionPrompts for a SID and shows the user, OS details, program, current and previous SQL_ID, wait event and the OS process ID behind it.
- Find the session behind an OS process IDWhen top or ps shows an Oracle process burning CPU, enter its PID to see which session and SQL it belongs to.