dba:kill_disconnect_session
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende ÜberarbeitungNächste Überarbeitung | Vorhergehende Überarbeitung | ||
dba:kill_disconnect_session [2015/01/02 19:26] – [Eine Sessoin beenden] gpipperr | dba:kill_disconnect_session [2015/01/16 16:23] (aktuell) – [Eine gekillete Session in der DB wiederfinden] gpipperr | ||
---|---|---|---|
Zeile 118: | Zeile 118: | ||
=== Eine gekillete Session in der DB " | === Eine gekillete Session in der DB " | ||
- | Wenn eine Session mit "kill Session" | + | Wenn eine Session mit "kill Session" |
- | und unter einen " | + | |
+ | Der PMON Prozess prüft regelmäßig auf diese " | ||
Dies hat aber zur Folge das unter der ursprüngliche PADDR Adresse in der V$SESSION der Prozess nicht mehr gefunden werden kann, eine neue PADDR wird vergeben. | Dies hat aber zur Folge das unter der ursprüngliche PADDR Adresse in der V$SESSION der Prozess nicht mehr gefunden werden kann, eine neue PADDR wird vergeben. | ||
- | Um das nun nach zuverfolgen stehen in der V$SESSION diese Spalten | + | Um das nun nach zuverfolgen stehen in der V$SESSION diese Spalten zur Verfügung: |
* CREATOR_ADDR - state object address of creating process | * CREATOR_ADDR - state object address of creating process | ||
* CREATOR_SERIAL# | * CREATOR_SERIAL# | ||
- | Mit der CREATOR_ADDR | + | Mit der CREATOR_ADDR |
- | + | ||
- | SQL: | + | |
- | < | + | |
- | select spid | + | |
- | , program | + | |
- | from v$process | + | |
- | where program!= ' | + | |
- | and addr not in (select paddr from v$session) | + | |
- | and addr not in (select paddr from v$bgprocess) | + | |
- | and addr not in (select paddr from v$shared_server) | + | |
- | / | + | |
- | + | ||
- | </ | + | |
**ab 11g 11.1.0.6 ** | **ab 11g 11.1.0.6 ** | ||
- | Folgende zwei neue Views helfen die Session besser wieder | + | Folgende zwei neue Views helfen die Session besser wieder |
Zeile 162: | Zeile 150: | ||
+ | |||
+ | <code sql> | ||
+ | ttitle left " | ||
+ | |||
+ | |||
+ | column process_id format a8 | ||
+ | column inst_id | ||
+ | column username | ||
+ | column osusername | ||
+ | column pname | ||
+ | |||
+ | select --p.inst_id | ||
+ | to_char(p.spid) as process_id | ||
+ | , p.username as osusername | ||
+ | , p.pname | ||
+ | , p.program | ||
+ | from v$process p | ||
+ | where p.program!= ' | ||
+ | and p.addr not in (select gv.paddr from v$session gv) | ||
+ | and p.addr not in (select bg.paddr from v$bgprocess bg) | ||
+ | and p.addr not in (select ss.paddr from v$shared_server ss) | ||
+ | --order by p.inst_id | ||
+ | / | ||
+ | |||
+ | -- new column creator_addr in v$session! | ||
+ | |||
+ | ttitle left "get the prozess of a killed session with the help of the creator_addr" | ||
+ | |||
+ | |||
+ | select --p.inst_id | ||
+ | to_char(p.spid) as process_id | ||
+ | , p.username as osusername | ||
+ | , p.pname | ||
+ | , p.program | ||
+ | from v$process p | ||
+ | where p.addr in (select gv.creator_addr from v$session gv where status in (' | ||
+ | / | ||
+ | |||
+ | ttitle off | ||
+ | </ | ||
+ | |||
+ | |||
+ | === SNIPED Sessions ==== | ||
+ | .. | ||
+ | |||
+ | |||
+ | Sniped - the session has passed the idle_time limit defined in user profile. The session will remain snipped until the client communicates with the db again, when it will get " | ||
+ | |||
+ | ... | ||
+ | |||
+ | When IDLE_TIME is set in the users' profiles or the default profile. This will kill the sessions in the database (status in v$session now becomes SNIPED) and they will eventually disconnect. It does not always clean up the Unix session (LOCAL=NO sessions). At this time all oracle resources are released but the shadow processes remains and OS resources are not released. This shadow process is still counted towards the parameters of init.ora. | ||
+ | This process is killed and entry from v$session is released only when user again tries to do something. Another way of forcing disconnect (if your users come in via SQL*Net) is to put the file sqlnet.ora on every client machine and include the parameter " | ||
+ | |||
+ | ... | ||
+ | |||
+ | see https:// | ||
<note important> | <note important> |
dba/kill_disconnect_session.txt · Zuletzt geändert: 2015/01/16 16:23 von gpipperr