sorry for the delay.
1) Master: Oracle 11g SE; Snapshot: Oracle XE 11g;; DB-Links on Snapshot site: public links for SYS, public and private links for Materialized view admin, propagator, table owner and user schema
CREATE SNAPSHOT DRAWIN TABLESPACE MDS_1M_D
SITE_ID, MACHINE_CUST_ID, MACHINE_ID,
ID, SECTION, SHEET,
DRAWINGNAME, DRAWING, CREATED_BY,
DATE_CREATED, MODIFIED_BY, DATE_MODIFIED,
FROM "MDS_ADMIN".DRAWIN@TMDS.HMB.HMBNET drawin
where confidential = 0 and
exists (select 0 from moempcus@TMDS.HMB.HMBNET moempcus where drawin.machine_CUST_ID=moempcus.cust_id and moempcus.hgremp_id = &2);
CREATE TABLE DRAWIN
SITE_ID NUMBER(20) NOT NULL,
MACHINE_CUST_ID NUMBER(20) NOT NULL,
MACHINE_ID NUMBER(20) NOT NULL,
ID NUMBER(20) NOT NULL,
SECTION VARCHAR2(1 BYTE) NOT NULL,
SHEET NUMBER(2) NOT NULL,
DRAWINGNAME VARCHAR2(20 BYTE) NOT NULL,
CREATED_BY VARCHAR2(30 BYTE) NOT NULL,
DATE_CREATED DATE NOT NULL,
MODIFIED_BY VARCHAR2(30 BYTE),
CONFIDENTIAL NUMBER(1) NOT NULL
CREATE TABLE MOEMPCUS
HGREMP_ID NUMBER(20) NOT NULL,
CUST_ID NUMBER(20) NOT NULL,
SITE_ID NUMBER(20) NOT NULL,
COUNTR_ISO_CODE VARCHAR2(3 BYTE) NOT NULL
Thanks for your efforts.
Log to snapshot site under schema that is owner of MV DRAWIN and run a SELECT you use to define MV.
SELECT SITE_ID, MACHINE_CUST_ID, MACHINE_ID, ID, SECTION, SHEET, DRAWINGNAME, DRAWING, CREATED_BY, DATE_CREATED, MODIFIED_BY, DATE_MODIFIED, CONFIDENTIAL FROM "MDS_ADMIN".DRAWIN@TMDS.HMB.HMBNET drawin where confidential = 0 and exists (select 0 from moempcus@TMDS.HMB.HMBNET moempcus where drawin.machine_CUST_ID=moempcus.cust_id and moempcus.hgremp_id = &2);
If this SQL work then problem is probably in DB link definition. When job is runned than background proccess is used and if in DB link definition is not used schema to log to master site then actual user credential are used.
G. Krieger wrote:
dbms_mview.refresh works when I omit the blob item. When I add the blob item it gives the above mentioned error in the alert.log.
ORA-12048: error encountered while refreshing materialized view "MDS_ADMIN"."DRAWIN"
ORA-04054: database link XEKRIEGE.HMB.HMBNET does not exist
ORA-02063: preceding line from TMDS.HMB.HMBNET
For me this errro say that on master site (DB link TMDS.HMB.HMBNET is pointing to) is missing DB link with name XEKRIEGE.HMB.HMBNET.
That's reason why I ask for DLL for master site objects. You suplied DDL statement for table. No view or MV using DB link XEKRIEGE.HMB.HMBNET.
I could suggest only create on master site simple table with primary key (number type) and BLOB column. Than on snapshot site create simple MV based on that table a try job that refresh this MV.
If this will work then porblem must be somwhere in objects used for you problematic MV. You need to check if you don't miss something.
What is DLL statement? I know DDL, DML DCL.....
Ok, after some reading and testing I did the following:
- rename the snapshot db from XEKRIEGE to XEKRIEGE.HMB.HMBNET
- create public database link on master db XEKRIEGE.HMB.HMBNET using the MV owner login
This works as long as the client computer is within our LAN. When I refresh from outside I get ORA-121790 TNS Connection timeout. That's clear, the master cannot resolve the client's name used in the db link.
Here's the code for the public db link:
CREATE PUBLIC DATABASE LINK "XEKRIEGE.HMB.HMBNET"
CONNECT TO <MV owner>
IDENTIFIED BY <PWD>
USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<MV client computer>)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)))';