The GET_DDL and the GET_GRANTED_DDL functions of the DBMS_METADATA package allow you to extract the source for nearly everything in your database.

DBMS_METADATA.GET_DDL (
object_type     IN VARCHAR2,
name            IN VARCHAR2,
schema          IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

DBMS_METADATA.GET_GRANTED_DDL (
object_type     IN VARCHAR2,
grantee         IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT 'DDL',
object_count    IN NUMBER   DEFAULT 10000)
RETURN CLOB;

With this demo, you grant some privileges to the DEMO user, and then by using both the GET_DDL and the GET_GRANTED_DDL functions, you extract all privileges you've provided to that user earlier.

Create a user for the demo

-- Create the DEMO user
-- DROP USER DEMO CASCADE;

CREATE USER DEMO IDENTIFIED BY DEMO DEFAULT TABLESPACE USERS;

Provide some grant to the DEMO user

-- Granting some system roles to the DEMO user

GRANT CONNECT, RESOURCE TO DEMO;

-- Granting some system privileges to the <span class="hlw">DEMO</span> user

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

-- Granting SELECT privileges on some system views to the <span class="hlw">DEMO</span> user

GRANT SELECT ON V_$SESSION TO DEMO;
GRANT SELECT ON V_$SESSION_EVENT TO DEMO;

Connect as the DEMO user and create some objects

-- Connect 

SQL> CONNECT DEMO/DEMO
Connected.

-- Create a table 

CREATE TABLE MYTABLE(DIGIT NUMBER, TEXT VARCHAR2(10));

-- Create an index 

CREATE INDEX MYINDEX ON MYTABLE(DIGIT);

-- Create a directory 

CREATE OR REPLACE DIRECTORY MYDIRECTORY AS '/tmp';

At this point, there are 3 manually created objects in the database - the Table, the Index, and the Directory.

Before retrieving any metadata for the objects, let's customize the DBMS_METADATA package output. It's an optional step.

SET PAGES 0
SET LINES 3000
SET LONG 2000000000
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON 
SET SERVEROUTPUT ON SIZE 1000000
COLUMN TXT FORMAT a2500 WORD_WRAPPED

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', FALSE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);

Now let’s get DDL statements of 3 formerly created objects with the GET_DDL function.

-- Connect as a privileged user

SQL> SHOW USER
USER is "SYS"


-- Retrieve a DDL for the table 

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','MYTABLE','DEMO') AS TXT FROM DUAL;

  CREATE TABLE "DEMO"."MYTABLE" ("DIGIT" NUMBER, "TEXT" VARCHAR2(10)) ;


-- Retrieve a DDL for the index

SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','MYINDEX','DEMO') AS TXT FROM DUAL;

  CREATE INDEX "DEMO"."MYINDEX" ON "DEMO"."MYTABLE" ("DIGIT") ;


-- Retrieve a DDL for the directory 

SQL> SELECT DBMS_METADATA.GET_DDL('DIRECTORY','MYDIRECTORY') AS TXT FROM DUAL;

   CREATE OR REPLACE DIRECTORY "MYDIRECTORY" AS '/tmp';

To get OBJECT, SYSTEM, or ROLE privileges for the created user, use the GET_GRANTED_DDL function.

-- Connect as a privileged user

SQL> SHOW USER
USER is "SYS"


-- Retrieve SYSTEM privileges granted to the DEMO user 

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEMO') AS TXT FROM DUAL;

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


-- Retrieve OBJECT privileges granted to the DEMO user 

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','DEMO') AS TXT FROM DUAL;

  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;


-- Retrieve ROLEs granted to the DEMO user 

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','DEMO') AS TXT FROM DUAL;

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

Both object DDL statements and user grants returned by the functions are identical to the statements at the beginning of the demo.

The GET_DDL function also retrieves DDL for other types of database objects such as DB_LINK, CONSTRAINT, USER, TABLESPACE, etc. You can find a complete list of supported objects in the DOCUMENTATION.

-- Connect as a privileged user

SQL> SHO USER
USER is "SYS"


-- Retrieve metadata for the DEMO user

SQL> SELECT DBMS_METADATA.GET_DDL('USER','DEMO') AS TXT FROM DUAL;

   CREATE USER "DEMO" IDENTIFIED BY VALUES 'S:6D1451646D30...BD6B' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP";


-- Retrieve metadata for the USERS tablespace

SQL>  EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE);
SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE','USERS') AS TXT FROM DUAL;

  CREATE TABLESPACE "USERS" DATAFILE
  '/u01/app/oracle/oradata/TST19C/users01.dbf' SIZE 5242880
  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

That’s it. The demo is completed. Drop the DEMO user

SQL> DROP USER DEMO CASCADE;

Written At

14 NOV 202114:00

OEL Release

7.9 x64

Database Release

19.12.0.0.0