×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Accessing Objects in another schema under same database

Accessing Objects in another schema under same database

Accessing Objects in another schema under same database

(OP)
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

RE: Accessing Objects in another schema under same database

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


RE: Accessing Objects in another schema under same database

(OP)
John,

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

Al

RE: Accessing Objects in another schema under same database

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:

CODE --> SQL

SELECT * FROM otherSchema.tableName 
There is no need for a DB-link or replication.

RE: Accessing Objects in another schema under same database

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close