Re: User Connections

From: AJ Allen <aallen20_at_ford.com>
Date: Tue, 15 Feb 2000 15:54:16 -0500
Message-ID: <38A9BCF8.A916422A@ford.com>

Mark Young wrote:

> 
> Can any body tell me the sql to check the amount of users connected to an
> Oracle server?
> 

Try this. It works for me.
- - - - - - C U T H E R E - - - - - - - SET PAGESIZE 70
SET LINESIZE 80
SET NEWPAGE 0

SET VERIFY     ON
SET ECHO       OFF

SET UNDERLINE =
SET HEADING ON
SET FEEDBACK Off
SET LONG 1000 --SPOOL SessionDetail.lst

COLUMN sys_id             NOPRINT new_value system_id
COLUMN sys_date           NOPRINT new_value system_date
COLUMN sys_user           NOPRINT new_value system_user

COLUMN sid                FORMAT 9999999  HEADING 'Session|ID     '
COLUMN serial#            FORMAT 9999999  HEADING 'Serial|Number'
COLUMN pid                FORMAT 9999999  HEADING 'Process|ID     '
COLUMN status             FORMAT a8       HEADING 'Status'
COLUMN username           FORMAT a12      HEADING 'User name'
COLUMN lockwait           FORMAT a8       HEADING 'Lock|Waited'
COLUMN current_statement  FORMAT a15      HEADING 'Current|Statement'

TTITLE Left   'probe_303'         -
       Right  'Date: ' system_date   -
  skip Left   'Database: ' system_id -
       Center 'Monitor Oracle Database Sessions'  -
       Right  'User: ' system_user   -
  skip Center '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' -

  skip 2

REPFOOTER SKIP 3 CENTER '### ## END OF REPORT ## ###' BTITLE -
SKIP 2 RIGHT 'Page: ' format 99 sql.pno - SKIP 1 CENTER '### ## Confidential Information -- Dispose of properly ## ###'

SELECT  d.name               sys_id,
        to_char(SysDate,'mm/dd/yyyy  HH24:MI:SS') sys_date,
        User                 sys_user,
        S.SID, 
        S.SERIAL#, 
        P.PID, 
        S.STATUS, 
        S.USERNAME, 
        LOCKWAIT,
        DECODE(S.COMMAND,  0,  'NONE',          1, 'CREATE TABLE',    2,


'INSERT',


                           3,  'SELECT',        4, 'CREATE CLUSTER',  5,


'ALTER CLUSTER',


                           6,  'UPDATE',        7, 'DELETE',          8,


'DROP CLUSTER',


                           9,  'CREATE INDEX', 10, 'DROP INDEX',     11,


'ALTER INDEX',


                          12, 'DROP TABLE',    13, 'UNDEFINED',      14,


'UNDEFINED',


                          15, 'ALTER TABLE',   16, 'UNDEFINED',      17,


'GRANT',


                          18, 'REVOKE',        19, 'CREATE SYNONYM', 20,


'DROP SYNONYM',


                          21, 'CREATE VIEW',   22, 'DROP VIEW',      23,


'UNDEFINED',


                          24, 'UNDEFINED',     25, 'UNDEFINED',      26,


'LOCK TABLE',


                          27, 'NO OPERATION',  28, 'RENAME',         29,


'COMMENT',


                          30, 'AUDIT',         31, 'NO AUDIT',       32,


'CREATE EXTERNAL DATABASE',


                          33, 'DROP EXTERNAL DATABASE',  34, 'CREATE
DATABASE', 35, 'ALTER DATABASE',
                          36, 'CREATE ROLLBACK SEGMENT', 37, 'ALTER
ROLLBACK SEGMENT',
                          38, 'DROP ROLLBACK SEGMENT',   39, 'CREATE
TABLESPACE',
                          40, 'ALTER TABLESPACE',        41, 'DROP
TABLESPACE', 42, 'ALTER SESSION',
                          43, 'ALTER USER',    44, 'COMMIT',         45,


'ROLLBACK',


                          46, 'SAVEPOINT',         'UNKNOWN')

current_statement

  FROM  v$database d,
        V$SESSION  S,
        V$PROCESS  P

 WHERE S.PADDR = P.ADDR
 ORDER

    BY  s.status,
        S.SID,
        P.PID,
        S.SERIAL#

;
-- SPOOL PFF

• - - - - - - - - - - - - - - - - - - - - -

--

*------------------------------*----------------------------*
| Andrew Allen                 | Against fools, the gods    |
| Process Leadership / PSL DBA | themselves contend in vain |
| Dearborn, MI USA             | -- author unknown          |
*------------------------------*----------------------------*

Received on Tue Feb 15 2000 - 14:54:16 CST