dbms_metadata遇到ORA-31603的解决方案

来源：互联网发布：网络维护主要做什么编辑：程序博客网时间：2024/05/18 13:23

环境:

OS:Red Hat Linux As 5

DB:10.2.0.1

1.用户A获取用户B下的表的DDL报如下错误

SQL> SELECT dbms_metadata.get_ddl('TABLE','TB_TEST','USER_B') FROM dual;

ERROR:

ORA-31603: object "TB_TEST" of type TABLE not found in schema "USER_B"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105

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

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

ORA-06512: at line 1

www.2cto.com

2.查看用户A用户的权限

column Grantee format a10;

column Privilege format a25;

column Type format a5;

Select Pri.Grantee,

Pri.Privilege,

Pri.Admin_Option,

Case

When Type = '1' Then

'Privs'

When Type = '2' Then

'Role'

End Type

From (Select Sp.Grantee, Sp.Privilege, Sp.Admin_Option, '1' Type

From Dba_Sys_Privs Sp www.2cto.com

Union All

Select Rp.Grantee, Rp.Granted_Role, Rp.Admin_Option, '2' Type

From Dba_Role_Privs Rp) Pri

15 Where Pri.Grantee = 'USER_A';

GRANTEE PRIVILEGE ADM TYPE

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

USER_A EXECUTE ANY PROCEDURE NO Privs

USER_A UNLIMITED TABLESPACE NO Privs

USER_A CONNECT NO Role

USER_A RESOURCE NO Role

发现用户没有select_catalog_role该角色，授予该角色给用户A

grant select_catalog_role to USER_A;

3.再次获取用户B下的表的DDL

SQL> set long 999999999;

SQL> SELECT dbms_metadata.get_ddl('TABLE','TB_TEST','USER_B') FROM dual;

DBMS_METADATA.GET_DDL('TABLE','TB_TEST','USER_B') www.2cto.com

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

CREATE TABLE "USER_B"."TB_TEST"

( "ID" NUMBER NOT NULL ENABLE,

"NAME1" CHAR(2000),

"NAME2" CHAR(2000),

"NAME3" CHAR(2000),

"NAME4" CHAR(2000),

"NAME5" CHAR(2000)

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

DBMS_METADATA.GET_DDL('TABLE','TB_TEST','USER_B')

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

TABLESPACE "TPS_TEST"

问题解决.