The use of DBMS_METADATA.GET_DDL

The GET_DDL function in the Dbms_metadata package

--get_ddl:return the metadata for a single object as DDL.

--This interface was meant for casual browsing (e.g., from SQLPlus)

--vs. the programmatic open/fetch/close interfaces above.

--PARAMETERS:

--Object_type-the type of object to be retrieved.

--Name-name of the object.

--Schema-schema containing the object. Defaults to

-The caller ' s schema.

--version-the version of the objects ' metadata.

--Model-the object model for the metadata.

--Transform. -Xsl-t transform. To is applied.

--Returns:metadata for the object transformed to DDL as a CLOB.

FUNCTION 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;

1. Get DDL statements for a table or index

SELECT Dbms_metadata. GET_DDL (' TABLE ', ' DEPT ', ' SCOTT ') from DUAL;

Select Dbms_metadata.get_ddl (' INDEX ', ' pk_dept ', ' SCOTT ') from dual;

2. Get the DDL for all tables, indexes, stored procedures under a user

SELECT Dbms_metadata. GET_DDL (U.object_type, U.object_name)
From User_objects u
where U.object_type in (' TABLE ', ' INDEX ', ' PROCEDURE ');

3. Get DDL statements for all tablespaces

SELECT Dbms_metadata. GET_DDL (' tablespace ', ts.tablespace_name)
From Dba_tablespaces TS;

4. Get DDL for all created users

SELECT Dbms_metadata. GET_DDL (' USER ', u.username)
From Dba_users U;

================================================================================

Dbms_metadata can be used in 9i. The GET_DDL package gets the DDL script for the database object. as follows (performed in Sqlplus):

A. Getting a single build table, view, and indexed syntax

Set pagesize 0
Set long 90000
Set feedback off
set echo off
spool dept.sql
Select dbms_metadata.get_ DDL (' TABLE ', ' tab_name ', ' Scott ') from dual; 
Select Dbms_metadata.get_ddl (' VIEW ', ' view_name ', ' Scott ') from dual; 
Select Dbms_metadata.get_ddl (' INDEX ', ' idx_name ', ' SCOTT ') from dual;
Spool off;
   
B. Get all the built-in tables, views, and indexed syntax under a schema, take Scott for example:
  
Set pagesize 0
Set Long 90000
Set Feedback off
set echo off
spool schema.sql
Connect Scott/tiger;
SELECT Dbms_metadata. GET_DDL (' TABLE ', u.table_name) from User_tables u;
 select Dbms_metadata. GET_DDL (' VIEW ', u.view_name) from User_views u;
SELECT Dbms_metadata. GET_DDL (' INDEX ', u.index_name) from User_indexes u;
Spool off;

C. Getting the syntax for a schema to build all stored procedures

Set pagesize 0
Set Long 90000
Set Feedback off
Set echo off
Spool Procedures.sql
Select Dbms_metadata. GET_DDL (' PROCEDURE ', u.object_name) from user_objects u where object_type = ' PROCEDURE ';
Spool off;

D. Getting the syntax for all functions of a schema

Set pagesize 0
Set Long 90000
Set Feedback off
Set echo off
Spool Function.sql
Select Dbms_metadata. Get_ddl (' function ', u.object_name) from user_objects u where object_type = ' function ';
Spool off;

The use of DBMS_METADATA.GET_DDL