Duplicate an Oracle database user
User creation:

select dbms_metadata.get_ddl( 'USER', 'PHIL' ) from dual;


Default role:

select dbms_metadata.get_granted_ddl( 'DEFAULT_ROLE', 'PHIL' ) from dual;


System grants:

select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'PHIL' ) from  dual;


Object grants:

select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'PHIL' ) from dual;


Role grants:

select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'PHIL' ) from dual;


Quotas:

select dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'PHIL' ) from dual;


If any of the above have no output, you'll get an exception that looks something like this:

SQL&gt; select dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'PHIL' ) from dual;
ERROR:
ORA-31608: specified object of type TABLESPACE_QUOTA not found
ORA-06512: at "SYS.DBMS_METADATA", line 4018
ORA-06512: at "SYS.DBMS_METADATA", line 5991
ORA-06512: at line 1

no rows selected

SQL&gt;


Then do a search and replace on the output to change the username.