CODEWITHSUNDEEP
sqloracle-databasesqlplus
fnr
fnr

Reputation: 9667

How can I get log info from DB user with SQL Plus?

we are using an Oracle database in our Company. For regulatory reasons we need a "report" with all DB users which were used to update the database of our Software in 2018 and their login and logoff timestamps. I know that Oracle has a tool which tracks that but unfortunately that feature was never used by our Company an is still deactivated. Is something like this still possible to get?

What I need should actually look like this:
1. Column: db user
2. Column: Login timestamp
3. Column: Logoff timestamp

Could be something like:

prod_user | 01.02.2018 05:30:05 | 01.02.2018 05:35:30  
prod_user | 02.02.2018 10:06:16 | 02.02.2018 10:48:58

Best regards

Upvotes: 0

Views: 968

Answers (2)

GMB
GMB

Reputation: 222482

No.

Unless you enable auditing in the database and audit connect, Oracle by default does not store user login/logout.

From the AUDIT SESSION documentation :

This option generates a single audit record for each session created by connections to an instance. An audit record is inserted into the audit trail at connect time and updated at disconnect time. This record can include connection time, disconnection time, and logical and physical I/O processed, among other information.

For what it's worth, you can access the last login date of any user by looking at column LAST_LOGIN in administrative view DBA_USERS. This is a regular feature, that does not require auditing to be enabled.

Upvotes: 2

Elie Nassif
Elie Nassif

Reputation: 462

given that the user you are using has sufficient permissions you can use the DBA_AUDIT_SESSION view to check the info you need. Documentation Oracle 10g

Upvotes: 0

Related Questions