1

I need to read the structure of some database objects from an oracle database. So I want to do the following:

select dbms_metadata.get_ddl('TABLE', 'MY_TABLE', 'OTHERUSER') from dual

That works well if I'm logged in with the OTHERUSER user. But for PROD I only have a user, which has synonyms and uses this objects with that synonyms. I can select the TABLE information with the ALL_TABLES where I see that the owner is OTHERUSER, but when I run this code above with a different user, I get the following error:

ORA-31603: object "MY_TABLE" of type TABLE not found in schema "OTHERUSER"
ORA-06512: at "SYS.DBMS_METADATA", line 6069
ORA-06512: at "SYS.DBMS_METADATA", line 8666
ORA-06512: at line 1

It is exactly on the schema OTHERUSER. What can I do different to get this run? I can select from ALL_TABLES, ALL_INDEXES and so on, so I can read the information anyway, so it might not be a right problem, or am I wrong?

I know as a workaround I can use a procedure which runs in the context of OTHERUSER, but this is more than ugly.

APC
  • 144,005
  • 19
  • 170
  • 281
Michael Baarz
  • 406
  • 5
  • 17
  • Anyway, it is like it is. I wouldn't do that exercise if there is a way around! – Michael Baarz Apr 02 '19 at 12:41
  • the database is setup to secure itself, you do not have the rights to get what you're asking for...if this object appears to you in all_tab_cols and all_constraints, etc, you could query those views and RE yourself the DDL to create that table - but if you want the table ddl and you don't have the rights, you ask the OWNER for them – thatjeffsmith Apr 02 '19 at 13:36
  • 1
    @apc i know, but sometimes i prefer to teach folks how to fish vs just giving them the fishing rod - also, that fallback feature in sqldev may or may not work – thatjeffsmith Apr 02 '19 at 14:09
  • I'm a bit at a loss as to what sort of solution you're hoping for. I think you need to describe the exact capability you need, and also list all the constraints you're working under, including what system privileges your connecting schema has in Prod. – APC Apr 03 '19 at 09:41
  • @APC Ah that is why you voted it down. I don't see it like you, the problem is described very well. When having knowledge of Oracle (which should be the case when answering or voting on that question) or seeing the error and have google you see that I miss the SELECT_CATALOG_ROLE of the user. More I don't need to describe whats around, because I need to fix exactly this problem, nothing else. – Michael Baarz Apr 03 '19 at 10:52

1 Answers1

1

One solution of doing it is described here:

http://dbmsdirect.blogspot.com/2007/10/using-dbmsmetadata-in-procedure-to.html

Michael Baarz
  • 406
  • 5
  • 17