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;