Identify and stop an Oracle process with permanent CPU load: show active sessions, kill the session and remove the root cause.

globe with meridians Language: Deutsch · English

hammer and wrench Stop a high-CPU Oracle process

When an Oracle background process pins the CPU: detect it, kill the session and prevent it from returning.

On this page

These steps come from maintaining the old development VM and are very Oracle-specific. Use with care on production systems.

1. Show active processes

SELECT s.username, s.sid, s.osuser, t.sql_id, sql_text
FROM v$sqltext_with_newlines t, v$session s
WHERE t.address = s.sql_address AND t.hash_value = s.sql_hash_value
  AND s.status = 'ACTIVE' AND s.username <> 'SYSTEM';

2. Get session data

SELECT inst_id, sid, serial# FROM gv$session WHERE sid = 133;

3. Kill the session

ALTER SYSTEM KILL SESSION '133,9,@1';

To prevent recurrence, remove the root cause (e.g. a problematic materialized view).

Related pages

file cabinet Oracle SQLPlus tools (DE)

More SQLPlus queries.

Open →