ORA-01775 - looping chain of synonyms 1

[stephenwales]

Hello,

I've got a materialized view that's suddenly causing me grief with an ORA-01775 on the refresh.

The entry in USER_JOBS looks like this:

SQL> l
  1* select * from user_jobs where job = 5596
SQL> /

       JOB LOG_USER                       PRIV_USER
---------- ------------------------------ ------------------------------
SCHEMA_USER                    LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE
------------------------------ --------- -------- --------- -------- ---------
NEXT_SEC TOTAL_TIME B
-------- ---------- -
INTERVAL
--------------------------------------------------------------------------------
  FAILURES
----------
WHAT
--------------------------------------------------------------------------------
NLS_ENV
--------------------------------------------------------------------------------
MISC_ENV                                                           INSTANCE
---------------------------------------------------------------- ----------
      5596 ELLIPSE                        ELLIPSE
ELLIPSE                        05-APR-05 11:16:17                    01-JAN-00
00:00:00       1391 Y
sysdate + 1/48
        19
dbms_refresh.refresh('"ELLIPSE"."MSFEDT_PRIOR_YEAR"');
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC
Y='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LAN
GUAGE='AMERICAN' NLS_SORT='BINARY'
0102000200000000                                                          0


SQL>

There is one synonym in the target database for a user defining the table name MSFEDT_PRIOR_YEAR to reference:

SQL> select * from dba_synonyms where table_name = 'MSFEDT_PRIOR_YEAR';

OWNER                          SYNONYM_NAME
------------------------------ ------------------------------
TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
DB_LINK
--------------------------------------------------------------------------------
USERNAME                       MSFEDT_PRIOR_YEAR
ELLIPSE                        MSFEDT_PRIOR_YEAR

And in the source database there's also only the one synonym:

SQL> select * from dba_synonyms where table_name = 'MSFEDT_PRIOR_YEAR';

OWNER                          SYNONYM_NAME
------------------------------ ------------------------------
TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
DB_LINK
--------------------------------------------------------------------------------
USERNAME                       MSFEDT_PRIOR_YEAR
ELLIPSE                        MSFEDT_PRIOR_YEAR

My confusion level here is high. I found nothing useful on Metalink - anyone here able to point me to what I'm missing?

Thanks
Steve

 

[jquaid]

Hi Steve,

When I get this error, its usually caused by two things:
1. The table, function, package or procedure that the synonym refers to has been deleted.
2. The user that is attempting to access the table, func.., etc has had their privileges removed.
So, try
a. check that the view still exists
b. re-grant the user ALL rights to the view
c. try running the job directly in SQLPLUS:
set serveroutput on format wrapped size 1000000
exec dbms_job.run(5596);

Hope this helps,
Jeremy

 

[stephenwales]

Permissions had been dropped for some weird reason for the user in the db_link on the replication. Muchas gracias, have a star.