topic Re: Oracle8 question in Operating System - HP-UX

Oracle8 question

Marty Metras — Fri, 30 Jan 2004 14:45:52 GMT

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

Re: Oracle8 question

Vijaya Kumar_3 — Fri, 30 Jan 2004 15:02:47 GMT

Can you check these tables:

V$STATNAME
V$SESSTAT
and
V$SESSION

Re: Oracle8 question

Marty Metras — Fri, 30 Jan 2004 15:35:20 GMT

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

Marty

Re: Oracle8 question

Hein van den Heuvel — Fri, 30 Jan 2004 16:05:41 GMT

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%';

Re: Oracle8 question

Marty Metras — Fri, 30 Jan 2004 16:53:35 GMT

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

Re: Oracle8 question

Arturo Galbiati — Mon, 02 Feb 2004 07:11:28 GMT

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

Re: Oracle8 question

Marty Metras — Tue, 03 Feb 2004 09:23:36 GMT

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