Suspend sessions and release CPU resources
                                                 Last update (2010-11-02 18:28:08)
                                                                                                                   Date added (2008-02-13 18:06:46)

Summary
Lets say that 50 users are currently active to the database and running heavy queries and the CPU utilization is 100%. Suddenly a new request is coming with a maximum priority. The new request must run and finish at the expected time as soon as possible, but with the load right currently is impossible because of all the other 50 active users. What do you do if you don't want to kill the other sessions?

One possible solution
You suspend the others sessions and release CPU resources.

How to suspend one session.
1. Find the SPID of the session that you want to “pause” with the query below
SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
v$session.SID, v$session.SERIAL#, v$process.SPID spid, v$session.process CLPRID,
v$session.USERNAME, STATUS, OSUSER, MACHINE, v$session.PROGRAM, MODULE, action, SQL_HASH_VALUE,
'alter system kill session ' || '''' || v$session.SID || ', ' || v$session.SERIAL# || '''' || ' immediate;' kill_sql
FROM v$session, v$process
WHERE v$session.paddr = v$process.addr
--AND v$process.spid = 23832
--and v$session.process = '26432'
--AND v$session.status = 'INACTIVE'
--AND v$session.username LIKE '%KAPARELIS SPYROS%'
--AND v$session.SID = 4392
--and v$session.sid in (select sid from v$session where SADDR in (select session_addr from v$sort_usage)) --(v$temp_usage)
--and v$session.osuser like 'oracle%'
--and osuser='uidea'
--AND v$session.module LIKE '%qot%'
--and v$session.machine like '%PLHROFORIK92%'
--AND v$session.program LIKE '%QMN%'
--AND v$session.action LIKE 'FRM%'
--and action like '%FRM%OTE%'
ORDER BY logon_time ASC;
2. From sqlplus connect as sysdba
oradebug setorapid SPID
oradebug SUSPEND
Watch Unix, you will see suspended session going down on $>top
3. To resume session execute from sqlplus
oradebug RESUME
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search