@@header

/*
*
*  Author  : Vishal Gupta
*  Purpose : Display User Password history
*  Parameters : 1 - DBUserName (Use % as wildcard, Default value '%')
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  26-May-16  Vishal Gupta  Created
*
*/


/************************************
*  INPUT PARAMETERS
************************************/
UNDEFINE USERNAME
DEFINE USERNAME="&&1"  

set term off
COLUMN  _USERNAME          NEW_VALUE  USERNAME             NOPRINT
SELECT DECODE('&&USERNAME','','%','&&USERNAME')   "_USERNAME"
FROM DUAL;
set term on


PROMPT ************************************************
PROMPT * User Password History
PROMPT * 
PROMPT * Input Parameters 
PROMPT *  - User Name = '&&USERNAME'
PROMPT ************************************************
PROMPT 
PROMPT 

SELECT u.username
     , h.password
     , h.password_date 
 FROM dba_users u 
      JOIN sys.user_history$ h ON h.user# = u.user_id
WHERE UPPER(u.username) = UPPER('&&USERNAME') 
ORDER BY u.username
       , h.password_date
;


@@footer