|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
Creating a session with SQL*Plus
Forcing a session to be terminated
A session can be forced to be terminated with the alter system kill session statement.
Note: The alter system kill session statement will not make the session go away until it times out or it tries to issue another statement. However, pmon will rollback the work done by the session.
The server process waits then until the client tries to execute another statement to send it a ORA-03113 EOF on communication channel or "packet write failure". This is why killed sessions still show up in the v$session view.
However, if dead client detection (or is it dead connection detection?) is enabled, the session is removed as soon as the expiry time is reached.
To immediately release the locks (immediatly meaning: after the session's rollback), one should use kill -9 on unix or orakill on unix.
If there is a logoff trigger, it won't be executed for the killed session.
If a session belongs to a foreground process, it has v$session.type = 'USER'. If it belongs to a background process, it has v$session.type = 'BACKGROUND'.
Limiting session usage through profiles
Oracle can enforce some limits on the session usage through profiles. These limits are
If a package declares a package variable, this variable is not shared with other sessions.
The isolation level for a session governs how the session sees data changed in other sessions.
Tracing SQL statements of a session