oracle dbms_metadata 獲取ddl語句

parameters發表於2011-08-15
Oracle
Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE在某些情況下，需要知道已經定義的物件的ddl語句，下面介紹以下方法：

直接查詢

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

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

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

  CREATE TABLE "SCOTT"."EMP"

   (    "EMPNO" NUMBER(4,0),

        "ENAME" VARCHAR2(10),

SQL>

此時會發現使輸出的結果不全，這是因為sqlplus 預設輸出的lob長度為80，做如下設定後查詢

SQL> set pagesize 1000

SQL> set long 8000

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

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

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

 

  CREATE TABLE "SCOTT"."EMP"

   (    "EMPNO" NUMBER(4,0),

        "ENAME" VARCHAR2(10),

        "JOB" VARCHAR2(9),

        "MGR" NUMBER(4,0),

        "HIREDATE" DATE,

        "SAL" NUMBER(7,2),

        "COMM" NUMBER(7,2),

        "DEPTNO" NUMBER(2,0),

         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "USERS"  ENABLE,

         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")

          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE

   ) 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> execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', false);

PL/SQL procedure successfully completed.

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

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

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

 

  CREATE TABLE "SCOTT"."EMP"

   (    "EMPNO" NUMBER(4,0),

        "ENAME" VARCHAR2(10),

        "JOB" VARCHAR2(9),

        "MGR" NUMBER(4,0),

        "HIREDATE" DATE,

        "SAL" NUMBER(7,2),

        "COMM" NUMBER(7,2),

        "DEPTNO" NUMBER(2,0),

         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") ENABLE,

         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")

          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE

   )

 

格式化輸出，不顯示約束等：

SQL> execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS', FALSE)

PL/SQL procedure successfully completed.

SQL> execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,'REF_CONSTRAINTS', FALSE)

PL/SQL procedure successfully completed.

SQL> execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR', TRUE)

PL/SQL procedure successfully completed.

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

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

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

  CREATE TABLE "SCOTT"."EMP"

   (    "EMPNO" NUMBER(4,0),

        "ENAME" VARCHAR2(10),

        "JOB" VARCHAR2(9),

        "MGR" NUMBER(4,0),

        "HIREDATE" DATE,

        "SAL" NUMBER(7,2),

        "COMM" NUMBER(7,2),

        "DEPTNO" NUMBER(2,0)

   ) ;

SQL>

 

最簡單的方式:

SQL> execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY', FALSE)

PL/SQL procedure successfully completed.

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

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

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

  CREATE TABLE "SCOTT"."EMP" ("EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10), "JOB" V

ARCHAR2(9), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER

(7,2), "DEPTNO" NUMBER(2,0)) ;

 

同時需要了解表所在使用者擁有的許可權，以便能夠正常的使用表：

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

 

DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCOTT')

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

   GRANT "CONNECT" TO "SCOTT";

   GRANT "RESOURCE" TO "SCOTT";

SQL>

 

 

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

相關文章