-
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
-
Wondering if using PUBLIC dblink would help?
Sanjay
-
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 ??
-
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
-
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
-
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
-
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
