Home » RDBMS Server » Server Administration » materialized view error (Oracle Solaris 11.1.0.7)
materialized view error [message #538957] Tue, 10 January 2012 09:08 Go to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
I have a the following materialized view. When I try
running it I get an error that says 'SYS@OTAEAST' must
be declared. Can somebody tell me why this is trying to look
at SYS@OTAEAST based on my layout below




CREATE MATERIALIZED VIEW MTAS.OTAPA_CDMA_PARAMETERS (HOME_SID,FIRST_PAGING_CHANNEL,EXTENDED_ADDRESS,STATION_CLASS_MARK,
MOB_PROT_REVISION,IMSI_CLASS,ADDR_NUM,MOBILE_COUNTRY_CODE,IMSI_ELEVEN_TWELVE,
LOCAL_CONTROL,MOB_TERM_HOME,MOB_TERM_FOR_SID,MOB_TERM_FOR_NID,MAX_SID_NID)
TABLESPACE DE10M_1
PCTUSED    0
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
USING INDEX
            TABLESPACE IE10M_1
            PCTFREE    10
            INITRANS   2
            MAXTRANS   255
            STORAGE    (
                        INITIAL          10M
                        NEXT             10M
                        MINEXTENTS       1
                        MAXEXTENTS       UNLIMITED
                        PCTINCREASE      0
                        BUFFER_POOL      DEFAULT
                       )
REFRESH FORCE ON DEMAND
WITH ROWID
AS 
/* Formatted on 1/10/2012 9:43:23 AM (QP5 v5.185.11230.41888) */
SELECT HOME_SID,
       FIRST_PAGING_CHANNEL,
       EXTENDED_ADDRESS,
       STATION_CLASS_MARK,
       MOB_PROT_REVISION,
       IMSI_CLASS,
       ADDR_NUM,
       MOBILE_COUNTRY_CODE,
       IMSI_ELEVEN_TWELVE,
       LOCAL_CONTROL,
       MOB_TERM_HOME,
       MOB_TERM_FOR_SID,
       MOB_TERM_FOR_NID,
       MAX_SID_NID
  FROM CDMA_PARAMETERS@otaeast CDMA_PARAMETERS;


COMMENT ON MATERIALIZED VIEW MTAS.OTAPA_CDMA_PARAMETERS IS 'snapshot table for snapshot MTAS.OTAPA_CDMA_PARAMETERS';

-- Note: Index I_SNAP$_OTAPA_CDMA_PARAMET will be
--  created automatically 
--  by Oracle with the associated materialized view.

CREATE UNIQUE INDEX MTAS.PK_OTAPA_CDMA_PARAMETERS ON MTAS.OTAPA_CDMA_PARAMETERS
(HOME_SID)
LOGGING
TABLESPACE IE10M_1
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PROCEDURE MTAS.snap_refresh_single
(lv_snap_name  IN   Varchar2)
AS
errcd NUMBER;
BEGIN
       DBMS_OUTPUT.ENABLE(1000000);
      DBMS_OUTPUT.PUT_LINE('----------------------------------------');
      DBMS_SNAPSHOT.REFRESH('mtas'||'.'||lv_snap_name,'C');

 EXCEPTION
   WHEN NO_DATA_FOUND THEN
   errcd := SQLCODE;
   raise_application_error(-20107,'Failed Due To ' ||SQLERRM(errcd));
   raise_application_error(-20104,'Error In Refreshing Snapshots No Data Found');

   WHEN OTHERS THEN
   errcd := SQLCODE;
   raise_application_error(-20106,'Failed Due To ' ||SQLERRM(errcd));
   raise_application_error(-20105,'Error In Refreshing Snapshots');
END ;
/


Running like this:
==================
exec snap_refresh_single('OTAPA_CDMA_PARAMETERS');

Error
=====

BEGIN snap_refresh_single('OTAPA_CDMA_PARAMETERS'); END;
Error at line 1
ORA-20106: Failed Due To ORA-06550: line 1, column 9:
PLS-00352: Unable to access another database 'OTAEAST'
ORA-06550: line 1, column 9:
PLS-00201: identifier 'SYS@OTAEAST' must be declared
ORA-06550: line 1, column 9:
PL/SQL: Statement ignored
ORA-06512: at "MTAS.SNAP_REFRESH_SINGLE", line 18
ORA-06512: at line 1

CREATE DATABASE LINK OTATEST
 CONNECT TO MTAS
 IDENTIFIED BY <PWD>
 USING 'OTATEST';

[Updated on: Tue, 10 January 2012 09:11]

Report message to a moderator

Re: materialized view error [message #538964 is a reply to message #538957] Tue, 10 January 2012 09:28 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
Is the view and databaselink created in the schema SYS ? (as it is a private databaselink) If possible don't create objects in the sys-schema.
Re: materialized view error [message #539528 is a reply to message #538964] Mon, 16 January 2012 15:03 Go to previous message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
FYI, TNS service name was not pointing to anywhere valid.
I changed it and it worked.
Previous Topic: connect to newly created database
Next Topic: Index and Table rebuild method
Goto Forum:
  


Current Time: Thu Mar 28 15:51:39 CDT 2024