Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.