Account details

SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY   off

COLUMN username              FORMAT a15    HEAD 'Username'
COLUMN account_status        FORMAT a17    HEAD 'Status'
COLUMN expiry_date           FORMAT a20    HEAD 'Expire Date'
COLUMN default_tablespace    FORMAT a25    HEAD 'Default Tbs.'
COLUMN temporary_tablespace  FORMAT a10    HEAD 'Temp Tbs.'
COLUMN created               FORMAT a20    HEAD 'Created On'
COLUMN profile               FORMAT a10    HEAD 'Profile'
COLUMN sysdba                FORMAT a6     HEAD 'SYSDBA'
COLUMN sysoper               FORMAT a7     HEAD 'SYSOPER'

SELECT distinct
    a.username                                       username
  , a.account_status                                 account_status
  , TO_CHAR(a.expiry_date, 'DD-MON-YYYY HH24:MI:SS') expiry_date
  , a.default_tablespace                             default_tablespace
  , a.temporary_tablespace                           temporary_tablespace
  , TO_CHAR(a.created, 'DD-MON-YYYY HH24:MI:SS')     created
  , a.profile                                        profile
  , DECODE(p.sysdba,'TRUE', 'TRUE','')               sysdba
  , DECODE(p.sysoper,'TRUE','TRUE','')               sysoper
FROM
    dba_users       a
  , v$pwfile_users  p
WHERE
    p.username (+) = a.username 
  and UPPER(a.username) like '%&USERNAME%'
ORDER BY username
/

Extract user password

select  extract(xmltype(dbms_metadata.get_xml('USER',username)),'//USER_T/PASSWORD/text()').getStringVal()
 from  dba_users where upper(username) = upper('%&USERNAME%');

Extract user password in CREATE USER command

select 'alter user "'||username||'" identified by values '''||extract(xmltype(dbms_metadata.get_xml('USER',username)),
'//USER_T/PASSWORD/text()').getStringVal()||''';'  
	old_password from  dba_users where upper(username) = upper('%&USERNAME%');