LLI Wiki — My Digital Knowledge Base

Extract Oracle object DDL with DBMS_METADATA

Examples of DBMS_METADATA.GET_DDL and GET_GRANTED_DDL for tables, indexes, procedures, grants and profiles.

The GET_DDL function of the DBMS_METADATA package extracts the DDL of objects in an Oracle database.

Syntax

DBMS_METADATA.GET_DDL('OBJECT_TYPE','OBJECT_NAME','SCHEMA')

All values are VARCHAR2: write them between single quotes and in upper case. If the schema is omitted, the current schema is used.

a) DDL of all tables of a schema

SELECT DBMS_METADATA.GET_DDL('TABLE', u.table_name, u.owner)
FROM DBA_TABLES u WHERE u.owner = '&owner';

SQL*Plus prompts for the schema name, for example Enter value for owner: SCOTT.

b) DDL of all indexes of a schema

SELECT DBMS_METADATA.GET_DDL('INDEX', u.index_name, u.owner)
FROM DBA_INDEXES u WHERE u.owner = '&owner';

c) DDL of a specific table

SELECT DBMS_METADATA.GET_DDL('TABLE', '&table_name', '&owner') FROM dual;

Example input: Enter value for owner: SCOTT, Enter value for table_name: EMP.

d) DDL of all tables of the current schema

SELECT DBMS_METADATA.GET_DDL('TABLE', a.table_name)
FROM USER_ALL_TABLES a
WHERE a.nested = 'NO' AND (a.iot_type IS NULL OR a.iot_type = 'IOT');

Nested tables and overflow segments are excluded.

e) DDL of the system privileges granted to a user

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'SCOTT') FROM dual;

Output:

GRANT CREATE ANY DIRECTORY TO "SCOTT"
GRANT CREATE SYNONYM TO "SCOTT"

f) DDL of all procedures of a schema

SELECT DBMS_METADATA.GET_DDL('PROCEDURE', u.object_name, u.owner)
FROM dba_objects u
WHERE u.object_type = 'PROCEDURE' AND u.owner = '&owner';

g) DDL of all functions of a schema

SELECT DBMS_METADATA.GET_DDL('FUNCTION', u.object_name, u.owner)
FROM DBA_OBJECTS u
WHERE u.object_type = 'FUNCTION' AND u.owner = '&owner';

h) DDL of all synonyms of a schema

SELECT DBMS_METADATA.GET_DDL('SYNONYM', u.synonym_name, u.owner)
FROM DBA_SYNONYMS u WHERE u.owner = '&owner';

i) DDL of all sequences of a schema

SELECT DBMS_METADATA.GET_DDL('SEQUENCE', u.sequence_name, u.sequence_owner)
FROM DBA_SEQUENCES u WHERE u.sequence_owner = '&owner';

j) Other granted privileges

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'APP_USER') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'APP_USER') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'APP_USER') FROM dual;

Role, view, synonym

SELECT DBMS_METADATA.GET_DDL('ROLE', 'ROLE_NAME') FROM dual;
SELECT DBMS_METADATA.GET_DDL('VIEW', 'VIEW_NAME', 'SCHEMA') FROM dual;
SELECT DBMS_METADATA.GET_DDL('SYNONYM', 'NAME', 'SCHEMA') FROM dual;

Other usable object types

AUDIT AUDIT_OBJ CLUSTER
COMMENT CONSTRAINT DB_LINK
DEFAULT_ROLE DIRECTORY FUNCTION
INDEX_STATISTICS INDEX_TYPE OBJECT_GRANT
PACKAGE PROCEDURE PROFILE
ROLE ROLE_GRANT ROLLBACK_SEGMENT
SEQUENCE SYNONYM SYSTEM_GRANT
TABLE TABLE_DATA TABLE_STATISTICS
TABLESPACE TABLESPACE_QUOTA TRIGGER
USER VIEW

Generate the DDL of profiles

To migrate profiles from a source database to a target database, generate one call per profile:

SELECT UNIQUE 'SELECT DBMS_METADATA.GET_DDL(''PROFILE'',' || '''' || PROFILE || ''') FROM DUAL;'
FROM DBA_PROFILES;

Then use the resulting rows in the following script (three example profiles here):

SET HEADING OFF
SET TRIMSPOOL ON
SET FEEDBACK OFF
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', TRUE);
-- your calls to dbms_metadata here
SELECT DBMS_METADATA.GET_DDL('PROFILE','PROF') FROM dual;
SELECT DBMS_METADATA.GET_DDL('PROFILE','DEFAULT') FROM dual;
SELECT DBMS_METADATA.GET_DDL('PROFILE','APP_PROFILE') FROM dual;
Mastodon LLIMastodon SocialFosstodon