DBMS_METADATA.GET_DLL

今天想用DBMS_METADA.GET_DLL取得建立表的SQL语句。

 

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

 

得到相应的结果，但是，发现有存储语句、表空间信息等。我想去掉这些信息，上网查询，得到如下解释

 

--输出信息采用缩排或换行格式化
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'PRETTY', TRUE);
--确保每个语句都带分号
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'SQLTERMINATOR', TRUE);
--关闭表索引、外键等关联（后面单独生成）
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'CONSTRAINTS', FALSE);
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'REF_CONSTRAINTS', FALSE);
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'CONSTRAINTS_AS_ALTER', FALSE);
--关闭存储、表空间属性
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'STORAGE', FALSE);
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'TABLESPACE', FALSE);
--关闭创建表的PCTFREE、NOCOMPRESS等属性
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', FALSE);

 

--创建用户对象
SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)
FROM USER_OBJECTS u
where U.OBJECT_TYPE IN ('TABLE','VIEW','SYNONYM','SEQUENCE')
;
SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)
FROM USER_OBJECTS u
where U.OBJECT_TYPE IN ('INDEX','TRIGGER')
;
SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)
FROM USER_OBJECTS u
where U.OBJECT_TYPE IN ('FUNCTION')
;
SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)
FROM USER_OBJECTS u
where U.OBJECT_TYPE IN ('PROCEDURE')
;
--获取表注释
SELECT DBMS_LOB.substr(DBMS_METADATA.get_dependent_ddl ('COMMENT', table_name))
FROM (SELECT distinct table_name
FROM user_col_comments
WHERE comments IS NOT NULL
)

 

其他注意

     1)dbms_metadata.get_ddl()包()内的参数都要大写

     2)set pagesize 0
       set long 90000              --为了完全显示查找的结果)
       set feedback off
       set echo off

     3)去掉schema,可以套用replace函数，将"schema".去掉

 

 

******************************************************

常见错误1:

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

SQL>

解决办法：运行$ORACLE_HOME/rdbms/admin/catmeta.sql

*****************************************************
常见错误2:

今天在一个数据库上执行DBMS_METADATA包的时候，出现了ORA-19206的错误。

 

具体错误信息如下：

SQL> SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW', 'CAT_AUTH_GMP', 'NDMAIN') 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

仔细检查了一下，发现所有和物化视图相关的表在执行DBMS_METADATA.GET_DDL时都会出现上面的错误，而其他的对象并没有受到影响。

由于这个数据库的数据是通过EXP/IMP迁移得到的，虽然在IMP过程中没有什么明显的错误信息，但是仍然怀疑是EXP/IMP造成的数据字典出现了错误。

查询了一下METALINK信息，发现了很多类似的错误，而Oracle给出的解决方法是重装XDB。

于是尝试重装XML方案：

首先以SYSDBA身份登陆，关闭并重启数据库，为卸载XML对象做准备：

SQL> CONN / AS SYSDBA
Connected.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Total System Global Area 5876197568 bytes
Fixed Size 739520 bytes
Variable Size 503316480 bytes
Database Buffers 5368709120 bytes
Redo Buffers 3432448 bytes
Database mounted.
Database opened.
SQL> SET ECHO ON
SQL> SPO XDB_REMOVAL.LOG
SQL> @?/rdbms/admin/catnoqm.sql
SQL> Rem
SQL> Rem $Header: catnoqm.sql 03-jan-2002.17:32:31 spannala Exp $
SQL> Rem
SQL> Rem catnoqm.sql
SQL> Rem
SQL> Rem Copyright (c) 2001, 2002, Oracle Corporation. All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem catnoqm.sql - CATalog script for removing (NO) XDB
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem this script drops the metadata created for SQL XML management
SQL> Rem This scirpt must be invoked as sys. It is to be invoked as
SQL> Rem
SQL> Rem @@catnoqm
SQL> Rem NOTES
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem spannala 01/03/02 - tables are not handled by xdb
SQL> Rem spannala 01/02/02 - registry
SQL> Rem spannala 12/20/01 - passing in the resource tablespace name
SQL> Rem tsingh 11/17/01 - remove connection string
SQL> Rem tsingh 06/30/01 - XDB: XML Database merge
SQL> Rem amanikut 02/13/01 - Creation
SQL> Rem
SQL> Rem
SQL>
SQL> execute dbms_registry.removing('XDB');
BEGIN dbms_registry.removing('XDB'); END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_REGISTRY", line 420
ORA-06512: at line 1

SQL> drop user xdb cascade;

User dropped.

SQL> SPO OFF

从上面脚本调用的结果可以确定，XML对象在执行导入时没有注册成功，可能这就是导致错误产生的原因。

保证JAVA池和共享池都大于150M，且XDB表空间可扩展，或者大于150M。则可以重启系统。

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Total System Global Area 5876197568 bytes
Fixed Size 739520 bytes
Variable Size 503316480 bytes
Database Buffers 5368709120 bytes
Redo Buffers 3432448 bytes
Database mounted.
Database opened.

下面重新安装XML方案：

SQL> SET ECHO ON
SQL> SPO XDB_INSTALL.LOG
SQL> @?/rdbms/admin/catqm.sql XDBPASSWD XDB TEMP

调用catqm.sql时，后面的三个参数依次时XDB用户的密码、默认表空间和临时镖客。

SQL> @?/rdbms/admin/catxdbj.sql

然后继续执行上面的SQL：

然后检查XDB用户是否存在错误对象，以及XDB是否注册到数据库中：

SQL> SELECT COUNT(*) FROM DBA_OBJECTS WHERE OWNER = 'XDB' AND STATUS = 'INVALID';

COUNT(*)
----------
0

SQL> SELECT COMP_NAME, STATUS, VERSION FROM DBA_REGISTRY WHERE COMP_NAME = 'Oracle XML Database';

COMP_NAME STATUS VERSION
---------------------------------------- ----------- --------------------