oracle 如何查看创建表等数据库对象时的DDL语句

深藏阁楼爱情的钟 2021-06-24 13:59 1302阅读 0赞

当我们想要查看某个表或者是表空间的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

解决办法：运行$ORACLE\_HOME/rdbms/admin/catmeta.sql