DBMS_METADATA包獲得物件DDL語句

DBMS_METADATA包獲得物件DDL語句

---

---

如何利用系統包DBMS_METADATA包中的GET_DDL函式來獲取物件的定義語句。下面是該函式的入參和出參：

SQL> DESC DBMS_METADATA.GET_DDL

PARAMETER   TYPE     MODE DEFAULT?

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

(RESULT)    CLOB                   

OBJECT_TYPE VARCHAR2 IN            

NAME        VARCHAR2 IN            

SCHEMA      VARCHAR2 IN   Y        

VERSION     VARCHAR2 IN   Y        

MODEL       VARCHAR2 IN   Y        

TRANSFORM   VARCHAR2 IN   Y        

其詳細引數如下：

l OBJECT_TYPE  需要返回原資料的DDL語句的物件型別

l NAME  物件名稱

l SCHEMA  物件所在的SCHEMA，預設為當前使用者所在所SCHEMA

l VERSION  物件原資料的版本

l MODEL  原資料的型別預設為ORACLE

l TRANSFORM  預設值為DDL

l RETURNS  物件的原資料預設以CLOB型別返回

一般情況下，只需要給出OBJECT_TYPE、NAME和SCHEMA3個引數即可。

n 檢視建立表SQL語句：

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

SELECT DBMS_METADATA.GET_DDL('TABLE',U.TABLE_NAME) FROM USER_TABLES U;

n 檢視建立索引的SQL語句：

SELECT DBMS_METADATA.GET_DDL('INDEX','PK_DEPT','SCOTT') FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME) FROM USER_INDEXES U;

n 檢視建立主鍵的SQL語句：

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','EMP_PK') FROM DUAL;

n 檢視建立外來鍵的SQL語句：

SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT','EMP_FK_DEPT') FROM DUAL;

n 檢視建立檢視（VIEW）的SQL語句：

SELECT DBMS_METADATA.GET_DDL('VIEW', 'MY_TABLES','SCOTT') FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('VIEW', U.OBJECT_NAME)

  FROM USER_OBJECTS U

 WHERE OBJECT_TYPE = 'VIEW';

SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME=UPPER('&VIEW_NAME');

n 檢視建立儲存過程（PROCEDURE）的SQL語句：

SELECT DBMS_METADATA.GET_DDL('PROCEDURE', U.OBJECT_NAME)

  FROM USER_OBJECTS U

 WHERE OBJECT_TYPE = 'PROCEDURE';

n 檢視建立觸發器（TRIGGER）的SQL語句：

SELECT DBMS_METADATA.GET_DDL('TRIGGER', U.OBJECT_NAME)

  FROM USER_OBJECTS U

 WHERE OBJECT_TYPE = 'TRIGGER';

n 檢視建立函式（FUNCTION）的SQL語句：

SELECT DBMS_METADATA.GET_DDL('FUNCTION', U.OBJECT_NAME)

  FROM USER_OBJECTS U

 WHERE OBJECT_TYPE = 'FUNCTION';

n 檢視建立包（PACKAGE）的SQL語句：

SELECT DBMS_METADATA.GET_DDL('PACKAGE', U.OBJECT_NAME)

  FROM USER_OBJECTS U

 WHERE OBJECT_TYPE = 'PACKAGE';

n 檢視建立序列（SEQUENCE）的SQL語句：

SELECT DBMS_METADATA.GET_DDL('SEQUENCE', U.OBJECT_NAME)

  FROM USER_OBJECTS U

 WHERE OBJECT_TYPE = 'SEQUENCE';

n 檢視建立同義詞（SYNONYM）的SQL語句：

SELECT DBMS_METADATA.GET_DDL('SYNONYM', U.OBJECT_NAME)

  FROM USER_OBJECTS U

 WHERE OBJECT_TYPE = 'SYNONYM';

n 檢視建立表空間（TABLESPACE）的SQL語句：

SELECT DBMS_METADATA.GET_DDL('TABLESPACE', U.TABLESPACE_NAME)

  FROM USER_TABLESPACES U;

n 檢視建立角色（ROLE）的SQL語句：

SELECT DBMS_METADATA.GET_DDL('ROLE', U.ROLE) FROM DBA_ROLES U;

n 檢視建立使用者（USER）的SQL語句：

SELECT DBMS_METADATA.GET_DDL('USER','SYS') FROM DUAL;

n 得到某個SCHEDULER JOB的建立語句：

