ORACLE 使用DBMS_METADATA.GET_DDL获取DDL语句

如何抽取数据表的调用关系？

我们在进行数据导入导出等操作时，如果不是按照设计文档比对，经常会出现数据完整性相

关的报错。如a、b表间存在外键关系，a为主表。直接向b表中插入a中没有的数据会报错。
找到a、b表间是否存在着约束关系，就能够有参照地进行处理了。

那么如何得到约束的创建语句，从而找到表之间存在的依赖关系呢？

由于以前用dbms_metadata函数获取过表的创建语句，首先想到是否可以用此函数获取约束的

创建语句呢？

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

dbms_metadata.get_ddl

以前只知道dbms_metadata的get_ddl方法可以用于获取建表语句，因此今天想到是否可以用

其获得其他对象的创建语句呢？
参考资料：
http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10802/d_metada.htm#

998564
http://blog.csdn.net/wh62592855/article/details/4697840

第一份参考资料中对于该函数的讲解比较全面，但对于哪些对象可以用此函数获取并未明确

，第二份参考资料则更加实际，例举了一些常见待获取ddl语句的对象。

我在oracle 9i环境中运行了如下语句：
select dbms_metadata.get_ddl('TABLE','EMP','SCOTT')  from dual;--获取表的创建语句
select dbms_metadata.get_ddl('INDEX','EMP_IDX','SCOTT')  from dual;--获取索引的创

建语句
select dbms_metadata.get_ddl('FUNCTION','GET_USER','SCOTT')  from dual;--获取函数

的创建语句
select dbms_metadata.get_ddl('PACKAGE','PCK_DBADMIN_GETDDL','SCOTT')  from dual;

--获取包、包体的创建语句
select dbms_metadata.get_ddl('VIEW','EMP_DEPT_02','SCOTT')  from dual;--获取视图

的创建语句
select dbms_metadata.get_ddl('SEQUENCE','TEMP_SEQ','SCOTT')  from dual;--获取序列

的创建语句
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','EMP_DEPT_01','SCOTT')  from

dual;--获取物化视图的创建语句
select dbms_metadata.get_ddl('TRIGGER','CHECK_MGR','SCOTT')  from dual;--获取物化

视图的创建语句
select constraint_name,dbms_metadata.get_ddl('CONSTRAINT',constraint_name,owner)

from user_constraints where constraint_type not in('R','O','C');
select constraint_name,dbms_metadata.get_ddl('CONSTRAINT',constraint_name,owner)

from user_constraints where constraint_type ='R';--获取参照性约束的创建语句，报错
select constraint_name,dbms_metadata.get_ddl('CONSTRAINT',constraint_name,owner)

from user_constraints where constraint_type ='F';--实验环境中没有结果
select constraint_name,dbms_metadata.get_ddl('CONSTRAINT',constraint_name,owner)

from user_constraints where constraint_type ='O';--实验环境中没有结果
select constraint_name,dbms_metadata.get_ddl('CONSTRAINT',constraint_name,owner)

from user_constraints where constraint_type ='U';--获取唯一约束的创建语句，有结果
select dbms_metadata.get_ddl('CONSTRAINT','FK_DEPTNO','SCOTT')  from dual;--获取

物化视图的创建语句
select dbms_metadata.get_ddl('USER','SCOTT')  from dual;--获取用户的创建语句
select dbms_metadata.get_ddl('TABLESPACE','SYSTEM')  from dual;--获取表空间的创建

语句
select dbms_metadata.get_ddl('SYNONYM','DUAL')  from dual;--获取同义词的创建语句
select dbms_metadata.get_ddl('','DUAL')  from dual;--获取同义词的创建语句

 

此函数可以进行处理的对象包括：
表
物化视图
索引
函数
包、包体
触发器
序列
同义词
等等

不能处理的对象包括dblinks、外键约束等

需要注意的是：
（1）产生的语句中，对象名称是用双引号包含的
（2）语句中的空格数是和原始数据不一样的（具体原因无法理解，但直接调用可能会有问题

）
（3）语句中的object_type项必须严格大写，且不能有拼写错误
（4）约束的获取比较特殊，后面详述
（5）通过数据字典可以更加方便地获得ddl语句
（如：获得所有用户的ddl，语句为
select username,dbms_metadata.get_ddl('USER',username) from dba_users;）

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

用这种方法并不能得到我们想要的外键约束详细内容。oracle中外键的详细内容似乎不是从

