15

I want to get the DDL of Table CARD_TABLE in XT schema 

SQL> select dbms_metadata.get_ddl('TABLE','CARD_TABLE','XT') from dual;
    ERROR:
    ORA-31603: object "CARD_TABLE" of type TABLE not found in
    schema "XT"
    ORA-06512: at "SYS.DBMS_METADATA", line 5746
    ORA-06512: at "SYS.DBMS_METADATA", line 8333
    ORA-06512: at line 1

But my select Query works

select count(*) from XT.CARD_TABLE;
count(*)
---------
0

I queried dba_objects it still got the table:

    SQL> select owner,object_type from DBA_OBJECTS
    where object_name='CARD_TABLE'  2
      3  ;


PUBLIC     SYNONYM
    XT     TABLE PARTITION
    XT     TABLE PARTITION
    XT     TABLE PARTITION
    XT     TABLE
    XT     TABLE PARTITION
    VAT    TABLE

    7 rows selected.
Hash
  • 4,647
  • 5
  • 21
  • 39
user2711819
  • 960
  • 3
  • 16
  • 29

2 Answers2

18

From the dbms_metadata documentation:

If nonprivileged users are granted some form of access to an object in someone else's schema, they will be able to retrieve the grant specification through the Metadata API, but not the object's actual metadata.

So unless you're connected as a privileged user, you can't see the DDL for another user's objects. You would need to connect as SYS, or have the SELECT_CATALOG_ROLE role granted to your user to be able to get XT's object definition.

Even with that role:

In stored procedures, functions, and definers-rights packages, roles (such as SELECT_CATALOG_ROLE) are disabled. Therefore, such a PL/SQL program can only fetch metadata for objects in its own schema. If you want to write a PL/SQL program that fetches metadata for objects in a different schema (based on the invoker's possession of SELECT_CATALOG_ROLE), you must make the program invokers-rights.

If you're calling dbms_metadata from an anonymous PL/SQL block that doesn't matter, but if you're calling it from a procedure you will have to include an AUTHID clause in the procedure declaration, adding AUTHID CURRENT_USER.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • 2
    Oracle might have shown no privilege/access error :) – user2711819 Sep 24 '14 at 21:17
  • 1
    I have already SELECT_CATALOG_ROLE granted but when select dbms_metadata.get_ddl it return error : ORA-06512: at "SYS.DBMS_METADATA", line 2625 ORA-06512: at "SYS.DBMS_METADATA", line 2668 ORA-06512: at "SYS.DBMS_METADATA", line 2983 ORA-06512: at "SYS.DBMS_METADATA", line 3897 ORA-06512: at "SYS.DBMS_METADATA", line 5678 ORA-06512: at line 1 31603. 00000 - "object \"%s\" of type %s not found in schema \"%s\"" *Cause: The specified object was not found in the database. *Action: Correct the object specification and try the call again. – adramazany May 08 '17 at 11:16
  • @adramazany - ask a new question, showing the statement you are running as well as the error. Also show the entry for the relevant object from `dba_objects`, and all the privileges that your current user has against that object. Make sure the object type and name match `dba_objects` exactly, including the case of the object name... – Alex Poole May 08 '17 at 11:20
  • @adramazany I have same problem.Did you find any solution.We also granted .SELECT_CATALOG_ROLE permission. – parita porwal Jun 14 '17 at 13:28
  • @paritaporwal - since adramazany doesn't seem to have asked a new question, I've added a note about `authid`; does that help, if you're calling it from a stored procedure? – Alex Poole Jun 14 '17 at 13:49
  • @AlexPoole.Thanks for suggestion. But my exact question is n this link - https://stackoverflow.com/questions/44545654/which-permission-need-to-grant-to-access-sys-dba-systems. And when I try to execute the query - grant select on sys.dba_objects to johnsmith; it gives error - SQL Error: ORA-01031: insufficient privileges 01031. 00000 - "insufficient privileges" – parita porwal Jun 15 '17 at 05:52
  • 1
    I execute grant SELECT ANY DICTIONARY query. By this my SELECT * FROM sys.dba_objects where owner ='SCHEMA' and object_type = 'TABLE' query is run for another schema but SELECT DBMS_METADATA.GET_DDL('TABLE','TASK','OPENSKYE') FROM dual gives error - ORA-31603: object "TASK" of type TABLE not found in schema "OPENSKYE" "object \"%s\" of type %s not found in schema \"%s\"" *Cause: The specified object was not found in the database. *Action: Correct the object specification and try the call again. – parita porwal Jun 15 '17 at 09:51
  • @paritaporwal - if this old answer doesn't solve your problem then please ask a new question that shows exactly what you are doing and what problem you have - particularly if you're calling it from a stored procedure or an anonymous block, – Alex Poole Jun 15 '17 at 09:55
2
grant SELECT_CATALOG_ROLE to <user> with delegate option;

it work for me. Do this after modify procedure

grant SELECT_CATALOG_ROLE to procedure <procedure name>;
dmitryaivanov
  • 31
  • 3