This discussion is archived
4 Replies Latest reply: Oct 9, 2007 12:36 AM by Bouch RSS

Can't Reverse-Engineering Synonyms from Oracle 10g

315809 Newbie
Currently Being Moderated
Common Scenario:

In Oracle database, the user OPE_OWNER owns all the objects of an operation system as the data source. For security reason, don't allow the ODI or other ETL tool to access the data source using OPE_OWNER connection information. Instead, we create a read-only user OPE_READ_ONLY in the same database instance and grant this user the SELECT privileges on appropriate OPE_OWNER's tables. We only allow ODI or other ETL tool to access the operational system information through this read-only user. This is a common scenario in the data warehouse world.

Test Case and Step:

In Oracle 10g(R2) database, the operational system owner OPE_OWNER has a table called CUSTOMER. We want to import the metadate (reverse engineering) of CUSTOMER into ODI. We perform the following steps:
1. Grant the SELECT privilege on OPE_OWNER.CUSTOMER to the read-only user OPE_READ_ONLY.
2. Create a priviate synonym CUSTOMER on the table OPE_OWNER.CUSTOMER for OPE_READ_ONLY.
3. Create an Oracle Data Server and a physical schema in ODI using the user OPE_READ_ONLY.
4. Import ODI's built-in Oracle RKM
5. Reverse the synonym object CUSTOMER into ODI (try both the options Standard and Customized).

ODI Synonyms Reverse Engineering Problem:

The synonym was reversed and a data store named CUSTOMER showed up in the tree-view. But there is no detailed metadata information at all. All the columns and constraints information are empty.

Anything wrong?

We tried the same approache with Oracle Warehouse Builder, the synonym's metadata was reversed successfully.