Oracle DBA · Lesson 2
What a Session Is
“I have 2,000 connections” might mean sockets, sessions, or server processes. Those are not the same bill — and they fail in different ways.
Watch on YouTube
Oracle DBA Lesson 2 — What a Session Is
AI Ops Pro
Four pieces on the path
User process — lives on the client (SQL*Plus, an app server, a tool). It does not itself update data files.
Server process — lives on the database host. It parses SQL, reads buffers, and applies changes for a session.
Connection — the communication path (IPC on the same host, or Oracle Net across the network).
Session — the logged-in state: user, schema, NLS, open transaction, session parameters.
You can have a connection without a useful session (listener answered, auth failed). Session state can outlive a single call.
Dedicated server picture
Default picture to hold: one user process ↔ one server process ↔ one session.
Where work happens: the server process uses the SGA (shared) and its PGA (private).
Why DBAs care: session counts drive PGA memory, process counts drive OS limits, and “kill the connection” is not always “rollback and go.”
Commands to try
SELECT sid, serial#, username, program, status
FROM v$session
WHERE type = 'USER';User sessions only. SID + SERIAL# identify a session for ALTER SYSTEM KILL SESSION.
SELECT s.sid, s.serial#, s.username, p.spid, p.program
FROM v$session s
JOIN v$process p ON p.addr = s.paddr
WHERE s.type = 'USER';SPID is the OS process id on the database host — not the client’s SQL*Plus pid.
Gotchas
- Connection ≠ session. A socket can exist before (or without) a logged-in session.
- The client process does not read table blocks; a server process on the database host does.
- Dedicated server: one server process per session — until you deliberately use shared server.
Quick quiz
Choose one answer per question, then submit. You’ll see the correct answer and a short why.
No comments:
Post a Comment