Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-01775: looping chain of synonyms

Quanwen ZhaoJul 15 2019 — edited Jul 23 2019

Hey my guys and friends ,

Today my oracle developer colleague ask me about when creating a new table (of course also insert some data) on grantor schema "SZD_BBS_V2" whether or not she would query out that table on grantee schema "QWZ". A few days ago I once created a view "usr_tables" with select table_name, num_rows, partitioned from all_tables where owner = 'SZD_BBS_V2' on schema "SZD_BBS_V2" and also granted some select privilege to a new role.

So my first thought is that I should create a materialized view for fast refresh data. My SQL code is as follows,

SET long     10000

SET linesize 300

SET pagesize 0

SET echo      OFF

SET feedback  OFF

SET heading   OFF

SET termout   OFF

SET verify    OFF

SET trimout   ON

SET trimspool ON

PROMPT =========================

PROMPT Executing on "SYS" schema

PROMPT =========================

DROP USER qwz;

CREATE USER qwz IDENTIFIED BY qwz;

GRANT connect, resource TO qwz;

GRANT create public synonym TO szd_bbs_v2;

GRANT drop public synonym TO szd_bbs_v2;

GRANT create materialized view TO szd_bbs_v2;

GRANT drop any materialized view TO szd_bbs_v2;

GRANT on commit refresh TO szd_bbs_v2;

CREATE ROLE bbs;

PROMPT ================================

PROMPT Executing on "SZD_BBS_V2" schema

PROMPT ================================

-- switching to specific schema "szd_bbs_v2", BTW I use Oracle SEPS (Security External Pasword Store) to achieve the intention

-- saving password of schema "szd_bbs_v2".

CONN /@szd_bbs_v2;

DROP MATERIALIZED VIEW u_tables;

CREATE MATERIALIZED VIEW u_tables

BUILD IMMEDIATE

DISABLE QUERY REWRITE

REFRESH FAST

AS

  SELECT table_name

         , num_rows

         , partitioned

  FROM all_tables

  WHERE owner = 'SZD_BBS_V2'

  ORDER BY table_name

;

GRANT SELECT ON u_tables TO bbs;

CREATE PUBLIC SYNONYM u_tables FOR u_tables;

SPOOL gen_bgs_role_syn_tab_2.sql

SELECT 'GRANT SELECT ON '

       || table_name

       || ' TO bbs;'

FROM user_tables

ORDER BY table_name

/

SELECT 'CREATE PUBLIC SYNONYM '

       || table_name

       || ' FOR '

       || table_name

       || ';'

FROM user_tables

ORDER BY table_name

/

SPOOL off

@gen_bgs_role_syn_tab_2.sql;

PROMPT =========================

PROMPT Executing on "SYS" schema

PROMPT =========================

CONN / as sysdba;

GRANT bbs TO qwz;

At this moment I run this SQL statement "select * from u_tables;" and encounter this weird error on schema "QWZ",

QWZ@xxxx> select * from u_tables;

select * from u_tables

              *

ERROR at line 1:

ORA-01775: looping chain of synonyms

Afterwards I eliminate those keywords "BUILD IMMEDIATE, DISABLE QUERY REWRITE and REFRESH FAST" on creating materialized view, it's okay and only become a common materialized view (BTW it still couldn't refresh data fast so it makes no sense to using materialized view).

Could you assist me to solve my confusion? Very appreciated if any help!

Best Regards

Quanwen Zhao

This post has been answered by Zlatko Sirotic on Jul 17 2019
Jump to Answer

Comments

Post Details

Added on Jul 15 2019
26 comments
13,845 views