April 12, 2013 at 10:16 am
I get Data from some Oracles Database. We own the Data but the system was designed by an outside firm.
They took away out permissions to browse the tables in a tool such as SQLDeveloper.
I can get a list of table by executing the following command:
select dbms_metadata.get_ddl('TABLE','TRANSACTION_FACT','RDB_DWH') from dual;
I can get a tables column definitions by executing the following:
DESCRIBE RDB_DWH.TRANSACTION_FACT
But I need the Primary Key, Indexes as well, Unique Constraints, Foreign Key Constraint, etc.
I execute the following command in an attempt to Column information for a specific table.
So I try the dbms_metadata.get_ddl Package and I get the following error. RDB_DWH is the owner name and TRANSACTION_FACT is the table name.
select dbms_metadata.get_ddl('TABLE','TRANSACTION_FACT','RDB_DWH') from dual;
ORA-31603: object "TRANSACTION_FACT" of type TABLE not found in schema "RDB_DWH"
ORA-06512: at "SYS.DBMS_METADATA", line 4018
ORA-06512: at "SYS.DBMS_METADATA", line 5843
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.
But even if that worked I would not get the PK, Indexes as well as all the constraints.
I'm trying to get permissions. Currently no one in the company has permissions to brows tables, etc. SUbmitted a request but in the even that that does not happen I need to do it in SQL.
Any help would be greatly appreciated.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 12, 2013 at 11:12 am
A quick BING search (search string: ORACLE and INFORMATION_SCHEMA views) led me here, does this help? http://www.alberton.info/oracle_meta_info.html
April 12, 2013 at 11:35 am
Yeah, that worked.
I googled it a lot but unfortunately I did not find that article or anything similar to it.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply