Kill session in Oracle

Generally there are two methods to kill a session in Oracle Database:

• do it directly in the database
• do it on OS level – kill dedicated server process for the database

Before you kill a session you need to scan following views to find sid, serial#, inst_id for the session

• V$SESSION – used for non RAC databases to find sid, serial#
• GV$SESSION – used for RAC databases to find sid, serial#, inst_id

where

• SID – session identifier
• SERIAL# – Session serial number. Used to uniquely identify a session’s objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID
• INST_ID – instance number. Required only for RAC.

--non RAC databases
SELECT s.sid, s.serial#
FROM v$session s
WHERE username = 'SCOTT';

--RAC databases
SELECT s.inst_id, s.sid, s.serial#
FROM gv$session s
WHERE username = 'SCOTT';

To kill a session outside database scan following views to find dedicated server process identifier spid on OS level

• V$PROCESS – non RAC databases to find spid
• GV$PROCESS – RAC databases to find spid

--non RAC databases
SELECT s.sid, s.serial#, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
  AND username = 'SCOTT';

--RAC databases
SELECT s.inst_id, s.sid, s.serial#, p.spid
FROM gv$session s, gv$process p
WHERE s.paddr = p.addr
  AND s.inst_id = p.inst_id
  AND username = 'SCOTT';

Kill session from database level

Following methods use sid, serial#, inst_id from view [G]V$SESSION.

1. First method is to use ALTER SYSTEM KILL SESSION

ALTER SYSTEM KILL SESSION 'SID, SERIAL#, @INSTANCE_ID' [IMMEDIATE]

Above command marks a session as terminated, roll back ongoing transactions, release all session locks, and partially recover session resources. If this activity is going on it can hang for a minute. To avoid this hang use IMMEDIATE.

Marked session will be killed by Oracle as soon as possible however sometimes it requires to kill dedicated process manually.

--non rac kill example
ALTER SYSTEM KILL SESSION '123,34216';
ALTER SYSTEM KILL SESSION '123,34216' IMMEDIATE;

--rac kill example
ALTER SYSTEM KILL SESSION '123,34216,@2';
ALTER SYSTEM KILL SESSION '123,34216,@1' IMMEDIATE;

2. Second method is to use ALTER SYSTEM DISCONNECT SESSION

ALTER SYSTEM DISCONNECT SESSION 'SID, SERIAL#' 
POST_TRANSACTION | IMMEDIATE;

This is more effective than ALTER SYSTEM KILL SESSION because it disconnect the current session by destroying the dedicated server process (or virtual circuit if the connection was made by way of a Shared Sever). It must be run on instance where you want to kill Oracle session.

• POST_TRANSACTION – waits for current transaction to complete, then kill dedicated process
• IMMEDIATE – kills immediately dedicated process

ALTER SYSTEM DISCONNECT SESSION '123,34213' POST_TRANSACTION;
ALTER SYSTEM DISCONNECT SESSION '123,34213' IMMEDIATE;

Kill session from os level

Other option to kill session is to kill dedicated process on OS. It will give the same very quick effect as ALTER SYSTEM DISCONNECT SESSION. SPID can be found in[G]V$PROCESS view.

--windows
c:\orakill ORACLE_SID spid

--unix
kill -9 spid

It’s worth on Unix to check if the process really exists before executing kill -9

ps -ef | grep spid

Finding source code in Oracle

Finding source code written by a developer is typical task executed by a DBA or a developer.

Generally there are two basic methods to get source code:

• select code from dictionary views – quick, simple, can return part of code, complicated to generate properly DDL
• generate code using DBMS_METADATA package – very powerful, returns full code, easy to generate DDL code

Prepare test objects

Two show both methods first I need to create some objects

table TEST_TBL

CREATE TABLE test_tbl
(
  id1 NUMBER,
  id2 NUMBER,
  id3 NUMBER,
  id4 NUMBER
);

