user_info.sql

This script will help you find database user information such as account status, creation date, password expiration date, and more.

set echo off
set define '&'
define username = &1
set tab off verify off
col USERNAME for a25
col ACCOUNT_STATUS for a15
col PROFILE for a30
col CREATED heading 'User|created_dt' for a18
col PTIME heading 'Password|last|changed_dt' for a18
col LTIME heading 'Password|last|locked_dt' for a18
col EXPIRY_DATE heading 'Password|expire|date' for a18
col default_tablespace heading Tablespace for a25
col  "User account status" for a80
set lines 250 pages 49999

SELECT
  d.name DB_NAME,
  du.username,
  du.default_tablespace,
  du.account_status,
  du.profile,
  to_char(du.created,'DD-MON-YY HH24:MI:SS') created,
  to_char(u.ptime,'DD-MON-YY HH24:MI:SS') ptime,
  to_char(du.expiry_date,'DD-MON-YY HH24:MI:SS') expiry_date,
  to_char(u.ltime,'DD-MON-YY HH24:MI:SS') ltime
FROM
  dba_users du,
  v$database d,
  sys.user$ u
WHERE
    username LIKE upper('%'|| TRIM('&username')|| '%')
    AND user# = user_id;

			

Note: To run this script, the user needs DBA privileges and SELECT privileges on the USER$ table.