DBMS_METADATA.GET_DLL学习

set pagesize 0
set long 90000
set feedback off
set echo off

–table ‘s ddl
SELECT DBMS_METADATA.GET_DDL(‘TABLE’, U.TABLE_NAME) FROM USER_TABLES U;

–index ‘s ddl
SELECT DBMS_METADATA.GET_DDL(‘INDEX’, U.INDEX_NAME) FROM USER_INDEXES U;

–view ‘s ddl
SELECT DBMS_METADATA.GET_DDL(‘VIEW’, U.VIEW_NAME) FROM User_Views U;

–procedure ‘s ddl
SELECT DBMS_METADATA.GET_DDL(‘PROCEDURE’, U.object_name) FROM User_objects U WHERE object_type=’PROCEDURE’;

dbms_metadata.get_ddl(‘TABLE’,’TAB1′,’USER1′)
三个参数中，第一个指定导出DDL定义的对象类型（此例中为表类型），第二个是对象名（此例中即表名），第三个是对象所在的用户名。

下面我们看一个例子：

　
SQL> create view hjm_test_view as

2 select * from v$datafile;


视图已建立。


SQL> set pagesize 0

SQL> set long 90000

SQL> set echo off

SQL> set feedback off


SQL> select dbms_metadata.get_ddl('VIEW','HJM_TEST_VIEW','HEJIANMIN')FROM USER_VIEWS;


CREATE OR REPLACE FORCE VIEW "HEJIANMIN"."HJM_TEST_VIEW" ("FILE#", "CREATION_C

#", "STATUS", "ENABLED", "CHECKPOINT_CHANGE#", "CHECKPOINT_TIME", "UNRECOVERABLE

_CHANGE#", "UNRECOVERABLE_TIME", "LAST_CHANGE#", "LAST_TIME", "OFFLINE_CHANGE#",

"ONLINE_CHANGE#", "ONLINE_TIME", "BYTES", "BLOCKS", "CREATE_BYTES", "BLOCK_SIZE

", "NAME", "PLUGGED_IN", "BLOCK1_OFFSET", "AUX_NAME") AS

select "FILE#","CREATION_CHANGE#","CREATION_TIME","TS#","RFILE#","STATUS","ENA

GE#","CHECKPOINT_TIME","UNRECOVERABLE_CHANGE#","UNRECOVERABLE_TIME","LAST_CHANGE

#","LAST_TIME","OFFLINE_CHANGE#","ONLINE_CHANGE#","ONLINE_TIME","BYTES","BLOCKS"

,"CREATE_BYTES","BLOCK_SIZE","NAME","PLUGGED_IN","BLOCK1_OFFSET","AUX_NAME" from

v$datafile



SQL> DROP VIEW HJM_TEST_VIEW;

SQL>

详细的具体内容介绍，可见http://www.oracle.com.cn/onlinedoc/appdev.920/a96612/d_metad2.htm