Accessing Objects in another schema under same database 1

[AlStl]

I am working on an application that is hosted on same DB server as another application. We share this server with this another app, but we are under different schemas.

We have a need of RO access a table or materialized view or view under the schema of this another application. What would the best way to accomplish it and maintain in the long run:

a) Ask other application to allow RO access to their objects via a DB_link? May not be DB link since it allows a link between two different DBs and not the same.

b) Is there a better another way to handle this? How to write direct SQL queries against this other schema? We can provision a user ID to login to their schema and that user has RO to the objects we need?

Any ideas will be highly appreciated.

Thx

Al

 

[johnherman]

I suggest replication between the two different schemas. It's relatively easy and it puts the burden of the update on Oracle, and you can choose how current the replicated table(s) need to be. From virtually immediate, hourly, daily, etc.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[AlStl]

John,

Thanks for your response. Replication is not a possibility due to limited resources on the server and network bandwidth availability.

Al

 

[stefanhei]

Accessing objects in another schema is pretty straightforward.
First you need the SELECT privilege on the table or MV you wish to access - the DBA or the other application team has to grant it. This privilege can be granted either directly to the user or via a database role. If you wish to access the object by a stored object (like a database function or procedure) the privilege has to be granted directly, if it is just used for queries roles can be used.
To access the object simply prefix it with the schema name where it resides:

SELECT * FROM otherSchema.tableName

There is no need for a DB-link or replication.

 

[Beilstwh]

And if you want to hide the schema name you can use a synonym

connect as otherschema
grant select on my_table to myschema;
connect as myschema
create synonym my_table for otherschema.my_table;

Then in your code simply use

select * from my_table;

o

Bill
Lead Application Developer
New York State, USA