[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How can I check user login times in 11g

Posted on 2011-10-13
9
Medium Priority
?
297 Views
Last Modified: 2013-01-19
Hi guys, which view I can use to check a particular user's login and logoff times.
0
Comment
Question by:nirvairghuman
7 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36963889
If you don't have auditing turned on I believe you will need a system trigger to track this.

There might be other options that I do not know about.
0
 

Author Comment

by:nirvairghuman
ID: 36963994
Our auditing is on,
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36964032
If you are auditing the session then you should be able to query the audit tables.

On mobile and cannot search for examples but there should be examples in the online docs on querying the audit trail.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 74

Accepted Solution

by:
sdstuber earned 336 total points
ID: 36964120
eslect * from dba_audit_trail where action_name in ('LOGON','LOGOFF') and username = 'YOURUSER'
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 164 total points
ID: 36968400
I have used this simple approach (in the SYS schema):

1. Create the table to capture the LOGIN and LOGOUT timings.

DROP TABLE ICW_logonaudittable;

CREATE TABLE ICW_logonaudittable
(
  event       VARCHAR2(10),
  sid         NUMBER,
  serial#     NUMBER,
  timestamp   DATE,
  username    VARCHAR2(30),
  osuserid    VARCHAR2(30),
  machinename VARCHAR2(64),
  ip          VARCHAR2(20)
);
 


2. Create LOGON trigger at DATABASE level.
 
CREATE OR REPLACE TRIGGER logonauditing AFTER LOGON ON database
DECLARE
  machinename VARCHAR2(64);
  osuserid    VARCHAR2(30);
  v_sid       NUMBER(10);
  v_serial    NUMBER(10);
  v_ip        VARCHAR2(20);
   
  CURSOR c1 IS
    SELECT sid, serial#, osuser, machine, sys_context('userenv','ip_address')
      FROM v$session WHERE audsid = userenv('sessionid');
BEGIN
  OPEN c1;
  FETCH c1 INTO v_sid, v_serial, osuserid, machinename, v_ip;
  INSERT INTO ICW_logonaudittable VALUES ( 'LOGON', v_sid, v_serial, sysdate,
      user, osuserid, machinename, v_ip);
 
  CLOSE c1;
END;
/


3. Check the result:


set linesize 1000
set pagesize 1000
set trimspool on
set heading on
set underline '-'
column event format A9
column sid   format 99999
column serial# format 99999
column username format A20 wrap
column osuserid format A20 wrap
column machinename format A20 wrap
column timestamp format A30 wrap
select event, sid, serial#, username, osuserid,
        machinename,
       to_char(timestamp,'dd-mon-yyyy hh24:mi:ss') as TIMESTAMP
from ICW_logonaudittable;
0
 
LVL 3

Expert Comment

by:gajmp
ID: 36990258
schwertner:
      They have already enable AUDIT then why we need to maintain system level trigger and user defined audit table?
      I accept system audit table wont maintain IP address. but you have used that. I didnt think IP will be useful in all the cases.

      All the application will maintain application level audit that is another context.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 336 total points
ID: 36990273
>>> why we need to maintain system level trigger

there isn't a "need",  it's simply an alternate approach.


however in 11g the comment_text of dba_audit_trail may contain tns connect info that includes client ip/name

for example....

Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=57278))

it's not reliable for all sessions, but many
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question