SELECT DBMS_METADATA.GET_DDL('PROCOBJ', D.JOB_NAME, D.OWNER)

  FROM DBA_SCHEDULER_JOBS D

 WHERE D.JOB_TYPE = 'STORED_PROCEDURE'

   AND D.STATE = 'SCHEDULED'

   AND D.SCHEDULE_NAME IS NULL;

n 得到一個使用者下的所有表、索引、儲存過程、函式的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');

如果想去掉表的儲存引數（例如，INITIAL、NEXT、FREELISTS等引數），那麼可以使用DBMS_METADATA包中的函式SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE)來完成，程式碼如下所示：

SYS@lhrdb> SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT')

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

  CREATE TABLE "SCOTT"."DEPT"

   (    "DEPTNO" NUMBER(2,0),

        "DNAME" VARCHAR2(14),

        "LOC" VARCHAR2(13),

         CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS"  ENABLE

   ) SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS"

SYS@lhrdb> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);

PL/SQL procedure successfully completed.

SYS@lhrdb> SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT')

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

  CREATE TABLE "SCOTT"."DEPT"

   (    "DEPTNO" NUMBER(2,0),

        "DNAME" VARCHAR2(14),

        "LOC" VARCHAR2(13),

         CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  TABLESPACE "USERS"  ENABLE

   ) SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  TABLESPACE "USERS"

使用DBMS_METADATA.GET_DDL需要注意以下問題：

（1）DBMS_METADATA.GET_DDL()包內的引數都要大寫，否則會報ORA-31600: invalid input value table for parameter OBJECT_TYPE in function GET_DDL的錯誤。

（2）是否查的當前使用者的DDL語句，若不是則需要加上物件的屬主資訊即SCHEMA引數。

（3）若在SQL*Plus中顯示不全，則需要set long 9999。

現在來回答本題目中的問題，程式碼如下所示：

SET PAGESIZE 0

SET TRIMSPOOL ON

SET LINESIZE 10000

SET LONG 90000

SET FEEDBACK OFF

SET FEED OFF;

SET ECHO OFF

SPOOL SCHEMA_SCOTT.SQL

SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME,U.OWNER)

FROM   DBA_OBJECTS U

WHERE  U.OBJECT_TYPE IN ('TABLE', 'INDEX', 'PROCEDURE', 'FUNCTION'，'PACKAGE','TRIGGER')

AND U.OWNER='SCOTT';

SPOOL OFF;

 

可以使用如下的SQL指令碼生成某個使用者下的所有物件的DDL語句：

sqlplus<<EOF

set long 100000

set head off

set echo off

set pagesize 0

set verify off

set feedback off

spool schema.out

 

select dbms_metadata.get_ddl(object_type, object_name, owner)

from

(

    --Convert DBA_OBJECTS.OBJECT_TYPE to DBMS_METADATA object type:

    select

        owner,

        --Java object names may need to be converted with DBMS_JAVA.LONGNAME.

        --That code is not included since many database don't have Java installed.

        object_name,

        decode(object_type,

            'DATABASE LINK',      'DB_LINK',

            'JOB',                'PROCOBJ',

            'RULE SET',           'PROCOBJ',

            'RULE',               'PROCOBJ',

            'EVALUATION CONTEXT', 'PROCOBJ',

            'PACKAGE',            'PACKAGE_SPEC',

            'PACKAGE BODY',       'PACKAGE_BODY',

            'TYPE',               'TYPE_SPEC',

            'TYPE BODY',          'TYPE_BODY',

            'MATERIALIZED VIEW',  'MATERIALIZED_VIEW',

            'QUEUE',              'AQ_QUEUE',

            'JAVA CLASS',         'JAVA_CLASS',

            'JAVA TYPE',          'JAVA_TYPE',

            'JAVA SOURCE',        'JAVA_SOURCE',

            'JAVA RESOURCE',      'JAVA_RESOURCE',

            object_type

        ) object_type

    from dba_objects

    where owner in ('LHR')

        --These objects are included with other object types.

        and object_type not in ('INDEX PARTITION','INDEX SUBPARTITION',

           'LOB','LOB PARTITION','LOB SUBPARTITION','TABLE PARTITION','TABLE SUBPARTITION','PROGRAM')

        --Ignore system-generated types that support collection processing.

        and not (object_type = 'TYPE' and object_name like 'SYS_PLSQL_%')

        --Exclude nested tables, their DDL is part of their parent table.

        and (owner, object_name) not in (select owner, table_name from dba_nested_tables)

        --Exlclude overflow segments, their DDL is part of their parent table.

        and (owner, object_name) not in (select owner, table_name from dba_tables where iot_type = 'IOT_OVERFLOW')

)

