All users (non-CDB pre-12c)

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 
ORDER BY username
/

All users (12c CDB)

alter session set container=CDB$ROOT;


SET LINESIZE 230
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'
COLUMN common                FORMAT a6     HEAD 'COMMON'
COLUMN pdb_name              FORMAT a20    HEAD 'PDB name'

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
  , COMMON											 common
  , pdb.name										 pdb_name
FROM
    cdb_users       a
  , v$pwfile_users  p
  , v$pdbs			pdb
WHERE
    p.username (+) = a.username 
	and pdb.con_id = a.con_id
ORDER BY username
/