I will start posting some of my old personal notes, that are still valid on day a day oracle database support, just clarifying that they are not new but it can help
this note is just for get DDL metada from a specific user, it will extract most of the metadata related to it.
set heading off; set echo off; Set pages 999; set long 90000; SYS@orcl > SELECT dbms_metadata.get_ddl('USER','SCOTT') FROM dual; DBMS_METADATA.GET_DDL('USER','SCOTT') -------------------------------------------------------------------------------- CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" SYS@orcl > SELECT DBMS_METADATA.GET_GRANTED_DDL( 2 'ROLE_GRANT','SCOTT') from dual; DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCOTT') -------------------------------------------------------------------------------- GRANT "CONNECT" TO "SCOTT" GRANT "RESOURCE" TO "SCOTT" SYS@orcl > SELECT DBMS_METADATA.GET_GRANTED_DDL( 2 'OBJECT_GRANT','SCOTT') from dual; DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SCOTT') -------------------------------------------------------------------------------- GRANT SELECT ON "HR"."EMPLOYEES" TO "SCOTT" SYS@orcl > SELECT DBMS_METADATA.GET_GRANTED_DDL( 2 'SYSTEM_GRANT','SCOTT') from dual; DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT') -------------------------------------------------------------------------------- GRANT UNLIMITED TABLESPACE TO "SCOTT" SYS@orcl > SELECT dbms_metadata.get_ddl('ROLE','RESOURCE') from dual; DBMS_METADATA.GET_DDL('ROLE','RESOURCE') -------------------------------------------------------------------------------- CREATE ROLE "RESOURCE" SYS@orcl > SELECT DBMS_METADATA.GET_GRANTED_DDL( 2 'SYSTEM_GRANT','RESOURCE') from dual; DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','RESOURCE') -------------------------------------------------------------------------------- GRANT CREATE INDEXTYPE TO "RESOURCE" GRANT CREATE OPERATOR TO "RESOURCE" GRANT CREATE TYPE TO "RESOURCE" GRANT CREATE TRIGGER TO "RESOURCE" GRANT CREATE PROCEDURE TO "RESOURCE" GRANT CREATE SEQUENCE TO "RESOURCE" GRANT CREATE CLUSTER TO "RESOURCE"