In our databace there are around 300 sessions were in inactive state.
Database is not releas the sessions. How to fix it
Full Version: inactive sessions in database
"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
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.
The Windows command to kill this session would be as follows.
C:\oracle9i\bin>orakill ORCL92 768
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
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.
The Windows command to kill this session would be as follows. C:\oracle9i\bin>orakill ORCL92 768
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.