order by owner, object_type, object_name;

 

 

spool off

quit

EOF

 

cat schema.out|sed 's/OWNER1/MYOWNER/g'>schema.out.change.sql

通過系統包DBMS_METADATA.GET_DDL也可以獲取使用者的許可權資訊，如下所示：

SELECT DBMS_METADATA.GET_DDL('USER', 'LHR') DDL_SQL FROM DUAL

UNION ALL

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'LHR') FROM DUAL

UNION ALL

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'LHR') FROM DUAL

UNION ALL

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'LHR') FROM DUAL;

將結果拷貝出來簡單的用文字編輯工具編輯後即可執行。

---

---

Oracle9i新特性-使用DBMS_METADATA包獲得物件DDL語句

從Oracle9i開始Oracle提供了一個新的系統包DBMS_METADATA，可以用於提取物件建立的DDL語句。

這個Package功能極其強大，我們來看看它的使用方法.

1.得到一個表的ddl語句：

dbms_metadata是oracle提供的從資料字典中獲取資料庫物件後設資料(也就是物件的原始定義)的包。在這以前DBA還可以通過建立自定義SQL來獲取物件定義，這個包的出現可以大大減少我們的工作。這個包提供了很多有用的工具，10g對這個包進行了更新加強，增加了許多新功能。

常用的過程有dbms_metadata.get_ddl或dbms_metadata.get_xml(物件定義語句以XML的格式返回), 還可以利用dbms_granted_ddl或dbms_granted_xml獲取物件的許可權資訊

獲取tablespace定義

SQL> set linesize 200
SQL> set pagesize 990
SQL> set long 1000
SQL> select dbms_metadata.get_ddl('TABLESPACE',tablespace_name) from dba_tablespaces;