index TEST_TBL_IDX

CREATE INDEX test_tbl_idx ON test_tbl(id1);

view TEST_TBL_VW

CREATE OR REPLACE VIEW test_tbl_vw
AS
SELECT * FROM test_tbl;

materialized view TEST_TBL_MV

CREATE MATERIALIZED VIEW test_tbl_mv
AS
SELECT * FROM test_tbl;

trigger TEST_TBL_BD_TRG

CREATE OR REPLACE TRIGGER test_tbl_bd_trg
BEFORE DELETE ON test_tbl
BEGIN
  NULL;
END;
/

procedure TEST_TBL_PRC

CREATE OR REPLACE PROCEDURE test_tbl_prc
AS
  l_cnt NUMBER;
BEGIN
  SELECT count(*)
    INTO l_cnt
    FROM test_tbl;
END;
/

Manual from dictionary

This method is very popular to get quick look on source code of interesting us object. The most benefit is you can get only part of code a few interesting lines to analyze. It’s especially useful when Oracle raises errors and return owner, name and line where error occurred in your PL/SQL code.

Finding code for

• FUNCTION
• JAVA SOURCE
• LIBRARY
• PACKAGE
• PACKAGE BODY
• PROCEDURE
• TRIGGER
• TYPE
• TYPE BODY

can be done by following select. Interesting option here is possibility to limit code by LINE

SELECT type, line, text
  FROM dba_source
 WHERE owner=USER 
   AND name='TEST_TBL_PRC'
ORDER BY line;

TYPE       LINE TEXT
---------- ---- --------------------------
PROCEDURE    1    PROCEDURE test_tbl_prc
PROCEDURE    2    AS
PROCEDURE    3      l_cnt NUMBER;
PROCEDURE    4    BEGIN
PROCEDURE    5      SELECT count(*)
PROCEDURE    6        INTO l_cnt
PROCEDURE    7        FROM test_tbl;
PROCEDURE    8    END;

Finding view code

SELECT view_name, text 
  FROM dba_views
WHERE owner=USER
  AND view_name='TEST_TBL_VW';

VIEW_NAME    TEXT
------------ -------------------------------
TEST_TBL_VW  SELECT "ID1","ID2","ID3","ID4" 
               FROM test_tbl

finding materialized view code

SELECT mview_name, query
  FROM dba_mviews
  WHERE owner=USER
    AND mview_name='TEST_TBL_MV';

MVIEW_NAME   QUERY
------------ -----------------------
TEST_TBL_MV  SELECT * FROM test_tbl

another quite popular method to find trigger body code

SELECT trigger_name, trigger_body 
  FROM dba_triggers
WHERE owner=USER
  AND trigger_name='TEST_TBL_BD_TRG';

TRIGGER_NAME     TRIGGER_BODY 
---------------- -------------
TEST_TBL_BD_TRG  BEGIN
                   NULL;
                 END;

It’s not good method to find full DDL definitions for objects like TABLES, INDEXES etc.

DBMS_METADATA

It’s very advanced package used to generate full code. Always should be used to generate full code for DDLs.

Some examples how to generate detailed DDLs

Table

SELECT dbms_metadata.get_ddl('TABLE', 'TEST_TBL')
  FROM dual;

CREATE TABLE "TOMASZ"."TEST_TBL" 
(    
  "ID1" NUMBER, 
    "ID2" NUMBER, 
    "ID3" NUMBER, 
    "ID4" NUMBER
) 
SEGMENT CREATION DEFERRED 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
NOCOMPRESS LOGGING
TABLESPACE "USERS";

Index

SELECT dbms_metadata.get_ddl('INDEX', 'TEST_TBL_IDX')
  FROM dual;
  
CREATE INDEX "TOMASZ"."TEST_TBL_IDX" ON "TOMASZ"."TEST_TBL" ("ID1") 
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
TABLESPACE "USERS";

View

