-
Users not logged over 3 month
Hi,
Could someone please help me with a query to find out the users who have not logged into the database for 3 months or above??
Thanks in advance.
Regards,
Aakriti
-
I believe there is no straight forward view is available to check the last log in date unless you enable auditing for that user.
Thanks,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
Hi,
Thanks for the response.
Will audit enabling help in getting the list of users logged into the database 3 months ago??
Thanks in advance
Regards
-
Well, I think... enabling the auditing on the database will take care of the user's login time from current date.
But, I would like to know if it is possible to determine the last login date and time by a user into an oracle instance at present,or say, users who logged in 3months ago.
Could you please help
Thanks in advance.
Regards,
Aakriti
-
you have already been given your answer
-
if you don't want to enable auditing, create on log on trigger and store the date/time in a table and you can query from that table any time.
Thanks,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
Hi Vnktummala,
Could you please guide me in creating the log on trigger and store the date/time in a table.
Thanks in Advance.
-
some thing like this ... test before you drop in production.
CREATE OR REPLACE TRIGGER SYS.ON_LOGON_USR
AFTER LOGON ON DATABASE
WHEN ( USER = ' ' )
BEGIN
execute immediate 'insert into table_name values (SYSDATE)';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
Thanks,
Last edited by vnktummala; 08-17-2010 at 01:35 AM.
Reason: codes
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
Just stumbled upon this, it maybe of help.
http://www.dba-oracle.com/art_builder_sec_audit.htm
connect sys/manager;
create table
stats$user_log
(
user_id varchar2(30),
session_id number(8),
host varchar2(30),
last_program varchar2(48),
last_action varchar2(32),
last_module varchar2(32),
logon_day date,
logon_time varchar2(10),
logoff_day date,
logoff_time varchar2(10),
elapsed_minutes number(8)
)
;
Designing a logon trigger
Once the table is designed, the next step is to create a system-level logon trigger that fills in as much information as possible at the time of the logon event. Listing B illustrates the logon audit trigger that I created.
create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into stats$user_log values(
user,
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
null,
null,
null,
sysdate,
to_char(sysdate, 'hh24:mi:ss'),
null,
null,
null
);
END;
/
As you can see, I populated this table with values that are available at logon time:
create or replace trigger
logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
BEGIN
-- ***************************************************
-- Update the last action accessed
-- ***************************************************
update
stats$user_log
set
last_action = (select action from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
--***************************************************
-- Update the last program accessed
-- ***************************************************
update
stats$user_log
set
last_program = (select program from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the last module accessed
-- ***************************************************
update
stats$user_log
set
last_module = (select module from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff day
-- ***************************************************
update
stats$user_log
set
logoff_day = sysdate
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff time
-- ***************************************************
update
stats$user_log
set
logoff_time = to_char(sysdate, 'hh24:mi:ss')
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Compute the elapsed minutes
-- ***************************************************
update
stats$user_log
set
elapsed_minutes =
round((logoff_day - logon_day)*1440)
where
sys_context('USERENV','SESSIONID') = session_id;
END;
/
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|