DBMS_METADATA GET_DDL : Avoid ORA-31603 error by using SELECT_CATALOG_ROLE

While extracting DDLs from command-line, I always find the interesting thing to learn. Below post is my another learning with the same.

This is about how to avoid ORA-31603 error while extracting DDLs of other schema objects even one have all the privileges on the objects. This is somethings dealing with database security and about one role SELECT_CATALOG_ROLE.

Lets start with example with formulating the problem…………….

I’m testing this with two sample schema HR and SCOTT on 10g DB.

Lets connect to HR and test wether this user have access to SCOTT EMP table or not.

SQL> conn hr/hr

Connected.

SQL> desc scott.emp

ERROR:

ORA-04043: object scott.emp does not exist

Oh no. Lets give all the privilege on EMP table to HR.

SQL> conn scott/tiger

Connected.

SQL> grant all on emp to hr;

Grant succeeded.

Hmmm………. Now hr have all the privilge on SCOTT.EMP table. So why cant HR extract the DDLs for the same. Lets see….

SQL> conn hr/hr

Connected.

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

ERROR:

ORA-31603: object “EMP” of type TABLE not found in schema “SCOTT”

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

no rows selected

OOOOOps……….. So why HR cant do this. Even he has all the privilege on SCOTT.EMP table. This is becasue HR needs SELECT_CATALOG_ROLE to browse the dictionaries as HR is not privileged user like DBA. When DBMS_METADATA package is called, it attempts to browse the dictionaries. If Calling use does not have privilege to browse the dictionary the it fires error by saying invisibility to the target object that is ORA-31603 error.

Now lets complete the loop by granting this role to HR.

SQL> conn sys as sysdba

Connected.

SQL> grant select_catalog_role to hr;

Grant succeeded.

Ready to test it.

SQL> conn hr/hr

Connected.

SQL> set head off

SQL> set long 40000

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

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”

Hurray !!

So to use DBMS_METADATA, one should have at least SELECT_CATALOG_ROLE. If user is already a DBA then it already have this role.

Thanks & Enjoy !!