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.