使用dbms_metadata.get_ddl遇到ORA-31603

使用dbms_metadata.get_ddl遇到ORA-31603

建了一个外部表，想看看这个表的信息，一查就报错了：

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

ERROR:

ORA-31603: object "ext_case1" of type TABLE not found in schema "SCOTT"

ORA-06512: at "SYS.DBMS_METADATA", line 1546

ORA-06512: at "SYS.DBMS_METADATA", line 1583

ORA-06512: at "SYS.DBMS_METADATA", line 1901

ORA-06512: at "SYS.DBMS_METADATA", line 2792

ORA-06512: at "SYS.DBMS_METADATA", line 4333

ORA-06512: at line 1

no rows selected

看看表时候存在，表确实是存在的：

SQL> select * from ext_case1;

ENAME    JOB                                 SAL

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

SMITH      CLEAK                            3904

ALLEN     SALESMAN                     2891

WARD     SALESMAN                      3128

KING       PRESIDENT                     2523

SQL> 

郁闷了，换sysdba用户试了一下，错误依然，有检查了一遍，还是不知道错误在那里，不想弄了，也不想看书了，下去买了瓶水，回来又试了一把，还是错误。

不解决不死心，google一把，悲剧了，google又间歇性的瘫痪了，无奈baidu,找到一个答案说是用户没有查询权限。

给scott赋权限：

SQL> grant select_catalog_role to scott;

Grant succeeded.

SQL> 

再查询还是不行，唉。。。打开google，google正常工作了。又找到一个答案，说是get_ddl里的参数都要大写。

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

DBMS_METADATA.GET_DDL('TABLE','EXT_CASE1')

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

  CREATE TABLE "SCOTT"."EXT_CASE1"

   ( "ENAME" VARCHAR2(10),

"JOB" VARCHAR2

SQL> 

输出的信息好像没有显示全，要设置一下sqlplus的long的设置来改变显示的长度，默认的long是80：

SQL> set long 2000

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

DBMS_METADATA.GET_DDL('TABLE','EXT_CASE1')

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

  CREATE TABLE "SCOTT"."EXT_CASE1"

   ( "ENAME" VARCHAR2(10),

"JOB" VARCHAR2(20),

"SAL" NUMBER

   )

   ORGANIZATION EXTERNAL

    ( TYPE ORACLE_LOADER

      DEFAULT DIRECTORY "EXTDIR"

      ACCESS PARAMETERS

      ( records delimited by newline

DBMS_METADATA.GET_DDL('TABLE','EXT_CASE1')

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

skip 6

fields terminated by ","

(ename,job,sal)

    )

      LOCATION

       ( 'ldr_case1.ctl'

       )

    )

SQL> 

另外DBMS_METADATA.GET_DDL默认的是查看当前用户的信息，如果你以用户A来查看用户B的表要在dbms_metadata.get_ddl的第三个参数里加上表/表空间的schmea，否则就会报ORA-31603。

以sysdba查看scott的表(不加schmea)：

SQL> show user 

USER is "SYS"

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

ERROR:

ORA-31603: object "BONUS" of type TABLE not found in schema "SYS"

ORA-06512: at "SYS.DBMS_METADATA", line 1546

ORA-06512: at "SYS.DBMS_METADATA", line 1583

ORA-06512: at "SYS.DBMS_METADATA", line 1901

ORA-06512: at "SYS.DBMS_METADATA", line 2792

ORA-06512: at "SYS.DBMS_METADATA", line 4333

ORA-06512: at line 1

no rows selected

SQL> 

以sysdba查看scott的表(加schmea)：

SQL> show user

USER is "SYS"

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

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

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

  CREATE TABLE "SCOTT"."BONUS"

   ("ENAME" VARCHAR2(10),

"JOB" VARCHAR2(9),

"SAL" NUMBER,

"COMM" NUMBER

   ) 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"

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

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

SQL> 

总结：

1)dbms_metadata.get_ddl()包()内的参数都要大写。
2)是否查的当前用户的DDL，不是要加上对象的schmea。
3)如果信息显示不全，set long 9999(随便设置他的大小，就是为了完全显示查找的结果)。

参考：http://space.itpub.net/7351078/viewspace-621050

PS.最近想东西想多了，又浮躁取来了，一个小问题折腾了几个小时，一定要静下心来。 come on !