Creating View
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Creating View

  1. #1
    Join Date
    Feb 2003
    Posts
    24

    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

  2. #2
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    i think a public synonym will do.

  3. #3
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    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

  4. #4
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    Woops:
    This should work. Go ahead and create your view, just rememeber to suffix any reference to the remote table with @.
    Should be @JANE_APP2

  5. #5
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    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.

  6. #6
    Join Date
    Feb 2003
    Posts
    24
    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

  7. #7
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    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

  8. #8
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width