René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Oracle Session

Creating a session with SQL*Plus

The command in SQL*Plus to create a session is connect.

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.

Processes

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
  • sessions per user
  • cpu per session
  • maximum connect time
  • maximum idle time
  • maximum block reads per session
  • maximum amount of private SGA.

Package variables

If a package declares a package variable, this variable is not shared with other sessions.

Isolation levels

The isolation level for a session governs how the session sees data changed in other sessions.

Tracing SQL statements of a session

The SQL statements executed by a session can be traced with dbms_support.start_trace.

Synchronizing sessions

Sessions can synchronize themselves with other sessions (for example if a session has to wait until another has completed a task) using dbms_lock. See this link.