Wednesday, April 6, 2011

How to get schema's objects DDL

In Oracle, if you need to recreate, copy or compare an entire schema (or even a whole database) without the data, you can use the DBMS_METADATA.GET_DDL package in order to get object's DDL like this:

set linesize 150
set long 90000
set pagesize 0
set wrap on
column mdata format a150

SELECT DBMS_METADATA.GET_DDL(object_type, object_name, owner) as mdata FROM DBA_OBJECTS
WHERE OWNER = 'MYSCHEMA' and OBJECT_TYPE not in ('LOB') ORDER BY object_type, object_name;

More information about getting object's DDL:

Get table and index DDL the easy way
DDL Generation--Oracle's Answer to Save You Time and Money

No comments:

Post a Comment