[转]Oracle的DBMS_METADATA包-白红宇

[转]Oracle的DBMS_METADATA包

阅读量：6932 次

发布时间：2019-06-27

本文共 6580 字，大约阅读时间需要 21 分钟。

原文地址：

作者：

(官方文档)

(百度上很详细的示例)

(百度空间上的系列博文一)

(百度空间上的系列博文二)

(百度空间上的系列博文三)

######################################################################################################DBMS_METADATA.GET_DDL包可以得到数据库的对象的ddl脚本。如下（SQLPLUS中执行）：

1.得到一个表的ddl语句：

SET SERVEROUTPUT ON

SET LINESIZE 1000

SET FEEDBACK OFF

set long 999999

------显示不完整

SET PAGESIZE 1000

----分页

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

---去除storage等多余参数

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

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

2.得到一个用户下的所有表，索引，存储过程的ddl

SET SERVEROUTPUT ON

SET LINESIZE 1000

SET FEEDBACK OFF

set long 999999

------显示不完整

SET PAGESIZE 1000

--分页

---去除storage等多余参数

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)

FROM USER_OBJECTS u

where U.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE');

3.得到所有表空间的ddl语句

SET SERVEROUTPUT ON

SET LINESIZE 1000

SET FEEDBACK OFF

set long 999999

------显示不完整

SET PAGESIZE 1000

----分页

---去除storage等多余参数

SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name) FROM DBA_TABLESPACES TS;

4.得到所有创建用户的ddl

SET SERVEROUTPUT ON

SET LINESIZE 1000

SET FEEDBACK OFF

set long 999999

------显示不完整

SET PAGESIZE 1000

----分页

---去除storage等多余参数

SELECT DBMS_METADATA.GET_DDL('USER',U.username) FROM DBA_USERS U;

另外，若执行不了该包，则需要正确安装好相应的包。

Trackback:

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

常见错误1:

ORA-39212: 安装错误: 未正确加载 XSL 样式表

ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: 在 "SYS.DBMS_METADATA_INT", line 7398

ORA-06512: 在 "SYS.DBMS_METADATA_INT", line 7447

ORA-06512: 在 "SYS.DBMS_METADATA_INT", line 9453

ORA-06512: 在 "SYS.DBMS_METADATA", line 1919

ORA-06512: 在 "SYS.DBMS_METADATA", line 2792

ORA-06512: 在 "SYS.DBMS_METADATA", line 4333

ORA-06512: 在 line 1

ORA-06502: PL/SQL: 数字或值错误

ORA-31605: the following was returned from LpxXSLResetAllVars in routine kuxslResetParams:

LPX-1: NULL pointer

ORA-06512: 在 "SYS.UTL_XML", line 246

ORA-06512: 在 "SYS.DBMS_METADATA_INT", line 7511

ORA-06512: 在 "SYS.DBMS_METADATA_INT", line 9453

ORA-06512: 在 "SYS.DBMS_METADATA", line 1919

ORA-06512: 在 "SYS.DBMS_METADATA", line 2792

ORA-06512: 在 "SYS.DBMS_METADATA", line 4333

ORA-06512: 在 line 1

【解决办法】：

如果遇到类似的错误，需要站点下载编号为2736436的补丁。

常见错误2:

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

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

常见错误3:

今天在一个数据库上执行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 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>

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(*)

----------

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

COMP_NAME STATUS VERSION

---------------------------------------- ----------- --------------------

Oracle XML Database VALID 9.2.0.4.0

下面重启数据库和监听，XDB重装完毕。

重建XDB之后，对DBMS_METADATA的访问恢复正常：

SQL> SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW', 'CAT_AUTH_GMP', 'NDMAIN') FROM DUAL;

DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','CAT_AUTH_GMP','NDMAIN')

------------------------------------------------------------------

CREATE MATERIALIZED VIEW "NDMAIN"."CAT_AUTH_GMP"

ORGANIZATION HEAP PCTFREE

转载于:https://www.cnblogs.com/uniqueness/archive/2013/04/16/3023846.html

你可能感兴趣的文章

Ruby 2.2.0发布，支持增量式垃圾收集和符号的垃圾收集

查看>>

使用Rekit开发可扩展的前端应用

查看>>

android源码分析-深入MessageQueue

查看>>

苹果裁撤自动驾驶项目员工200余人

查看>>

红帽OpenShift总经理谈容器技术需要关注的方向

查看>>

组合使用Laravel和vfsStream测试文件上传

查看>>

微软发布Azure Application Insights for Node.js 1.0版本

Azure Service Fabric正式发布

查看>>

唐文：挖掘产品生命周期潜藏的商业价值——应用性能管理

查看>>

迅雷“星域”打通最后一公里重新定义CDN

查看>>

专访Matt Klein关于在Lyft构建Envoy的问答

Concourse：可扩展的开源CI管道工具

MongoDB主动撤回SSPL的开源许可申请

查看>>