Use DBMS_METADATA to extract the complete DDL (metadata) of an Oracle user, including system, object and role grants. This is handy when cloning a user or migrating it to another database. 1. User DDL set long 999999 select dbms_metadata.get_ddl('USER','<username>') from dual; 2. System privilege grants select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','<username>') from dual; 3. Object grants select dbms_metadata.get_granted_ddl('OBJECT_GRANT','<username>') from dual; 4. Role grants select dbms_metadata.get_granted_ddl('ROLE_GRANT','<username>') from dual; Tip: run set long 999999 and set pagesize 0 first so the output isn't truncated. If a grant type has no rows, you'll get ORA-31608; that just means the user has none of that type.
One blog for all ORACLE DBA scripts & AWS Cloud commands, OCI commands etc