Oracle Database -Private DB Link
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Oracle Database -Private DB Link

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Posts
    38

    Question

    Hi All,

    I'm looking for a solution for the below listed problem:

    I have 2 databases (lets say DB1 & DB2) and in both the databases I have a user (lets say USER1) and a private database link (created without password) from (DB2 to DB1). I have few tables (T1 & T2) in DB1 and using a procedure in DB2 of USER1 the data is populated into a table from DB1 by using the database link (tables are also owned by USER1).

    Now, I have another user USER2 in DB2 instance who is referencing the tables in USER1 (DB2 instance), but as and when required USER2 should be able to execute the PROCEDURE owned by USER1 for refreshing the Tables data.

    Problem: As the DB Link is a private database link created without using passwords USER2 is unable to execute the procedure. Given above limitations will I be able to execute the procedure either using DBMS_JOB or by creating SNAPSHOTS and refereshing as and when required from USER2 itself.

    Any help is highly appreciated.

    Thanks in advance

    Shanker

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Wondering if using PUBLIC dblink would help?

    Sanjay

  3. #3
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    Hi ,

    I think even a public/private dblink can be created . provided grants are given on the required objects to both user1 and user2.

    also , i would like to confirm following query reg dblinks .

    if u are pointing to remote table directly then DML is bit slow . instead , if u make a view in the local database for the remote table and point it to the remote table through view it is faster . is this TRUE ??


  4. #4
    Join Date
    May 2002
    Posts
    232
    Hiii,you create public dblink by uname/pass,ipaddress,port etc.
    I was migrated 8idb to 9i db by using dblink.
    I was migrated around 3gb data with in 15mins,i think db links are faster than exp/imp.
    cheers
    kavitha
    kavitha

  5. #5
    Join Date
    Nov 2000
    Posts
    38
    As I stated above, I cannot make use public database link.

    As I need to use only private database link (that will be create without giving password at the
    time of creating DB Link). Even though I create any object in USER1 that uses DB Link. Even though
    the other users have access objects of USER1, they are unusable as Oracle is throwing me an error
    ORA-01017: invalid username/password; logon denied

    Regards

    Shanker

  6. #6
    Join Date
    May 2002
    Posts
    232
    Hiiiiiiii,u follow below syntex
    CREATE PUBLIC DATABASE LINK ABC CONNECT TO RAI IDENTIFIED BY ORACLE
    USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
    (HOST = 132.130.100.32)(port = 1521)))(connect_data =(sid = ORCL)))';

    I hope it helps for you
    kavitha

  7. #7
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Dblink without password is using connected user for connecting to remote db.
    Try either of the following:

    1.
    Create private dblink using user and pass and it should work.
    Like:
    create database link DB1 connect to USER1 identified by using 'mrl0';

    2. Create USER2 on DB1 and give him rights on USER1@DB1 tables he needs.

    Tomaz

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