Generating DDL of a schema

DB Scripts

These functions can be used as part of a select statement to produce output for groups of objects, such as object creation scripts, like the one shown below.

CONN scott/tiger

SET LONG 20000
SET PAGESIZE 0

SELECT DBMS_METADATA.get_ddl (object_type, object_name, USER)
FROM user_objects;

Note: the output does not include terminators (semicolon 😉 The formatting of the output is improved using the following commands.

BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, ‘SQLTERMINATOR’, true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, ‘PRETTY’, true);
END;
/
The results can be spooled into a file for editing.

Leave a Reply

Your email address will not be published.