Creating View

Hi all ,

I need your expertise in Creating Oracle View.

I have 2 databases, Database A reside in Server Server_A and
Database B reside in Server_B.

I need to create a view for a table in database A (Server_A), so that the person can see the table in database B (Server_B).

What is the best options to implement this requirement.


Please help as i am a newbie. Your help is very much appreciared

God Bless
Thanks and regards,
Rob

i think a public synonym will do.

You can create a database link on database A to database B. Suppose you want to create a view on database APP1 that combines the EMP table with the DEPT table on database APP2. EMP is owned by FRED and DEPT is owned by JANE.

Logged in as FRED on APP1:

SQL> create database link JANE_APP2 connect to JANE identified by "" using 'APP2';

The USING clause of the CREATE DATABASE LINK statement refers to the Net8 connect string for the other database in the tnsnames.ora file on the APP1 server.

Test the link by doing something like this:

Still connected to APP1 as FRED:

SQL> SELECT count(*) FROM dept@JANE_APP2;

This should work. Go ahead and create your view, just rememeber to suffix any reference to the remote table with @.

One warning, I believe that there are bugs in some versions of Oracle where the entire remote table is needlessly passed down the wire when using a database link. Maybe others can provide platforms and versions if I'm correct?

HTH

Austin

Woops:

Quote:

---

This should work. Go ahead and create your view, just rememeber to suffix any reference to the remote table with @.

---

Should be @JANE_APP2

Hang on, you don't want to actually join the local and remote tables do you? No need for a view then, use a synonym as suggested above. You will, however, still need to create a database link first.

Hi Thank you so much for your reply and help.
I have tried already creating database link.

Thanks.

My requirement is also to have the table in read only mode. So is that possible to have read only synonym. I know if i create a view i can set it to read only then create the database link for it.

What about synonym?

Thanks to you, i am salute.

Robby

if you need to update the base table through
the view, there are limitations.

but you can update the base table through synonym
as normal as table updation.

you can prevent the users from updating the table,
by not giving the privileges.

you can not have read only synonyms.
it is just another name for the table(or any objects).

-Raja

To clarify rajabalachandra's solution:

The synonym can be read only by doing the following:

1) On database B create a user account. I'll call the user READONLY for clarity. You can of course call it what you want.

2) Grant create session to READONLY

3) Grant select on table to be accessed read only on database A to READONLY

4) Create a database link on database A specifying READONLY user in the CONNECT TO clause

5) Create a synonym on database A for the table that READONLY has SELECT on. Suffix the table name with @database link name created in step 4

HTH

Austin