Oracle Tips by Burleson

Chapter 9 Oracle Security Internals Scripts

COLUMN username                 FORMAT a12 HEADING User
COLUMN lock_date                FORMAT a11 HEADING 'Date|Locked'
COLUMN expiry_date              FORMAT a11 HEADING 'Expiry|Date'
COLUMN profile                  FORMAT a15 HEADING Profile
 
SET PAGESIZE 58  LINESIZE 131 FEEDBACK OFF
ttitle 'ORACLE USER EXPIRATION REPORT'
BREAK ON username SKIP 1 ON default_tablespace ON temporary_tablespace ON
profile ON account_status
SPOOL user_expire
rem
 
SELECT
   username,
   default_tablespace,
   temporary_tablespace,
   profile,account_status,
   TO_CHAR(lock_date,'dd-mon-yyyy') lock_date,
   TO_CHAR(expiry_date,'dd-mon-yyyy') expiry_date
FROM
   sys.dba_users
ORDER BY
   username,
   default_tablespace,
   temporary_tablespace,
   profile,
   account_status;