Re: Help identifying logged in users.

From: Mary Kay Petersen <mkay_at_sequent.com>
Date: Thu, 2 Jul 92 17:00:24 GMT
Message-ID: <1992Jul2.170024.24171_at_sequent.com>

radar_at_busadm1.cba.hawaii.edu (James Johnson) writes:

>Hello netters,
 

>I've created the following query to help me track who has logged in or is
>still logged to the database. We're running an 6.0.29 kernal using OS/2 on
>an PS/2 mod80 accessed through a novell network by DOS workstations.
 

> SELECT SUBSTR(USERNAME,1,12) USERNAME,
> TIMESTAMP DAY,
> SUBSTR(TO_CHAR(TIMESTAMP,' HH24:MI'),1,10) LOGGED_IN,
> SUBSTR(TO_CHAR(LOGOFF_TIME,' HH24:MI'),1,10) LOGGED_OUT,
> SUBSTR(RETURNCODE,1,8) CODE
> FROM DBA_AUDIT_CONNECT
> WHERE USERNAME !='JIM'
> AND TO_CHAR(TIMESTAMP,'DD-MON-YY')=TO_CHAR(SYSDATE,'DD-MON-YY')
> ORDER BY TIMESTAMP
> /
 

>The problem is that the performance of this query is slowing down, because
>the view DBA_AUDIT_CONNECT is growing and I'm only concerned with say the
>past weeks worth of data. I am wondering about:
> 1. The relationship bewteen the view DBA_AUDIT_CONNECT and the
> underlying table AUD$. Rowcounts on both return 5080 for the view
> and 6408 for the table. What accounts for the differnce?
> 2. I'm thinking of deleting fron AUD$ where timestamp < sysdate - 7.
> Do you see any problems in doing this.
> 3. Is there a better way to monitor user connect information?
 

>Thanks in advance,
>Jim
>University of Hawaii
>Colege of Business

Perform a query on v$process. Here is an example query setup for a unix system.

---

set verify off

ttitle "Process Information"

column pid format 999 heading "PID"; column spid format 99999 heading "SPID";

column username       format a8       heading "Username";
column terminal       format a10      heading "TTY";
column program        format a45      heading "Program";

select pid,
       spid,
       username,
       terminal,
       program

from v$process
where pid like '%&oracle_pid%'
  and spid like '%&frontend_pid%'
  and username like lower('%&unix_username%')   and program like '%&program%%'
order by username;

---

This query allows you to qualify who you are looking for else if you enter nothing, it will query all.

---
  /_ _   /		sequent computer system, inc.	+-----+
 / / /  /__/ __		15450 sw koll parkway		|   # |
/ / /  / \__(_/_/_/	beaverton, or  97006-6063	|     |
mary kay petersen/	mkay_at_sequent.com 		+-----+
(503)578-5817 __/

Received on Thu Jul 02 1992 - 19:00:24 CEST