SELECT dbms_metadata.get_ddl('VIEW', 'TEST_TBL_VW')
  FROM dual;

CREATE OR REPLACE FORCE EDITIONABLE VIEW 
"TOMASZ"."TEST_TBL_VW" ("ID1", "ID2", "ID3", "ID4") AS 
SELECT "ID1","ID2","ID3","ID4" FROM test_tbl

Materialized view

SELECT dbms_metadata.get_ddl('MATERIALIZED_VIEW', 'TEST_TBL_MV')
  FROM dual; 

CREATE MATERIALIZED VIEW "TOMASZ"."TEST_TBL_MV" 
("ID1", "ID2", "ID3", "ID4")
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
NOCOMPRESS LOGGING
TABLESPACE "USERS" 
BUILD IMMEDIATE
USING INDEX 
REFRESH FORCE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS SELECT * FROM test_tbl

Trigger

SELECT dbms_metadata.get_ddl('TRIGGER', 'TEST_TBL_BD_TRG')
  FROM dual;

CREATE OR REPLACE EDITIONABLE TRIGGER "TOMASZ"."TEST_TBL_BD_TRG" 
BEFORE DELETE ON TEST_TBL 
BEGIN
  NULL;
END;

ALTER TRIGGER "TOMASZ"."TEST_TBL_BD_TRG" ENABLE;

Procedure

SELECT dbms_metadata.get_ddl('PROCEDURE', 'TEST_TBL_PRC')
  FROM dual;

CREATE OR REPLACE EDITIONABLE PROCEDURE "TOMASZ"."TEST_TBL_PRC" 
AS
  l_cnt NUMBER;
BEGIN
  SELECT count(*)
    INTO l_cnt
    FROM test_tbl;
END;

huge advantage over manual method are extra options included in the package like:

• transformations of attributes like owner, tablespace name, storage etc
• possibility to generate code as XML
• it’s main method used by export and import tools expdp, impdp
• support for all objects in the database

Real time sql monitoring in 11g

source

This article presents new feature of 11g Real time sql monitoring.

Real time sql monitoring introduced in Oracle 11G enables to monitor performance of SQL statements while they are running. Statistics about monitored queries are visible in new views.

Requirements to monitor a query by database

Oracle will monitor a query if following requirements are fulfilled:

• following instance parameters are set

STATISTIC_LEVEL=TYPICAL or STATISTIC_LEVEL=ALL
CONTROL_MANAGEMENT_PACK_ACCESS=DIAGNOSTIC+TUNING

• the query is started in parallel or
• it has consumed at least five seconds of the CPU or I/O time in a single execution or
• you are using hint MONITOR

  select /*+ MONITOR */ from

You can block monitoring for a query if you specify hint NO_MONITOR

select /*+ NO_MONITOR */ from

Views for real time monitoring

New views are collecting statistics about monitored queries:

V$SQL_MONITOR – here you can see informations like elapsed time, CPU time, number of read and writes, I/O wait time and many others. Statistics are available at least one minute after the query ends. It’s refreshed in almost real time once a second.

V$SQL_PLAN_MONITOR – here you can see very detailed statistics for each step in explain plan used by the query and they are as well updated once a second.

Both above views plus old V$SQL, V$SQL_PLAN, V$SESSION, V$ACTIVE_SESSION_HISTORY, V$SESSION_LONGOPS can give perfect information about execution of your query.

Enterprise Manager 11g and sql monitoring

You can access real time monitoring feature in Enterprise Manager Database Control. You can find link “SQL Monitoring” on “Performance” tab of Enterprise Manager.

once you click the link you should see queries which Oracle is monitoring. In my case I can see only one on my database  well my database is sleeping well right now.

If you click on the statement you will get more informations like:

– duration of this query

– full explain plan (standard form or graphical form)

– how database time was consumed  per CPU,I/O etc

– how much time was consumed by PL/SQL, Java

