ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle dbms_metadata 获取ddl语句

oracle dbms_metadata 获取ddl语句

原创 Linux操作系统 作者:parameters 时间:2011-08-15 09:13:52 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-04-07

  • 博文量
    45
  • 访问量
    50219