Oracle Kill/Terminate Session: Example

 Introduction:

When a SQL session is hanging for a long time, it make sense to terminate/kill the session. This is to improve developer time as well as to free up system resources in server side.

Here are some scenarios where KILL SESSION is useful:

1. System is in deadlock
2. While testing a query, developer missed a join, which makes internal query processing prohibitively expensive
3. Some sessions are taking up a lot of resources and important processes or users get very slow response

 

Steps to Kill Session:

Find the Problem Session:

Identify the session that needs to be killed. Easiest way is to get the user by Database Schema/User name. But it is possible that a single DB user may have many sessions open due to multiple reasons. In that case, finding session is tricky. Use any of the WHERE clauses in the below query to identify the session.

 

-- get session by User/Schema
SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'BAI';
 
-- Get session by other parameters/inputs
SELECT SID || ',' || SERIAL# AS KILLSTR, SID, SERIAL#, X.*
FROM V$SESSION X
WHERE USERNAME = 'APPS'
AND MACHINE NOT LIKE 'XXABCWEB%' -- REMOVE WEB SERVER TO DB Server Connection
AND OSUSER NOT IN ('APPLPRD') -- REMOVE UNIX AND APPLN SERVER USERS
AND PROGRAM IN ('TOAD.EXE', 'SQL DEVELOPER', 'SQLPLUS.EXE') -- FILTER BY SQL Client Program
AND ROWNUM < 21;

 

Kill/Terminate the Problem Session:

Use inputs from above query in the KILL command

ALTER SYSTEM KILL SESSION '<SID>,<SERIAL>' IMMEDIATE;
 
ALTER SYSTEM KILL SESSION '1794,64680' IMMEDIATE
/
 

 

Keywords:

Kill Session, Terminate, Oracle, ALTER SESSION, PL/SQL, Query, SQL, SQLPLUS

Oracle Date Conversion Functions : Random Examples

 

Introduction:

Some random examples of Oracle PL/SQL Date Conversion functions. Might be useful for some and thought it worth sharing.

 

Examples:

Get current system date in default format (Easy !):

select sysdate from dual;

  

Get current system date in different formats:

select 'x ' || to_char(sysdate, 'yyyymmdd') from dual;
 
select 'x ' || to_char(sysdate, 'yyyymmdd hh24miss') from dual;
 
select 'x ' || to_char(sysdate, 'hh24miss') from dual;

 

Number of seconds since beginning of the day:

-- number of seconds since beginning of the day
select 'x ' || to_char(sysdate, 'sssss') from dual;

  

Number of days since beginning of the year:

-- number of days since beginning of year
select 'x ' || to_char(sysdate, 'ddd') from dual;
 

Get System Timestamp:

select systimestamp from dual;

This will return time up to 6 decimal places. Very useful for performance tuning/improvement as the time is recorded with microsecond accuracy.

Few other Timestamp functions/examples:

select 'x ' || to_char(systimestamp, 'dd-mon-yyyy hh.mi.ss.ff4') from dual;
select 'x ' || to_char(systimestamp, 'yyyymmdd hhmissff4') from dual;
 
select 'x ' || to_char(systimestamp, 'yyyymmdd hhmissff6') from dual;
select 'x ' || to_char(systimestamp, 'hhmissff6') from dual;
select 'x ' || to_char(systimestamp, 'missff6') from dual;
 

Keywords:

Date Conversion, Format, Timestamp, Systimestamp, Date Function, Oracle, PL/SQL, Query, SQL, SQLPLUS