– how much buffer gets was executed

– how many I/O request was executed

– how many I/O bytes was used per read/write

– full statistics for each step in explain plan

– usage of parallel processes

– statistics per wait events

– CPU usage across time

– I/O request across time

– I/O throughput across time

– PGA usage across time

– Temp usage across time

By clicking “Report” button you can generate very simple report

or you can save all above pages by clicking “Save”. Saving all above is very usefully in case you want to document something from database or you want send some details to somebody else.

From main page you can also drill down to session which executed this query or historical activity of the query.

DBMS_SQLTUNE and sql monitoring

In case you don’t have access to Enterprise Manager you can generate such nice reports using function REPORT_SQL_MONITOR from package DBMS_SQLTUNE. The function has got many parameters and can produce very detail report(you can turn off some sections if you want) in many formats like text, simple HTML, active HTML, XML.

DBMS_SQLTUNE.REPORT_SQL_MONITOR
( sql_id IN VARCHAR2 DEFAULT NULL, 
  session_id IN NUMBER DEFAULT NULL, 
  session_serial IN NUMBER DEFAULT NULL, 
  sql_exec_start IN DATE DEFAULT NULL, 
  sql_exec_id IN NUMBER DEFAULT NULL, 
  inst_id IN NUMBER DEFAULT NULL, 
  start_time_filter IN DATE DEFAULT NULL, 
  end_time_filter IN DATE DEFAULT NULL, 
  instance_id_filter IN NUMBER DEFAULT NULL, 
  parallel_filter IN VARCHAR2 DEFAULT NULL, 
  plan_line_filter IN NUMBER DEFAULT NULL, 
  event_detail IN VARCHAR2 DEFAULT 'YES', 
  bucket_max_count IN NUMBER DEFAULT 128, 
  bucket_interval IN NUMBER DEFAULT NULL, 
  base_path IN VARCHAR2 DEFAULT NULL, 
  last_refresh_time IN DATE DEFAULT NULL, 
  report_level IN VARCHAR2 DEFAULT 'TYPICAL', 
  type IN VARCHAR2 DEFAULT 'TEXT', 
  sql_plan_hash_value IN NUMBER DEFAULT NULL) 
RETURN CLOB;

simple example

let’s prepare some data

create table test_tbl
(id number);

begin
  for i in 1..100
  loop
    insert into test_tbl values(i);
  end loop;
  commit;
end;
/

let’s run query to monitor

set timing on
select /*+ MONITOR */ count(*) cnt
from test_tbl, test_tbl, test_tbl, test_tbl;
  CNT
----------
 100000000 

Elapsed: 00:00:04.662

you can read details about it in V$SQL_MONITOR

select key, sql_id, status 
from v$sql_monitor 
where sql_text='select /*+ MONITOR */ count(*) cnt 
from test_tbl, test_tbl, test_tbl, test_tbl';

KEY                        SQL_ID        STATUS            
-------------------------- ------------- -------------------
657129996295               9fvrwtjugnkup DONE (ALL ROWS)     

Elapsed: 00:00:00.016

and generate your report as TEXT

select
DBMS_SQLTUNE.REPORT_SQL_MONITOR(
   sql_id=>'9fvrwtjugnkup',
   report_level=>'TYPICAL',
   type=>'TEXT')
from dual;

SQL Monitoring Report

SQL Text
------------------------------
select /*+ MONITOR */ count(*) cnt from test_tbl, test_tbl, test_tbl, test_tbl

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)     
 Instance ID         :  1                   
 Session             :  SYS (204:115)       
 SQL ID              :  9fvrwtjugnkup       
 SQL Execution ID    :  16777216            
 Execution Started   :  02/03/2013 01:57:49 
 First Refresh Time  :  02/03/2013 01:57:49 
 Last Refresh Time   :  02/03/2013 01:57:54 
 Duration            :  5s                  
 Module/Action       :  SQL Developer/-     
 Service             :  SYS$USERS           
 Program             :  SQL Developer       
 Fetch Calls         :  1                   

