SET SERVEROUTPUT ON
SET ECHO OFF
SET FEEDBACK OFF
SET PAGES 80
SET LINES 180
SET TRIMSPOOL ON

REM
REM || Title : show_role_users.sql
REM ||
REM || Purpose : This script will report all roles granted to users
REM || default tablespace, temporary tablespace, and profile.
REM ||
REM ||
REM || Release No : 1
REM ||
REM || Variables : None
REM ||
REM || Schema : system
REM ||
REM ||

COL username FORMAT a20 HEADING 'Username' JUSTIFY l
COL role FORMAT a40 HEADING 'Role (admin,grant)' JUSTIFY l
COL dts FORMAT a18 HEADING 'Default|Tablespace' JUSTIFY l
COL tts FORMAT a18 HEADING 'Temporary|Tablespace' JUSTIFY l
COL prof FORMAT a18 HEADING 'Profile' JUSTIFY l

BREAK -
ON username skip 1 -
ON role -
ON dts -
ON tts

SELECT username,
default_tablespace dts,
temporary_tablespace tts,
PROFILE prof,
granted_role||DECODE (admin_option, 'YES', ' | ADMIN', ' ')||
DECODE (granted_role,'YES', ' |GRANT', ' ') role
FROM dba_users,
dba_role_privs
WHERE dba_users.username = dba_role_privs.grantee
AND username NOT IN ('PUBLIC')
ORDER BY 1,2,3,4

spool show_role_users.lis
/
spool off

SET SERVEROUTPUT OFF
SET ECHO OFF
SET FEEDBACK ON
SET PAGES 60
CLEAR BREAKS