Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, mark.

Asked: May 29, 2020 - 1:03 pm UTC

Answered by: Chris Saxon - Last updated: May 29, 2020 - 1:27 pm UTC

Category: SQL - Version: 12.1

Viewed 100+ times

You Asked

I have some code (see below) which tells me the username and last_login for any login not used in the last 15 days.
I would like to add a 3rd column to the results, which shows the number of days sine the last login, to make it easier for the person running the report to see how long the account has been inactive.
Results would look something like USERNAME, LAST LOGIN, NUMBER OF DAYS.

I cannot seem to get it to work. Any suggestions>?

CODE without additional 'NUMBER OF DAYS' coding:


select username, last_login
from
dba_users
where username in 
(select username from all_users where oracle_maintained = 'N')
and 
username NOT IN ('DBAMETRICS')
and last_login <= TRUNC(SYSDATE) - 15
order by last_login ASC;


and we said...

Have you tried subtracting the login date from sysdate?

select username, last_login,
       TRUNC(SYSDATE) - trunc ( last_login ) days
from   dba_users
where  username in  (select username from all_users where oracle_maintained = 'N')
and    username NOT IN ('DBAMETRICS')
and    last_login <= TRUNC(SYSDATE) - 15
order by last_login ASC;

USERNAME    LAST_LOGIN                  DAYS   
HR          12-MAY-2020 15.29.45 +01         17 

and you rated our response

  (1 rating)

Reviews

Excellent!

May 29, 2020 - 1:32 pm UTC

Reviewer: mark dickinson from philadelphia area

Answer works perfectly. Thanks a million!

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.