DBMS_METADATA.GET_DDL('TABLESP
--------------------------------------------------------------------------------

CREATE TABLESPACE "SYSTEM" DATAFILE
'/u02/oradata/idctest/test/datafile/o1_mf_system_3142lbps_.dbf' SIZE 262144000
AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL

CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
'/u02/oradata/idctest/test/datafile/o1_mf_undotbs1_3142lbq6_.dbf' SIZE 209715200
AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE

..........

獲取使用者定義

SQL> select dbms_metadata.get_ddl('USER',username) from dba_users;

DBMS_METADATA.GET_DDL('USER',U
--------------------------------------------------------------------------------

CREATE USER "HR" IDENTIFIED BY VALUES '01C6FA40E1437B28'
DEFAULT TABLESPACE "HRD"
TEMPORARY TABLESPACE "TEMP"

CREATE USER "OSC" IDENTIFIED BY VALUES '1EAF99B140949974'
DEFAULT TABLESPACE "OSCD"
TEMPORARY TABLESPACE "TEMP"

CREATE USER "SYSMAN" IDENTIFIED BY VALUES '447B729161192C24'
DEFAULT TABLESPACE "SYSAUX"
TEMPORARY TABLESPACE "TEMP"
................

................

獲取表的定義

SQL> select dbms_metadata.get_ddl('TABLE','TEST') from dual;

DBMS_METADATA.GET_DDL('TABLE',
--------------------------------------------------------------------------------

CREATE TABLE "IQUEWEB"."TEST"
( "A" CHAR(4),
"B" VARCHAR2(10),
"ADDRESS" VARCHAR2(200)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

獲取許可權定義

SQL> select dbms_metadata.get_granted_ddl('SYSTEM_GRANT') from dual;

DBMS_METADATA.GET_GRANTED_DDL(
--------------------------------------------------------------------------------

GRANT UNLIMITED TABLESPACE TO "IQUEWEB"

SQL> select dbms_metadata.get_granted_ddl('OBJECT_GRANT') from dual;

DBMS_METADATA.GET_GRANTED_DDL(
--------------------------------------------------------------------------------

GRANT DELETE ON "INTERINFO"."IQUE_USER_BASE" TO "IQUEWEB"

GRANT INSERT ON "INTERINFO"."IQUE_USER_BASE" TO "IQUEWEB"

GRANT SELECT ON "INTERINFO"."IQUE_USER_BASE" TO "IQUEWEB"

GRANT UPDATE ON "INTERINFO"."IQUE_USER_BASE" TO "IQUEWEB"

GRANT SELECT ON "INTERINFO"."IQUE_USER" TO "IQUEWEB"

GRANT REFERENCES ON "INTERINFO"."IQUE_FIN_PROJECT" TO "IQUEWEB"

GRANT SELECT ON "INTERINFO"."IQUE_FIN_PROJECT" TO "IQUEWEB"

---

---

利用DBMS_METADATA包獲取許可權資訊 

Oracle的9i增加了DBMS_METADATA包，從此在需要得到物件的建立指令碼時，不再需要通過查詢多張系統檢視去自己拼湊結果了。只需要呼叫這個包中的GET_DDL過程就可以輕鬆的獲取物件的建立腳步。

不過很多人對DBMS_METADATA包的瞭解僅限與此。當需要獲取使用者的許可權時，往往還是採用通過到資料字典中讀取的方式，其實DBMS_METADATA包本身就支援獲取許可權資訊。

DBMS_METADA他的GET_GRANTED_DDL過程可以用於獲取使用者的授權資訊。Oracle支援的授權相關資訊型別包括：OBJECT_GRANT、SYSTEM_GRANT、ROLE_GRANT、DEFAULT_ROLE、TABLESPACE_QUOTA和PROXY。

由於使用十分簡單，這裡給一個簡單的例子，就不詳細描述了：

SQL> CREATE USER TEST IDENTIFIED BY TEST DEFAULT TABLESPACE YANGTK;

使用者已建立

SQL> GRANT CONNECT TO TEST;

授權成功。

SQL> GRANT SELECT ANY TABLE TO TEST;

授權成功。

SQL> GRANT INSERT, UPDATE ON T TO TEST;

授權成功。

SQL> ALTER USER TEST QUOTA 10M ON YANGTK;

使用者已更改。

SQL> CREATE ROLE R1;

角色已建立

SQL> GRANT R1 TO TEST;

授權成功。

SQL> ALTER USER TEST DEFAULT ROLE R1;

使用者已更改。

SQL> ALTER USER TEST GRANT CONNECT THROUGH YANGTK;

使用者已更改。

SQL> SET LONG 10000
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'TEST') FROM DUAL;

DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','TEST')
----------------------------------------------------------------------

GRANT UPDATE ON "YANGTK"."T" TO "TEST"

GRANT INSERT ON "YANGTK"."T" TO "TEST"

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'TEST') FROM DUAL;

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','TEST')
----------------------------------------------------------------------

GRANT SELECT ANY TABLE TO "TEST"

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'TEST') FROM DUAL;

DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','TEST')
----------------------------------------------------------------------

GRANT "CONNECT" TO "TEST"

GRANT "R1" TO "TEST"

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('DEFAULT_ROLE', 'TEST') FROM DUAL;

DBMS_METADATA.GET_GRANTED_DDL('DEFAULT_ROLE','TEST')
----------------------------------------------------------------------

ALTER USER "TEST" DEFAULT ROLE "R1"

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', 'TEST') FROM DUAL;

DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA','TEST')
----------------------------------------------------------------------

ALTER USER "TEST" QUOTA 640 ON "YANGTK"

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('PROXY', 'TEST') FROM DUAL;

DBMS_METADATA.GET_GRANTED_DDL('PROXY','TEST')
----------------------------------------------------------------------

ALTER USER "TEST" GRANT CONNECT THROUGH "YANGTK"

需要注意的是，如果指定查詢的授權不存在，並不是簡單的返回未選定行，而是還會顯示錯誤資訊：

SQL> REVOKE SELECT ANY TABLE FROM TEST;

撤銷成功。

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'TEST') FROM DUAL;
ERROR:
ORA-31608: 找不到型別為 SYSTEM_GRANT 的指定物件
ORA-06512: 在"SYS.DBMS_SYS_ERROR", line 86
ORA-06512: 在"SYS.DBMS_METADATA", line 631
ORA-06512: 在"SYS.DBMS_METADATA", line 1339
ORA-06512: 在line 1

未選定行

最後還要說明一點，DBMS_METADA他的GET_GRANTED_DDL不會顯示SYSDBA和SYSOPER許可權。

---

---

About Me

............................................................................................................................................. ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文部落格園地址：http://www.cnblogs.com/lhrbest ● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ............................................................................................................................................. ● QQ群號：230161599（滿）、618766405 ● 微信群：可加我微信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友（646634621），註明新增緣由 ● 於 2017-09-01 09:00 ~ 2017-09-30 22:00 在魔都完成 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ............................................................................................................................................. ● 小麥苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/ ............................................................................................................................................. 使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。    小麥苗的微信公眾號      小麥苗的DBA寶典QQ群1     小麥苗的DBA寶典QQ群2        小麥苗的微店 .............................................................................................................................................

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