Global Stats
=================================================
| Elapsed |   Cpu   |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
=================================================
|    4.85 |    4.76 |     0.09 |     1 |     12 |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=2897041765)
===========================================================================================================================================
| Id |         Operation         |   Name   |  Rows   | Cost |   Time    | Start  | Execs |   Rows   |  Mem  | Activity | Activity Detail |
|    |                           |          | (Estim) |      | Active(s) | Active |       | (Actual) | (Max) |   (%)    |   (# samples)   |
===========================================================================================================================================
|  0 | SELECT STATEMENT          |          |         |      |         4 |     +2 |     1 |        1 |       |          |                 |
|  1 |   SORT AGGREGATE          |          |       1 |      |         4 |     +2 |     1 |        1 |       |          |                 |
|  2 |    MERGE JOIN CARTESIAN   |          |    100M | 275K |         4 |     +2 |     1 |     100M |       |          |                 |
|  3 |     MERGE JOIN CARTESIAN  |          |      1M | 2752 |         4 |     +2 |     1 |       1M |       |          |                 |
|  4 |      MERGE JOIN CARTESIAN |          |   10000 |   31 |         4 |     +2 |     1 |    10000 |       |          |                 |
|  5 |       TABLE ACCESS FULL   | TEST_TBL |     100 |    2 |         4 |     +2 |     1 |      100 |       |          |                 |
|  6 |       BUFFER SORT         |          |     100 |   29 |         4 |     +2 |   100 |    10000 |  4096 |          |                 |
|  7 |        TABLE ACCESS FULL  | TEST_TBL |     100 |      |         1 |     +2 |     1 |      100 |       |          |                 |
|  8 |      BUFFER SORT          |          |     100 | 2752 |         4 |     +2 | 10000 |       1M |  4096 |          |                 |
|  9 |       TABLE ACCESS FULL   | TEST_TBL |     100 |      |         1 |     +2 |     1 |      100 |       |          |                 |
| 10 |     BUFFER SORT           |          |     100 | 275K |         5 |     +1 |    1M |     100M |  4096 |   100.00 | Cpu (5)         |
| 11 |      TABLE ACCESS FULL    | TEST_TBL |     100 |      |         1 |     +2 |     1 |      100 |       |          |                 |
===========================================================================================================================================

or generate full report using ACTIVE option. Save the output in HTML file to view it.

select
DBMS_SQLTUNE.REPORT_SQL_MONITOR(
   sql_id=>'9fvrwtjugnkup',
   report_level=>'TYPICAL',
   type=>'ACTIVE')
from dual;

ACTIVE reports have a rich, interactive user interface similar to Enterprise Manager while not requiring any EM installation. The report file built is in HTML format, so it can be interpreted by most modern browsers. The code powering the active report is downloaded transparently by the web browser when the report is first viewed, hence viewing it requires outside connectivity.

or generate full report using HTML option. Save the output in HTML file to view it.

select
DBMS_SQLTUNE.REPORT_SQL_MONITOR(
   sql_id=>'9fvrwtjugnkup',
   report_level=>'TYPICAL',
   type=>'HTML')
from dual;

HTML version is pretty nice and simple

Daily Production Oracle DBA checklist !!!

1) Checking all instances status, Make sure all are up and running 

     How: Log in to each instance, run any query to make sure databases are available and running. Such as

            $ export ORACLE_SID=orcl

            $ sqlplus /nolog

            SQL> select host_name from v$instance ; 

2) Check alert log entries for any error or any new things (at least daily two times for all databases)

i) When you enter into office 

ii) When you are leaving office  

And when any complain comes about database slowness or anything

3) Verify success of databases RMAN/export/any other backups.

                                    i)  Check the RMAN/export logs for any error.

                                    ii) Check the backup size for confirmation and daily backup size growth.  

           

