Generate user DDL

The oracle DBA can use these 2 scripts to generate DDL statements for a user with their roles, system and object privileges.

On the other side, with datapump (impdp) you can use the parameter sqlfile=My_file.sql you can easily get DDL from dumpfile

For Oracle >=10:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

clear screen accept uname prompt 'Enter User Name : ' accept outfile prompt  ' Output filename : ' spool &&outfile..gen SET LONG 2000000 PAGESIZE 0 head off verify off feedback off linesize 132 SELECT dbms_metadata.get_ddl('USER','&&uname') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&uname') from dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&uname') from dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&&uname') from dual; spool off

More information for this package in the official Oracle 12c documentation :
http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_metada.htm#ARPLS026

For Oracle <10 10g="" 11g="" runs="" strong="" too="" well="" with="">:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

clear screen accept uname prompt 'Enter User Name : ' accept outfile prompt  ' Output filename : ' col username noprint col lne newline set heading off pagesize 0 verify off feedback off linesize 132 spool &&outfile..gen prompt  -- genarate user ddl SELECT username, 'CREATE USER '||username||' '||        DECODE(password, 'EXTERNAL', 'IDENTIFIED EXTERNALLY',               'IDENTIFIED BY VALUES '''||password||''' ') lne,        'DEFAULT TABLESPACE '||default_tablespace lne,        'TEMPORARY TABLESPACE '||temporary_tablespace||';' lne   FROM DBA_USERS  WHERE USERNAME LIKE UPPER('%&&uname%')     OR UPPER('&&uname') IS NULL  ORDER BY USERNAME; SELECT username, 'ALTER USER '||username||' QUOTA '||        DECODE(MAX_BYTES, -1, 'UNLIMITED', TO_CHAR(ROUND(MAX_BYTES/1024))||'K')        ||' ON '||tablespace_name||';' lne   FROM DBA_TS_QUOTAS  WHERE USERNAME LIKE UPPER('%&&uname%')     OR UPPER('&&uname') IS NULL  ORDER BY USERNAME; col grantee noprint select grantee, granted_role granted_priv,        'GRANT '||granted_role||' to '||grantee||        DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')   from dba_role_privs  where grantee like upper('%&&uname%')          UNION select grantee, privilege granted_priv,        'GRANT '||privilege||' to '||grantee||        DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')   from dba_sys_privs  where grantee like upper('%&&uname%')  order by 1, 2; spool off