Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem with dbms_metadata.get_ddl Expand / Collapse
Author
Message
Posted Friday, April 12, 2013 10:16 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:06 PM
Points: 4,253, Visits: 4,295
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/

Post #1441790
Posted Friday, April 12, 2013 11:12 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:48 AM
Points: 20,857, Visits: 32,877
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


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1441801
Posted Friday, April 12, 2013 11:35 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:06 PM
Points: 4,253, Visits: 4,295
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/

Post #1441821
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse