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