Oracle8 question

[Marty Metras]

In Oracle8 you can see when a users is created in the DBA_USERS table.
Is there a table in the database when you can see when the users loggen into the database last?

Marty

[Vijaya Kumar_3]

Can you check these tables:

V$STATNAME
V$SESSTAT
and
V$SESSION

[Marty Metras]

No not there. That shows the session information.
I'm looking for a table that shows when they connected to the database last.

Marty

[Hein van den Heuvel]

I suspect you need to enable a level of AUDIT to retain that info in the DB.

try SQL> DESCRIBE USER_AUDIT_SESSION

That looks like the table you would want filled in, but is has no rows on my box.


I found that table doign a few probes like:
select TABLE_NAME, COLUMN_NAME from ALL_TAB_COLS where COLUMN_NAME = 'USERNAME'

and

select TABLE_NAME, COLUMN_NAME from ALL_TAB_COLS where COLUMN_NAME like '%LAST%'

having started with:
select TABLE_NAME from DICT where TABLE_NAME like '%LOGIN%';

[Marty Metras]

I checked these and a couple others and still find nothing that looks like a field that would show last login date/time.

I am trying to clean out inactive users.
Our ERP system on UNIX lets me get the login from the OS. Many of the users access the Database thru the report writer and never login thru the OS. I can get the connection information from the Listener log. That is not easy. I was think that all the neat thing that you can do with Oracle and SQL that the must be a table out there with the last login information.

Guess I can keep looking.
Marty

[Arturo Galbiati]

Hi Marty,
to easy fix your problem I suggest you to create a trigger to be onvoked at logon to store in a table the info you need.
This is a short example about this:


create table t_users_logon
(ul_user_name varchar2(30),
ul_logon_date date
)
storage (initial 5M
next 1M
pctincrease 0
maxextents 250
)
tablespace users;
create or replace trigger t_logontrigger
after logon on database
declare
l_count number;
begin
select count(*)
into l_count
from t_users_logon
where ul_user_name=user;
if l_count > 0 then
update t_users_logon
set ul_logon_date=sysdate
where ul_user_name=user;
else
insert into t_users_logon values (user,sysdate);
end if;
commit;
end;

in this way you wil; be able to see the last logon of the user.
Feel free to add all the fields you need in the table. Look for trigger at logon to see all the field available.

I hope this help you.
Art

[Marty Metras]

Thanks Art,
That might be the way to go. I haven done any triggers before this would be a good first one.
Marty