Materialized view refresh - ORA-02019: connection description for remote database not found.

the owner of the materialized view needs to own the database link or the link needs to be public.

Hi,
Thank you for your reply, I have checked the link & it's a public link.

All the posts I found seem to point to the link but I have checked & double checked this & appears correct. Is there anything else i can try?

there must be something else that isn't being seen

can you post the exact query you used  and the results of

select * from dba_db_links

In the materialized view creation, are you using FORCE or FAST?

We are using FAST.

The results of SELECT * FROM DBA_DB_LINKS are in the attached file DBLinks.txt.

The MV's and Refresh Group are owned by user CAL.

As there are 3 DB Links will it default to the one owned by CAL or use the Public view?

When I tested the links I logged in as both SYS and MVADMIN and ran: -
SELECT SYSDATE FROM DUAL@SDIR.GEMCO.LOCAL
Both returned the date

Thank you for your assistance.
DBLinks.txt

FAST vs COMPLETE vs FORCE is irrelevant for the error you are getting


It will use the one owned by CA.  What happens when you test using CAL?

What is the MV query?

The laptop is temp unavailable so I'll test the CAL link tomorrow but the MV Query is as follows: -

SELECT "CONFIRMED", "DATE_BOOKED", "DATE_PROPOSED", "DATE_COMPLETE", "PRINTED", "CANCELED", "CANCEL_REASON", "CONFIRMED_WITH", "CONFIRMED_DATE", "CREATE_USER", "CREATE_DATE_TIME", "EQUIPMENT_DUE_FROM", "EQUIPMENT_DUE_TO", "SCHEDULE_ID", "NUMBER_OF_ITEMS", "CANCELLED_BY", "CANCELLED_DATE", "CUSTOMER_ORDER_NUMBER", "COMMENTS_TEMP", "COMMENTS", "CALL_ID", "JOB_SHEET", "ENGINEER_ID", "DATE_DUE", "CALL_TYPE", "CUSTOMER_ID", "COMPLETE" FROM "CAL"."CALLS"@SDIR.GEMCO.LOCAL

When I ran the refresh and checked the sessions on the Oracle server I could see the session which was trying to execute this statement. It was this sessions that displayed the ORA-02019 error.
The session was showing the following as the current SQL...
SELECT /*+ */ "A2"."CONFIRMED","A2"."DATE_BOOKED","A2"."DATE_PROPOSED","A2"."DATE_COMPLETE","A2"."PRINTED","A2"."CANCELED","A2"."CANCEL_REASON","A2"."CONFIRMED_WITH","A2"."CONFIRMED_DATE","A2"."CREATE_USER","A2"."CREATE_DATE_TIME","A2"."EQUIPMENT_DUE_FROM","A2"."EQUIPMENT_DUE_TO","A2"."SCHEDULE_ID","A2"."NUMBER_OF_ITEMS","A2"."CANCELLED_BY","A2"."CANCELLED_DATE","A2"."CUSTOMER_ORDER_NUMBER","A2"."COMMENTS","A2"."CALL_ID","A2"."JOB_SHEET","A2"."ENGINEER_ID","A2"."DATE_DUE","A2"."CALL_TYPE","A2"."CUSTOMER_ID","A2"."COMPLETE","A1"."CHANGE_VECTOR$$" FROM "CAL"."CALLS" "A2", ( (SELECT DISTINCT "A5"."CALL_ID" "CALL_ID","A5"."CHANGE_VECTOR$$" "CHANGE_VECTOR$$" FROM "CAL"."MLOG$_CALLS" "A5" WHERE "A5"."SNAPTIME$$">:1 AND "A5"."DMLTYPE$$"<>'D')UNION (SELECT "A4"."CALL_ID" "CALL_ID","A4"."CHANGE_VECTOR$$" "CHANGE_VECTOR$$" FROM "CAL"."USLOG$_CALLS"@! "A4" WHERE "A4"."DMLTYPE$$"<>'I')) "A1" WHERE "A2"."CALL_ID"="A1"."CALL_ID" ORDER BY "A2"."CALL_ID"

Hope this helps

Hi,

I have logged into SQLPlus as user CAL and run: -

select sysdate from dual@sdir.gemco.local which returned the SYSDATE OK.

I then removed the public link and re-ran the above which returned ORA-02019: connection description for remote database not found.

I dropped the CAL DB Link and recreated with username & passsword for CAL and the correct service_name. I re-ran the select script which returned the SYSDATE.

 
I then ran the Refresh group (still logged in as CAL) exec dbms_refresh.refresh('CAL_UPDATE') but it appears to hang and it creates no session on the main oracle server.

Any more ideas?

Regards,
Col

what is the wait event for the hung session?

Provided my own work around to the problem.
Root cause of the initials issue was never found.