| |
 |
|
Oracle Tips by Burleson |
Monitoring Currently
Logged-in User Processes
A final report in this section that I have found useful lists
currently logged-in processes, their user IDs, and operating system
IDs, as well as any programs they are currently running. Of course,
the Q product on the Precise Web site does a better job, but I don’t
always have time to start it up just to check on users. The script,
called pid.sql, is shown in Source 11.13, and an example of its
output is shown in Listing 11.12.
SOURCE 11.13 Example of script to show active users.
REM
REM Name: pid.sql
REM
REM FUNCTION: Generate a list of current oracle sids/pids
REM
COLUMN terminal FORMAT a10 HEADING 'Terminal'
COLUMN program FORMAT a30 HEADING 'Program'
COLUMN pid FORMAT 9999 HEADING 'Process|ID'
COLUMN sid FORMAT 9999 HEADING 'Session|ID'
COLUMN osuser FORMAT A15 HEADING 'Operating|System|User'
COLUMN spid FORMAT A7 HEADING 'OS|Process|ID'
COLUMN serial# FORMAT 99999 HEADING 'Serial|Number'
SET LINES 132 PAGES 58
BREAK ON username
COMPUTE COUNT OF pid ON username
START title132 "Oracle Processes"
SPOOL rep_out\&db\cur_proc
SELECT
NVL(a.username,'Null') username,
b.pid,a.sid,
DECODE(a.terminal,'?','Detached',a.terminal) terminal,
b.program,b.spid,a.osuser,a.serial#
FROM
v$session a,
v$process b
See code depot
This is an excerpt by Mike Ault’s book “Oracle9i
Administration & Management” . If you want more current Oracle
tips by Mike Ault, check out his new book “Mike
Ault’s Oracle Internals Monitoring & Tuning Scripts” or Ault’s
Oracle Scripts Download. |
