How to extract user details in Oracle

-- Usage: @ get_user_ddl username

SET pagesize 0 linesize 999 LONG 9999 longchunksize 9999 verify OFF feedback OFF

COLUMN MYUSER ON NEW_VALUE MYUSER NOPRINT
UNDEFINE MYUSER

SELECT USERNAME AS MYUSER FROM DBA_USERS WHERE USERNAME = UPPER('&1');

prompt -- create profile
SELECT
  CASE PROFILE
    WHEN 'DEFAULT'
    THEN '/* user &MYUSER has DEFAULT profile */'
    ELSE CAST( dbms_metadata.get_ddl( 'PROFILE', PROFILE ) || '/' AS VARCHAR2(4000) )
  END
FROM DBA_USERS
WHERE USERNAME = '&MYUSER';

prompt -- create user
SELECT dbms_metadata.get_ddl( 'USER', '&MYUSER' ) || '/' FROM DUAL;

prompt -- create roles
SELECT
  REGEXP_REPLACE( dbms_metadata.get_ddl( 'ROLE', GRANTED_ROLE ), '('||CHR(10)||' *){2}|\s+$', ';\1' )
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = '&MYUSER';

prompt -- grant roles to roles
SELECT
  REGEXP_REPLACE( dbms_metadata.get_granted_ddl( 'ROLE_GRANT', GRANTED_ROLE ), '('||CHR(10)||' *){2}|\s+$', ';\1' )
FROM DBA_ROLE_PRIVS a
WHERE GRANTEE = '&MYUSER'
  AND EXISTS (SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = a.GRANTED_ROLE);

prompt -- grant system privileges to roles
SELECT
  REGEXP_REPLACE( dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', GRANTED_ROLE ), '('||CHR(10)||' *){2}|\s+$', ';\1' )
FROM DBA_ROLE_PRIVS a
WHERE GRANTEE = '&MYUSER'
  AND EXISTS (SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = a.GRANTED_ROLE);

prompt -- grant object privileges to roles
SELECT
  REGEXP_REPLACE( dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', GRANTED_ROLE ), '('||CHR(10)||' *){2}|\s+$', ';\1' )
FROM DBA_ROLE_PRIVS a
WHERE GRANTEE = '&MYUSER'
  AND EXISTS (SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE = a.GRANTED_ROLE);

prompt -- grant roles
SELECT
  REGEXP_REPLACE( dbms_metadata.get_granted_ddl( 'ROLE_GRANT', GRANTEE ), '('||CHR(10)||' *){2}|\s+$', ';\1' )
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = '&MYUSER'
  AND ROWNUM <= 1;

prompt -- assign default roles
SELECT dbms_metadata.get_granted_ddl( 'DEFAULT_ROLE', '&MYUSER' ) || '/' FROM DUAL;

prompt -- grant system privileges
SELECT
  REGEXP_REPLACE( dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', GRANTEE ), '('||CHR(10)||' *){2}|\s+$', ';\1' )
FROM DBA_SYS_PRIVS
WHERE GRANTEE = '&MYUSER'
  AND ROWNUM <= 1;

prompt -- grant object privileges
SELECT
  REGEXP_REPLACE( dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', GRANTEE ), '('||CHR(10)||' *){2}|\s+$', ';\1' )
FROM DBA_TAB_PRIVS
WHERE GRANTEE = '&MYUSER'
  AND ROWNUM <= 1;

prompt -- assign tablespace quotas
SELECT
  dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', USERNAME ) || '/'
FROM DBA_TS_QUOTAS
WHERE USERNAME = '&MYUSER'
  AND ROWNUM <= 1;