dba/user/all_constraints中得到的。然而，我们应该看到：某张表中的外键列应该是另外

某张表上的主键。

通过外键名找到约束的名称和其参照的约束名称（也就是主表上的主键名）。如果我们通过

层次查询，就可以找到其对应关系，从而分析出哪些表需要先创建。

外键的定义格式为alter table table_name add constraint constraint_name foreign

key(column_name) references tab2_name(tab2_column_name);

 

通过以下语句，可以获得表之间的外键联系
select level,uc.table_name||'->'||u.table_name "primary->standbys" from

user_constraints u,user_constraints uc
where u.r_constraint_name=uc.constraint_name
start with uc.table_name not in
(select table_name from user_constraints where constraint_type='R')
connect by prior u.table_name=uc.table_name
order by level

这样，在导入数据时就需要按照level级别，首先处理不存在外键调用的，每次保证导入的数

据不违反约束条件。

 

Oracle 在9i以后，可以利用DBMS_METADATA.GET_DDL包得到数据库的对象的ddl脚本。如下（SQLPLUS中执行）：

 

1. 获取单个的建表、视图和建索引的语法
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;

 

2. 获取一个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;

 

3. 获取某个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;

4. 获取某个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;

 

当我们想要查看某个表或者是表空间的DDL的时候，可以利用dbms_metadata.get_ddl这个包来查看。

dbms_metadata包中的get_ddl函数详细参数
GET_DDL函数返回创建对象的原数据的DDL语句，详细参数如下
-- object_type ---需要返回原数据的DDL语句的对象类型
-- name --- 对象名称
-- schema ---对象所在的Schema，默认为当前用户所在所Schema
-- version ---对象原数据的版本
-- model ---原数据的类型默认为ORACLE
-- transform. - XSL-T transform. to be applied.
-- RETURNS: 对象的原数据默认以CLOB类型返回

dbms_metadata包中的get_ddl函数定义
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;

注意如果使用sqlplus需要进行下列格式化，特别需要对long进行设置，否则无法显示完整的SQL

set linesize 180   
set pages 999
set long 90000    

查看创建用户表的SQL

查看当前用户表的SQL
select dbms_metadata.get_ddl('TABLE','EMPLOYEES') from dual;
查看其他用表或索引的SQL

SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;

查看创建用户索引的SQL
查看所需表的索引
SQL> select INDEX_NAME, INDEX_TYPE, TABLE_NAME from user_indexes WHERE table_name='EMP';
查看当前用户索引的SQL
select dbms_metadata.get_ddl('INDEX','PK_DEPT') from dual;

查看其他用户索引的SQL
 select dbms_metadata.get_ddl('INDEX','PK_DEPT','SCOTT‘) from dual;
查看创建主键的SQL
查看所需表的约束
SQL> select owner, table_name, constraint_name, constraint_type from user_constraints where table_name='EMP';
查看创建主键的SQL
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','EMP_PK') FROM DUAL;
查看创建外键的SQL
SQL> SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT','EMP_FK_DEPT') FROM DUAL;
查看创建VIEW的语句
查看当前用户视图的SQL
SQL> SELECT dbms_metadata.get_ddl('VIEW', 'MY_TABLES')

查看其他用户视图的SQL
SQL> SELECT dbms_metadata.get_ddl('VIEW', 'MY_TABLES','SCOTT‘) FROM DUAL;
查看创建视图的SQL也可以
SQL> select text from user_views where view_name=upper('&view_name');
DBMS_METADATA.GET_DDL的一些使用技巧
1、得到一个用户下的所有表，索引，存储过程，函数的ddl
SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)
FROM USER_OBJECTS u
where U.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE','FUNCTION‘);
2、得到所有表空间的ddl语句
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)
FROM DBA_TABLESPACES TS;
3、得到所有创建用户的ddl
SELECT DBMS_METADATA.GET_DDL('USER',U.username)
FROM DBA_USERS U;
4、去除storage等多余参数
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

常见错误

SQL> select dbms_metadata.get_ddl('TABLE','PC','SCOTT') from dual;
ERROR:
ORA-19206: Invalid value for query or REF CURSOR parameter
ORA-06512: at "SYS.DBMS_XMLGEN", line 83
ORA-06512: at "SYS.DBMS_METADATA", line 345
ORA-06512: at "SYS.DBMS_METADATA", line 410
ORA-06512: at "SYS.DBMS_METADATA", line 449
ORA-06512: at "SYS.DBMS_METADATA", line 615
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1

no rows selected