DBMS_METADATE.GET_DDL獲取物件DDL指令碼

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;
另外，若執行不了該包，則需要正確安裝好相應的包。

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

常見錯誤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 WNER = '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
---------------------------------------- ----------- --------------------
Oracle XML Database VALID 9.2.0.4.0

下面重啟資料庫和監聽，XDB重灌完畢。

重建XDB之後，對DBMS_METADA他的訪問恢復正常：

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8750110/viewspace-200488/，如需轉載，請註明出處，否則將追究法律責任。