Oracle 11: UNX/UNV possibility to use user.table.column

[Former Member]

Dear all,

I am currently working on a solution where the technical user in our Universe Connection has changed. The Connection is via Oracle Client to an Oracle 11 db. Due to this change I need to address the right tables/ views via user.table.column, before it was just table.column.
Otherwise the tables are not found. I tried to insert it into Customer SQL Query in Webi, but it will not work because of the optional prompts.

Do you know another way to fix this Problem?

I am using both UNX and UNV in BO 4.1 SP2.

Best Regards,

Mischa

[Former Member]

Have a read up on Virtual Private Databases it may be of use:

http://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm

Regards,

Mark

[former_member285534]

Hi Mischa,

If your "old" user hasn't been dropped at the database level, that is, if only the tables have been moved or recreated on another schema, you have the option to create synonyms.  That way BO will find again transparently the tables (without any changes needed on the BO layer).

On the old_user session:

CREATE OR REPLACE SYNONYM my_table FOR new_user.my_table;

old_user must have select privileges over new_user.my_table.

If old_user has been dropped you can recreate it and then create the synonym with the above procedure.

Regards,

Fernando

[amitrathi239]

Hi,

What about if you will change the owner/Qualifier of the table to new user in IDT Data Foundation layer?

Right click Table->Change the owner/Qualifier

Amit