An account can exist with nobody connected. A session appears only after a successful login, and it goes away when that connection ends. The account definition stays.
Oracle DBA Lesson 30B — Checking Current Database Connections
An Oracle account is a named identity used to log in. A session is the database-side connection created when a client logs in. The same account can have several sessions. Use that split when you need to know whether an application connected, whether those sessions are working or idle, and how the count changes.
Account information and current sessions
The data dictionary stores definitions. Dynamic performance views show activity in the running instance. DBA_USERS describes accounts in the current container. V$SESSION describes current sessions. Oracle updates the dynamic views as activity changes. See data dictionary and dynamic performance views.
| View | What it shows |
|---|---|
DBA_USERS |
Account definition in the current container |
V$SESSION |
Current sessions in this instance |
GV$SESSION |
Sessions from qualified instances, with INST_ID |
For a local account such as COURSE_READER, query the definition from that account's PDB. Root DBA_USERS does not list local PDB accounts.
SELECT username, account_status
FROM dba_users
WHERE username = 'COURSE_READER';ACCOUNT_STATUS is login state for the account, such as OPEN or LOCKED. It is not the activity status of one session. See DBA_USERS.
From a root monitoring connection that can see the PDB, list current connections:
SELECT con_id, username, status
FROM v$session
WHERE username = 'COURSE_READER';Each row is a session this observer can see. The same username can appear more than once. CON_ID is that row's container. An empty result means this query saw no matching session. Check the observer's container and privileges before you treat that as "nobody can connect."
ACTIVE means the session is executing SQL, including SQL that is waiting. INACTIVE means the session is idle between calls. An inactive session can still hold an open transaction. Status alone does not tell you it is safe to disconnect. KILLED and SNIPED mean something else. See V$SESSION.
Add the session identifiers when you need to tell one connection from the next:
SELECT con_id, sid, serial#, username, status
FROM v$session
WHERE username = 'COURSE_READER';Oracle can reuse a SID after a session ends. SERIAL# separates successive sessions that used that SID. In a multi-instance report, keep INST_ID as well. These queries do not kill a session.
Preserve the observation context
Record the sample from the same monitoring connection. Keep the three results together.
SELECT SYSTIMESTAMP AS sample_time, instance_name, status
FROM v$instance;
SELECT SYS_CONTEXT('USERENV','CON_NAME') AS query_container,
SYS_CONTEXT('USERENV','SESSION_USER') AS observer
FROM dual;
SELECT con_id, sid, serial#, username, status
FROM v$session
WHERE username = 'COURSE_READER';SYSTIMESTAMP is the database host time, with time zone. V$INSTANCE.STATUS is the instance startup state. The query container is where the observer is connected. Each session row's CON_ID is that row's container. A root observer can see a PDB session with CON_ID = 3. See SYSTIMESTAMP and V$INSTANCE.
Dynamic values change while Oracle reads them. Rows in one result can come from slightly different moments, and separate statements run at different times. Keep query order and the sample time with the output. The timestamp marks when you looked. It does not freeze every row. For a connection that is coming and going, take short repeated samples of the same user, instance, and container, and compare the counts. See dynamic performance views.
Keep the queries simple. If you need joins, sorts, or aggregates, copy the rows into a table first and query that copy. The statements above stay as direct reads.
Startup and instance scope
Which views you can query depends on how far startup has gone. V$INSTANCE is available at NOMOUNT. V$DATAFILE needs MOUNT. Application sessions belong to an open database or PDB.
V$ views are for one instance. GV$ views pull the same information from qualified instances and add INST_ID for the source instance.
SELECT inst_id, con_id, sid, serial#, username, status
FROM gv$session
WHERE username = 'COURSE_READER';Keep INST_ID when you compare sessions across instances. Managing Oracle RAC is a separate topic. See Oracle RAC performance views.
Practice
Use the existing reader account and a separate observer. Do not create or drop the account.
- In the reader's PDB, confirm the account row with the
DBA_USERSquery. - From the monitor, take a sample before the reader connects. Keep other sessions for that account closed so the change is obvious.
- Open one reader connection and leave it at the prompt. Take another sample. Note
SID,SERIAL#, container, and status. - Close only that reader session with
EXIT ROLLBACK. After cleanup, sample again. Compare the recorded session identity. Do not assume a fixed count if other users are connected. - Query the account again in its PDB. It is still there, ready for the next login.
You are done when you can point at the session that appeared and explain each sample with time, instance, and container. The only cleanup is closing that reader connection. Leave private application data out of the notes.
No comments:
Post a Comment