How to extract metadata of users, tables, views, triggers, and other Oracle database objects by using the metadata API.
DEMO

DEMO: HOW TO RETRIEVE AN OBJECT DDL OR A USER DDL USING PL/SQL

RELATED

HOW TO FIND SYSTEM, ROLES AND OBJECTS PRIVILEGES THAT HAVE BEEN GRANTED TO A USER
HOW TO FIND ALL OBJECTS PRIVILEGES GRANTED TO A USER IN AN ORACLE DATABASE

QUERY

The DBT_RETRUSMETA is a PL/SQL anonymous block that retrieves metadata for a user.

SQL> @DBT_RETRUSMETA.sql

Enter value for username: demo
old   2: USERNAME VARCHAR2(40)  := UPPER('&USERNAME');
new   2: USERNAME VARCHAR2(40)  := UPPER('demo');

----------------------------------------
-- FETCHING USER METADATA
----------------------------------------


   CREATE USER "DEMO" IDENTIFIED BY VALUES 'S:AEF622A2197A3D29323B2AF584A8B0E624DD8036A55CC09F5672F94EBC6F;
T:DA0EBA577B30B24A860B87CB89A17764B37A22B4AA0310A9F4403A4532E55CDD802C05BDC9FA82761E3E1BC45177AF04B83004A1E
5037CC9AA60F6BBFB498D7BF428F7B81DD7F0DF74CBF45C186F2234' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP";

----------------------------------------
-- FETCHING SYSTEM PRIVILEGIES
----------------------------------------


  GRANT UNLIMITED TABLESPACE TO "DEMO";
  GRANT CREATE ANY DIRECTORY TO "DEMO";

----------------------------------------
-- FETCHING ROLE PRIVILEGIES
----------------------------------------


   GRANT "CONNECT" TO "DEMO";
   GRANT "RESOURCE" TO "DEMO";

----------------------------------------
-- FETCHING OBJECT PRIVILEGIES
----------------------------------------


  GRANT SELECT ON "SYS"."V_$SESSION" TO "DEMO";
  GRANT SELECT ON "SYS"."V_$SESSION_EVENT" TO "DEMO";
  GRANT EXECUTE ON DIRECTORY "MYDIRECTORY" TO "DEMO" WITH GRANT OPTION;
  GRANT READ ON DIRECTORY "MYDIRECTORY" TO "DEMO" WITH GRANT OPTION;
  GRANT WRITE ON DIRECTORY "MYDIRECTORY" TO "DEMO" WITH GRANT OPTION;

Written At
14 NOV 202113:40
OEL Release
7.9 x64
Database Release
19.12.0.0.0