safe kill inactive sessions oracle Oracle disconnect user

Is it safe to kill inactive sessions in Oracle?

Terminating Sessions and Cleaning Up Processes

This module describes how you can terminate sessions and clean up session-related operating system processes.

This module discusses the following:

Overview
Terminating Sessions Using SQL*Plus
Terminating Sessions Using Enterprise Manager
Terminating Session-related Operating System Processes on UNIX
Terminating Session-related Operating System Processes on Windows

Move your mouse over this icon to show all screenshots. You can also move your mouse over each individual icon to see only the screenshot associated with it.

Back to List of Topics

In this module you will learn how to terminate sessions using SQL*Plus and Enterprise Manager.

You should always terminate user sessions using Oracle tools. However, if the operating system process related to a terminated Oracle user session remains active, you can kill the session-related operating system process by following the appropriate steps for your operating system:

Back to List of Topics

You can terminate sessions with the ALTER SYSTEM KILL command. When you issue the ALTER SYSTEM KILL session command, you must specify the session's index number and serial number. To identify the session index number (sid) and serial number of a session, query the V$SESSION dynamic performance view as shown below. The value of the STATUS column will be ACTIVE when the session is making a SQL call to Oracle. It will be INACTIVE if it is not making a SQL call to Oracle.

Identify the correct session and terminate the session by performing the steps below:

1.

Invoke SQL*Plus.

2.

Query V$SESSION supplying the username for the session you want to terminate:

SELECT SID, SERIAL#, STATUS, SERVER
FROM V$SESSION
WHERE USERNAME = '<username>'

3.

Execute the ALTER SYSTEM command to terminate the session:

ALTER SYSTEM KILL SESSION '<sid, serial#>'

4.

Query V$SESSION:

SELECT SID, SERIAL#, STATUS, SERVER
FROM V$SESSION
WHERE USERNAME = '<username>'

5.

After PMON has cleaned up after the session, the row is removed from V$SESSION:

SELECT SID, SERIAL#, STATUS, SERVER
FROM V$SESSION
WHERE USERNAME = '<username>'

Back to List of Topics

Identify the correct session and terminate the session by performing the steps below.

Note: Oracle Enterprise Manager 9.2 was used in the examples.

1.

Select START > Programs > Oracle > OraHome92 > Enterprise Manager Console. Select Launch Standalone and click OK.

2.

Expand Databases. Expand your database. Expand Instance and select Sessions. Identify the session you want to terminate.

3.

Expand Sessions. Select the session you want to terminate and click Kill Session:

4.

Select Immediate and click OK:

5.

STATUS changes to KILLED and SERVER changes to PSEUDO:

6.

The row is removed after PMON has cleaned up after the session:

Back to List of Topics

The PMON background process will clean up after any user session you terminate with the ALTER SYSTEM KILL SESSION command. You can kill the session-related operating system process by performing the steps outlined below:

1.

Invoke SQL*Plus.

2.

Issue the following query to determine the operating system process identifier (spid):

SELECT spid
FROM v$process
WHERE NOT EXISTS (SELECT 1 FROM v$session
WHERE paddr = addr);

3.

If you are unable to identify the operating system process identifier (spid) from the query in step 2, you can issue the following query to help identify the correct session:

SELECT s.sid, s.serial#, p.spid FROM v$process p, v$session s WHERE p.addr = s.paddr AND s.username = '<username>';
4.

At the operating system prompt, issue the kill command and supply the operating system process identifier (spid):

kill <spid>

Back to List of Topics

The PMON background process will clean up after any user session you terminate with the ALTER SYSTEM KILL SESSION command. You can kill the session-related operating system process by performing the steps outlined below:

1.

Invoke SQL*Plus.

2.

Issue the following query to determine the operating system process identifier (spid) or thread:

SELECT spid, s.osuser, s.program
FROM v$process p, v$session s
WHERE p.addr = s.paddr;

3.

At the operating system prompt, issue the orakill command. Supply the SID and the thread which you obtained from the SPID column in step 2:

orakill <sid> <thread>

Move your mouse over this icon to hide all screenshots

Can we kill inactive session in Oracle?

You can terminate sessions with the ALTER SYSTEM KILL command. When you issue the ALTER SYSTEM KILL session command, you must specify the session's index number and serial number. To identify the session index number (sid) and serial number of a session, query the V$SESSION dynamic performance view as shown below.

What does Inactive session mean in Oracle?

It just means that someone is logged in but not executing SQL right at that instant. Your very own session is "INACTIVE" everytime you are in the process of typing in a query via sqlplus. It is a normal state to be in.

Why are there so many inactive sessions in Oracle?

Large numbers of long term inactive sessions are typically caused by problems with an application or an application server not handling its connections properly. The obvious thing to do is correct the problem at the source.

How do I kill an inactive session?

Update If you want to kill all the sessions, you could just prepare a small script. SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session; Spool the above to a . sql file and execute it, or, copy paste the output and run it.

Related Posts

Internal proliferation pepper safe to eat
Does Sports Afield make a good gun safe?
Is it safe to pet a horse
What yarn material is best for babies?

Toplist

Latest post

TAGs

Q&A What When Why Who How to Reviews Best Ways How long Step Deal Health Review Calories Guide Lose weight Game