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.
We tried the same approache with Oracle Warehouse Builder, the synonym's metadata was reversed successfully.
ODI allows you to reverse the OPE_OWNER Objects without using the OPE_OWNER password.
1) Grant select any table on OPE_OWNER table's to OPE_READ_ONLY (or some tables).
2) Create a server on ODI the user should be OPE_READ_ONLY
3) Create a physical Schema and select
OPE_OWNER in (Schema) and OPE_READ_ONLY in (Work Schema).
You can now reverse all the tables from OPE_OWNER using the standard reverse or the RKM Oracle.
If you have to reverse only synonyms, you have to update the RKM
I have the same error trying to reverse a synonym from oracle... ODI doesn't show the columns or constrains but i'm not using different schemas... source table and synonym are at the same schema, any suggestion.
You don't have to have different schema... the problem is that the standard reverse didn't reverse synonyms. The RKM Oracle provided by ODI don't do the job!
If you know how to query the table "ALL_SYNONYMS" from oracle Database, it will be easy you to adapt the RKM Oracle and to reverse the synonym.