Get create user DDL with grants, privileges and roles

rem script: user_cr_ddl.sql
rem  Purpose: generate create user script with privs
rem
rem  Usage: user_cr_ddl
rem
rem  Note:
rem   If ORA-31608 encountered, it means the user does not
rem   have grants in that category. Edit the spooled script
rem   as ncessary
rem
rem
SET LINESIZE 200
SET PAGESIZE 0 FEEDBACK off VERIFY off
-- SET TRIMSPOOL on
SET LONG 1000000
-- COLUMN ddl_string FORMAT A100 WORD_WRAP
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
COLUMN ddl FORMAT A4000
define username=&&1
spool &username._cr_ddl.sql
SELECT DBMS_METADATA.GET_DDL('USER', upper('&username') )  DDL FROM dual;
prompt -- Role
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', upper('&username'))  DDL from dual
where exists ( select 1 from dba_role_privs  where grantee=upper('&username') );
prompt -- Sys priv
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', upper('&username'))  DDL FROM  dual
where exists ( select 1 from dba_sys_privs  where grantee=upper('&username') );
prompt -- Object priv
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', upper('&username'))  DDL
FROM dual where exists ( select 1 from dba_tab_privs  where grantee=upper('&username') );
prompt -- tablespace quota
SELECT  DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA',upper('&username')) DDL from dual
where exists ( select 1 from dba_ts_quotas  where username=upper('&username') );
spool off
exit