find last time users logged in

Robeen

Oracle Database 11.2.0.4

Hi,

how do I find last time users logged in and those who did not login for past 180 days

USERNAME                                      PROFILE                        ACCOUNT_STATUS

--------------------------------------------- ------------------------------ ------------------------------

SYS                                           DEFAULT                        OPEN

SYSTEM                                        DEFAULT                        OPEN

OUTLN                                         DEFAULT                        EXPIRED & LOCKED

DIP                                           DEFAULT                        EXPIRED & LOCKED

MUZAMMER                                      DEFAULT                        OPEN

DBSNMP                                        DEFAULT                        OPEN

APPQOSSYS                                     DEFAULT                        EXPIRED & LOCKED

WMSYS                                         DEFAULT                        EXPIRED & LOCKED

XS$NULL                                       DEFAULT                        EXPIRED & LOCKED

EXFSYS                                        DEFAULT                        EXPIRED & LOCKED

XDB                                           DEFAULT                        EXPIRED & LOCKED

USERNAME                                      PROFILE                        ACCOUNT_STATUS

--------------------------------------------- ------------------------------ ------------------------------

ANONYMOUS                                     DEFAULT                        LOCKED

ORDSYS                                        DEFAULT                        EXPIRED & LOCKED

ORDDATA                                       DEFAULT                        EXPIRED & LOCKED

ORDPLUGINS                                    DEFAULT                        EXPIRED & LOCKED

SI_INFORMTN_SCHEMA                            DEFAULT                        EXPIRED & LOCKED

MDSYS                                         NICE_PROFILE                   OPEN

MDDATA                                        DEFAULT                        EXPIRED & LOCKED

SPATIAL_WFS_ADMIN_USR                         DEFAULT                        EXPIRED & LOCKED

PP2                                           DEFAULT                        OPEN

SPATIAL_CSW_ADMIN_USR                         DEFAULT                        EXPIRED & LOCKED

DBADM                                         NICE_PROFILE                   OPEN

Regards,

Roshan

Robeen

last time users queried, I got the following query:

select username,machine,terminal,logon_time from v$session;

Mustafa_KALAYCI

you can use audit if you have, query dba_audit_Trail to see logon and off times.

PKR

Hi

See MOS DOC

How to Audit User Connection, Disconnection Date and Time (Doc ID 99786.1)

Rgrds

PKR

PKR

Hi

DB version 12c only

select USERNAME,LAST_LOGIN from dba_users

Rgrds

PKR

PKR

Hi,

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1830073957439

also MOS DOC

How to Audit Logon/Logoff Events with Triggers (Doc ID 70679.1)

Rgrds,

PKR

EdStevens

Robeen wrote:last time users queried, I got the following query:select username,machine,terminal,logon_time from v$session;

v$session shows only current sessions.  If I logged of 1 ms before you query v$session, you wont see the 'last time I queried".

Unless you are on 12c ( See PKR, msg #4) you will really have to role your own, by keeping a 'last logon' table, and populate it with a trigger.  Auditing is useful but not really a complete solution.  First, it can be turned off and back on, but more importantly, it will only give you the last logon of a user if that last logon was within the retention period of your audit trail.

I took the approach of a custom 'last_logon' table and a  trigger.  My table looks like this:

CREATE TABLE "LOCALDBA"."LAST_USER_LOGON"

  (    "USERNAME" VARCHAR2(30 BYTE),

       "OS_USERNAME" VARCHAR2(30 BYTE),

       "LAST_LOGON_DATE" DATE,

       "SOURCE_IND" CHAR(1 BYTE),

    CONSTRAINT "LAST_LOGON_PK" PRIMARY KEY ("USERNAME")

the logon trigger updates the row for the user that is logging on.  For an initial load of the table, I joined dba_users with dba_audit_trail, using the date of the most recent audited logon (if available) to populate LAST_LOGON_DATE.  If no audit record existed for a given user, I populated LAST_LOGON_DATE with sysdate.  SOURCE_IND is an 'A' (Audit) if the last logon was from the audit trail on the initial load, a 'D' (Default) if from sysdate on the initial load, and when updated by the trigger, SOURCE_IND is set to 'T' (Trigger).

DeepC

Login time populated only when you connect using SQL*PLUS. Using -nologintime switched off login time and no login time recording for SYSDBA,SYSDG and SYSBACKUP

Unknown

"Login time populated only when you connect using SQL*PLUS"

So this is a half-hearted, poorly conceived 'enhancement' to the data dictionary information?

EdStevens

Deep Chakraborty wrote:Login time populated only when you connect using SQL*PLUS. Using -nologintime switched off login time and no login time recording for SYSDBA,SYSDG and SYSBACKUP 

Huh?

What is that from, and what is it referencing?

AndrewSayer

EdStevens wrote:Deep Chakraborty wrote:Login time populated only when you connect using SQL*PLUS. Using -nologintime switched off login time and no login time recording for SYSDBA,SYSDG and SYSBACKUP Huh?What is that from, and what is it referencing?

To confirm:

If it means that you can disable LAST_LOGIN from being populated when you use the -nologintime switch, it's wrong. It still gets recorded.

If it means that when you login with something like sql developer: it won't get populated, it's wrong. It still gets recorded.

Can't confirm the SYS___ roles.