user ddl

set head off
REM set long 150
set linesize 150
set longchunksize 150
set pages 0
set long 99999999
set feedback off
set echo off
spool /home/oracle/user_MIGRATE.sql
SELECT DBMS_METADATA.GET_DDL('USER',USERNAME) || ';' DDL
FROM DBA_USERS
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',USERNAME) || ';' DDL
FROM DBA_USERS
where exists (select 'x' from dba_role_privs drp where drp.grantee=upper('USERNAME'))
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',USERNAME) || ';' DDL
FROM DBA_USERS
where exists (select 'x' from dba_sys_privs drp where drp.grantee=upper('USERNAME'))
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',USERNAME) || ';' DDL
FROM DBA_USERS
where exists(select 'x' from dba_tab_privs drp where drp.grantee=upper('USERNAME'))
/
select 'grant '||GRANTED_ROLE||' to '||GRANTEE||';'from DBA_ROLE_PRIVS
/
spool off


ddl username

createUser.sql

set pages 800 lines 300  long 99999
select DBMS_METADATA.GET_DDL('USER','&&USER') ddl from dual union all
select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&USER') ddl from dual union all
select DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&USER') ddl from dual