4. Verify success of database backup archiving to tape (or let system admin know to back it up.)

5) Verify Auto control file backup successful 

6) Verify success of all schedule job

7) Verify enough spaces are there for -

                                    i) Space of Tablespaces (if autoextend = no)

                                    ii) Space in OS Hard disk/ Storage LUN

                                    iii) Archive log space/ flash_recovery_area locations

8) Check to all objects status to make sure all are 'VALID'

9) If you have a Standby Database, check the status of standby database and gap between primary. 

10) Review contention for CPU, memory, network, and disk resources.

                        In linux can use -  top, netstat, vmstat  etc. commands

                        In Sun Solaris can use – prstat, vmstat ,netstat etc. 

10) Checks for blocking locks , killed sessions etc. 

11) Check connected sessions (average count) to verify there are no abnormalities (sometimes parallel thread exceeds your process parameter’s value) 

                        i) Check top session’s information.

                        ii) Top SQL statements

12) Troubleshooting if any issue reported 

13) Spend sometimes to increase and upgrade your knowledge 

Discussion on DCD,Resource Limits, V$SESSION, V$PROCESS and OS processes !!!

Dead Connection Detection (DCD)  These are previously valid connections with the database but the connection between the client and  server processes has terminated abnormally. Examples of a dead connection: - A user reboots/turns-off their machine without logging off or disconnecting from the database. - A network problem prevents communication between the client and the server. In these cases, the shadow process running on the server and the session in the database may not terminate. Implemented by       * adding SQLNET.EXPIRE_TIME = to the sqlnet.ora file With DCD is enabled, the Server-side process sends a small 10-byte packet to the client process after the duration  of the time interval specified in minutes by the SQLNET.EXPIRE_TIME parameter. If the client side connection is still connected and responsive, the client sends a response packet back to the database  server, resetting the timer..and another packet will be sent when next interval expires (assuming no other activity on  the connection). If the client fails to respond to the DCD probe packet      * the Server side process is marked as a dead connection and      * PMON performs the clean up of the database processes / resources      * The client OS processes are terminated NOTE: SQLNET.RECV_TIMEOUT can be set on the SERVER side sqlnet.ora file. This will set a timeout for  the server process  to wait for data from the client process. Inactive Sessions: These are sessions that remain connected to the database with a status in v$session of INACTIVE. Example of an INACTIVE session: - A user starts a program/session, then leaves it running and idle for an extended period of time. Database Resource Limits (using user profiles)  Implemented by      * Setting RESOURCE_LIMIT = TRUE in the database startup parameter file (spfile or pfile)      * Creating or modifying existing user profiles (DBA_PROFILES) to have one or more resource limit      * Assigning a profile to a user whose resources are wished to be limited It could happen that if the idle_time has been set on the DEFAULT profile, this can lead to an MTS dispatchers being  set to 'sniped' and then getting 'cleaned up' via the shell script. The removal of the dispatcher will result in other sessions  'dying' .In that case, If you are to implement resource limits, may be advisable to create new profiles that be assigned to users and not to change the characteristics of DEFAULT. Alternatively, if you do change DEFAULT, ensure that all the properties that you have affected have been fully tested in a development environment. When a resource limit is exceeded (for example IDLE_TIME) ... PMON does the following      * Mark the V$SESSION as SNIPED      * Clean up the database resources for the session      * Remove the V$SESSION entry When a resource limit is exceeded (for example IDLE_TIME) ... PMON marks the session as SNIPED in V$SESSION.   Then, AFTER the SNIPED session tries to execute any SQL statement, its database resources are cleaned up and its  V$SESSION entry is removed. Resource Manager Resource manager plans can be created to kill inactive sessions with high idle time. Refer to How To Automatic Kill  Inactive Sessions using Resource Manager (Doc ID 1935739.1). This document contains the details with an example.  You can customize the plan directives as per your requirement. In this case, once the inactive time goes beyond the specified MAX_IDLE_TIME, PMON marks the session as KILLED.  When this KILLED later tries to execute any SQL statement, its database resources are cleaned up and its V$SESSION  entry is removed. It is strongly recommended that both DCD and Resource Limits with Profiles be implemented in order to  clean up resources at both the database and OS level This combination will not clean up IDLE / ABANDONED / INACTIVE connections (OS processes) as these  sessions still have active clients For this case we will see that :      * PMON has cleaned up the V$SESSION entries .. but both the OS processes and the V$PROCESS entries  will still exist      * SQLNET will continue to be able to send the 10 byte packet successfully until the session is logged off This condition can be a major problem as      * The database exhausts PROCESSES and gives ORA-20 maximum number of processes exceeded      * The OS can become exhausted due to the un needed resources consumed by the abandoned processes The SYMPTOMS of this condition are      * The database view V$PROCESS will have no corresponding V$SESSION entry      * An OS process / thread still exists for the SNIPED session The solutions to this scenario can are to cleanup the OS processes ... after which the V$PROCESS entries should  be removed automatically Methods to cleanup OS processes:      * UNIX : kill -x ... the OS process at the OS level (typically kill -9)      * UNIX:  if using a dedicated server, use the following shell script to kill the shadow process  (script has been tested on Solaris, AIX, Tru64 and  HPUX): #!/bin/sh tmpfile=/tmp/tmp.$$ sqlplus system/manager <spool $tmpfile select p.spid from v\$process p,v\$session s where s.paddr=p.addr and s.status in ('INACTIVE,'SNIPED'); spool off EOF for x in `cat $tmpfile | grep "^[0123456789]"` do kill -9 $x done rm $tmpfile NOTE: If you are running in a shared server environment, you need to be careful not to accidentally kill your  dispatchers and/or shared servers. In Oracle 10.2 (or higher) a dedicated connections  V$SESSION + V$PROCESS + OS Process can be cleaned up with ALTER SYSTEM DISCONNECT SESSION ',' IMMEDIATE At this point in versions prior to 10.2 and for shared server connections the only solution is to kill the session  at the OS level (see Kill and ORAKILL above)      * Windows : use the orakill command .... orakill (see Note 69882.1 for details) On occasions we see conditions where a database session has a V$SESSION.STATUS = SNIPED ... and the entry  never goes away . This condition can be achieved by implementing Database Resource Limits + Profiles without DCD  and allow the database session to exceed the limit in the profile Summary of what was discussed here:1) DCD initiates clean up of OS and database processes  that have disconnected / terminated abnormally 2) DCD will not initiate clean up sessions that are still connected ... but are idle / abandoned / inactive 3) Database Resource Limits + user Profiles clean up database resources for user sessions that exceed resource limits 4) Database Resource Limits + user Profiles will not clean up OS processes 5) If DCD and Database Resource Limits + user Profiles are used in combination .. Dead Connections OS and Database  Resources will be cleaned up  6) IDLE / ABANDONED / INACTIVE sessions OS processes will not be cleaned  up even if DCD and Database Resource Limits + user Profiles are used in combination ... these must be cleaned  up manually 7) Resource Manager can be used to kill inactive session, which exceeds the idle time mentioned in the plan directives. REFERENCES NOTE:395505.1 - How to Check if Dead Connection Detection (DCD) is Enabled in 9i ,10g and 11g NOTE:805586.1 - Troubleshooting Session Administration NOTE:151972.1 - Dead Connection Detection (DCD) Explained NOTE:438923.1 - How To Track Dead Connection Detection(DCD) Mechanism Without Enabling Any  Client/Server Network Tracing NOTE:1287854.1 - Troubleshooting Guide - ORA-20: Maximum Number Of Processes (%S) Exceeded NOTE:1935739.1 - How To Automatic Kill Inactive Sessions using Resource Manager