白天和黑夜只交替没交换无法想像对方的世界
分类：
2011-11-03 17:53:38
原文地址： 作者：
dbms_metadata包中的get_ddl函数
1.得到一个表或索引的ddl语句
select dbms_metadata.get_ddl('table','dept','scott') from dual;
select dbms_metadata.get_ddl('index','pk_dept','scott') from dual;
2.得到一个用户下的所有表，索引，存储过程的ddl
select dbms_metadata.get_ddl(u.object_type, u.object_name)
from user_objects u
where u.object_type in ('table','index','procedure');
存储过程：查看 存储过程 的定义语句
sql> set long 9999
sql> select dbms_metadata.get_ddl('procedure','analyzedb','newccs') from dual;
create or replace procedure "newccs"."analyzedb"
is
cursor get_ownertable
is
select table_name
from user_tables;
ownertable get_ownertable%rowtype;
begin
open get_ownertable;
loop
fetch get_ownertable
into ownertable;
exit when get_ownertable%notfound;
execute immediate 'analyze table '
|| ownertable.table_name
|| ' compute statistics for table for all indexes for all indexed columns';
end loop;
exception
when others
then
raise;
end;
3.得到所有表空间的ddl语句
select dbms_metadata.get_ddl('tablespace', ts.tablespace_name)
from dba_tablespaces ts;
4.得到所有创建用户的ddl
select dbms_metadata.get_ddl('user',u.username)
from dba_users u;
================================================================================
9i 中可以利用dbms_metadata.get_ddl包得到数据库的对象的ddl脚本。如下（sqlplus中执行）：
a. 获取单个的建表、视图和建索引的语法
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.获取一个schema下的所有建表、视图和建索引的语法，以scott为例：
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. 获取某个schema的建全部存储过程的语法
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. 获取某个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;