Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: about find unused users in oracle database

Re: about find unused users in oracle database

From: joel garry <joel-garry_at_home.com>
Date: Wed, 01 Aug 2007 11:48:39 -0700
Message-ID: <1185994119.037291.249810@g12g2000prg.googlegroups.com>


On Jul 31, 11:25 am, esthershe..._at_yahoo.com wrote:
> HI,
>
> I'm wondering is there any simple way to find unused oracle users for
> a period of time ? ( which has not logged into the oracle database for
> some period time ).
>
> I found we could enable audit connect or create logon/logoff trigger
> to find those info.
>
> But is there any simple way ( like record login info in oracle
> database somewhere )
>
> Thanks very much for any suggestion on it.

If your users only come in through the listener, and you have the log turned on, you can mine the listener log, compare to usernames in dba_users. Quick and dirty mine:

grep USER /oracle9/app/oracle/product/9.2.0/network/log/listener.log| \
awk 'BEGIN
 {FS="("} {for (i=1;i<=NF;i++) {if (index($i,"USER")>0) { split($i,j,")") split(j[1],k,"=");print k[2] } } }'| \ sort -u

For those who don't know awk, that uses ( as a field separator, loops through all the fields looking for the string USER, splits any found using ) as a field seperator, then using = as a separator, takes the second field and prints it, the USER.

jg

--
@home.com is bogus.
http://eyeonoracle.blogs.techtarget.com/2007/08/01/does-oracle-11g-have-a-shot-with-smbs/
Received on Wed Aug 01 2007 - 13:48:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US