The usage of dbms_metadata.get_ddl
GET_DDL: Return the metadata for a single object as DDL.
-- This interface is 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 be 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 a ddl statement 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
two。 Get the ddl of all tables, indexes, and 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 the ddl of all the created users
SELECT DBMS_METADATA.GET_DDL ('USER',U.username) FROM DBA_USERS U
=
In 9i, you can use the DBMS_METADATA.GET_DDL package to get the ddl script for the objects of the database. As follows (executed in SQLPLUS):
a. Get a single syntax for creating tables, views, and indexes
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 syntax for creating tables, views, and indexes under a SCHEMA, taking scott as an 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. Get the syntax of a SCHEMA's build all stored procedure
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. Get the syntax for building 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