Help - Search - Members - Calendar
Full Version: inactive sessions in database
Oracle DBA Forums > Oracle > Oracle Forum
tulsidask@dcbl.com
In our databace there are around 300 sessions were in inactive state.

Database is not releas the sessions. How to fix it
aussie_dba
"In our databace there are around 300 sessions were in inactive state."

They don't have to be active to stay connected. What of these 3900 people are all out to lunch, or at a meeting?

Use a v$session script to walk back and see.

If you don't want to write your own download the Oracle scripts:

http://www.dba-oracle.com/oracle_scripts.htm

If you KNOW that you want them to disconnect after beconing idle, nuke them with the "idle time" option:

http://www.dba-oracle.com/t_connect_time_i...ire_timeout.htm

If you think they are zombies you can kill then with "aslter system kill session"

http://www.dba-oracle.com/t_alter_system_kill_session.htm

You can also have a sniped session:

http://www.rampant-books.com/t_oracle_snip...ns_ora00020.htm

CODE
select s.status, count(1), s.username from v$process p, v$session s
where paddr(+)=addr
group by s.status, s.username
order by 1;

STATUS     COUNT(1) USERNAME
-------- ---------- ------------------------------
ACTIVE            1 SYS
ACTIVE           16
INACTIVE          2 SYS
SNIPED            1 USR


And some you kill from the OS, using the PID:

http://www.dba-oracle.com/tips_killing_oracle_sessions.htm

1 - Gather session information from Oracle

2 - Kill the session at the OS-level

3 - Kill the session within Oracle using the "alter system kill session" command:

a) UNIX - I always locate the Server PID (SPID) from v$process and issue the UNIX kill -9 command.

cool.gif The Windows command to kill this session would be as follows.

C:\oracle9i\bin>orakill ORCL92 768
burleson


http://www.dba-oracle.com/t_inactive_sessions.htm
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.