oracle dbms_metadata 獲取ddl語句

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/，如需轉載，請註明出處，否則將追究法律責任。

oracle dbms_metadata 獲取ddl語句

相關文章