DBMS_SNAPSHOT over DBlink

If we create all the materialized views in our reporting instance and want to do a refresh of these views from the reporting instance using the DBMS_SNAPSHOT package how do we establish the dblink between the reporting instance and our applications instance.

Thankyou,
Ganesh Ryali.
babunagendraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RCorfmanCommented:
When the snapshots (materialized views) were created originally, the db_link was specified, you just refresh them using the package and the same link it utilized.  I'll put together an example in a minute.
RCorfmanCommented:
--- First, we create the materialized view using an ALREADY DEFINED database link
SQL> create materialized view udtmv as select * from warehouse_sites@whread;

Materialized view created.

-- then we can use the dbms_snapshot package to refresh it
SQL> exec dbms_snapshot.refresh('UDTMV');

PL/SQL procedure successfully completed.

-- you can see that the link name that we used is stored in the user_snapshots table
SQL> select table_name,master_view,master_owner,master,master_link from user_snapshots
  2  where name='UDTMV';

TABLE_NAME                     MASTER_VIEW
------------------------------ ------------------------------
MASTER_OWNER                   MASTER
------------------------------ ------------------------------
MASTER_LINK
-----------------------------------------------------------------------------------------
UDTMV
CMTWH                          WAREHOUSE_SITES
@WHREAD.MYGLOBALNAME

--- if you drop the database link and then try to refresh, the call do dbms_snapshot fails...
SQL> drop database link WHREAD;

Database link dropped.

SQL> exec dbms_snapshot.refresh('UDTMV');
BEGIN dbms_snapshot.refresh('UDTMV'); END;

*
ERROR at line 1:
ORA-02019: connection description for remote database not found
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 1

SQL>

-----------------
So, the database link to your application instance must be created before you create the materialized view in the reporting instance. And, when you refresh the materialized view, the link must still be in existance.
babunagendraAuthor Commented:
Thankyou for the clue. Actually I did it in a different fashion as we were required to create the MV in a remote loaction and needed to refrest it from application instance. Created the MV@ dblink on remote server and then created the aynonyms for the MVs on the application instance which was easily refreshed. But we still have to test it if dbms_snapshot.refresh would work with a synonym.

Thankyou,
Ganesh Ryali.
RCorfmanCommented:
If you look at user_snapshots, you will see the link name is embedded there.  It won't refresh if THAT link, specified in user_snapshots no longer exists.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.