The oracle DBA can use these 2 scripts to generate DDL statements for a user with their roles, system and object privileges.
For Oracle 18c / 12c / 11g / 10g:
clear screen accept uname prompt 'Enter User Name : ' accept outfile prompt ' Output filename : ' spool &&outfile..gen SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON BEGIN DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true); DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true); END; / 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 the package dbms_metadata, function get_ddl user in the official Oracle 18c/12c documentation :
https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_METADATA.html
For Oracle <10 (runs well too with 10g, 11g, 12.2 and 18c):
clear screen accept uname prompt 'Display the DDL for this specific user: ' accept outfile prompt ' Output filename : ' col username noprint col lne newline SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON spool &&outfile..gen prompt -- generate 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
Another use of this procedure is to copy a user account from one Oracle instance to another. With the same password, grants and roles without using the expdp/impdp tools.
Another method to retreive the Data Description Language for an Oracle user with all roles and Privileges:
With datapump (impdp) you can use the parameter sqlfile=My_file.sql you can easily get DDL from dumpfile:
http://www.oracle-scripts.net/standard-datapump-use/
Author: Vincent Fenoll
Compatibility: Oracle 18c, 12c, 11g