Datasheet

46 Chapter 1

Introducing SQL
The following query may return several rows depending on the activity and number of
users connected to the database:
SELECT username, sid, serial#, program
FROM v$session;
If you’re using SQL*Plus, you may have to adjust the column width to fit the output in
one line:
COLUMN program FORMAT a20
COLUMN username FORMAT a20
SELECT username, sid, serial#, program
FROM v$session;
USERNAME SID SERIAL# PROGRAM
-------------------- ---------- ---------- -----------------
118 6246 ORACLE.EXE (W000)
BTHOMAS 121 963 sqlplus.exe
DBSNMP 124 23310 emagent.exe
DBSNMP 148 608 emagent.exe
150 1 ORACLE.EXE (FBDA)
152 7 ORACLE.EXE (SMCO)
155 1 ORACLE.EXE (MMNL)
156 1 ORACLE.EXE (DIA0)
158 1 ORACLE.EXE (MMON)
159 1 ORACLE.EXE (RECO)
164 1 ORACLE.EXE (MMAN)
… … … (Output truncated)
As you can see, the background processes do not have usernames. To find out only the
user sessions in the database, you can filter out the rows that do no have valid user-
names:
SELECT username, sid, serial#, program
FROM v$session
WHERE username is NOT NULL;
If you’re looking for specific information, you may want to add more filter conditions such
as looking for a specific user or a specific program. The following SQL returns the rows in
order of their session login time, with the most recent session on the top:
SELECT username, sid, serial#, program
FROM v$session
95127c01.indd 46 2/18/09 6:37:11 AM