Thanks for the question, Vicente.
Asked: September 15, 2017 - 10:55 am UTC
Last updated: July 30, 2019 - 3:11 am UTC
Version: Oracle 11.2.0.4.0
Viewed 1000+ times
You Asked
Hi there.
I have created in the database A the following MV:
CREATE MATERIALIZED VIEW MRW_USER.MB_TRACK_REPORT3_FINAL_SS (SOUNDREC_CODE,ISRC,CAT_NUM,TITLE,MIX_NAME, ARTIST,RELEASE_DATE,LABEL,COMPANY,CORPORATE_GROUP, DISTRIBUTOR,DEALER_PRICE,GENRE,SUB_GENRE,DURATION, PRIMARY_FLAG,DELETION_FLAG,PHYSICAL_BARCODE,INTERNAL_ID) TABLESPACE RMAESTRO_DATA NOCACHE NOLOGGING NOCOMPRESS BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND WITH PRIMARY KEY AS
SELECT * FROM mb_track_report3_ss
/
COMMENT ON MATERIALIZED VIEW MRW_USER.MB_TRACK_REPORT3_FINAL_SS IS 'snapshot table for snapshot MRW_USER.MB_TRACK_REPORT3_FINAL'
/
GRANT SELECT ON MRW_USER.MB_TRACK_REPORT3_FINAL_SS TO READ_ROLE
/
Then in the database B I have a database link to the database A called UKSRV. I need to refresh the MV on database A from database B in a stored procedure. When I run the following:
DBMS_MVIEW.REFRESH( 'MRW_USER.MB_TRACK_REPORT3_FINAL_SS@UKSRV');
Oracles says is completed but when I check in database A if the MV has been refreshed it looks like it isn't because the last refreshing date is from July. What I'm doing wrong. I can't use a job in database A to refresh the MV because I need to refresh it from a stored procedure in database B and do more things with those data afterwards.
I'll really appreciate your help.
Thank you so much for you support.
Kind regards.
Vinny.
and Connor said...
Here's an example for you:
--
-- remote database
--
SQL> create table t as select * from dba_objects;
Table created.
SQL>
SQL> create materialized view t_mv refresh complete on demand
2 as select owner, count(*) c from t group by owner;
Materialized view created.
SQL> delete from t where owner = 'MCDONAC';
23 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from t_mv where owner = 'MCDONAC';
OWNER C
------------------------------ ----------
MCDONAC 23
So you can see that T_MV is out of date and needs to be refreshed. I now go to another database and try refresh it remotely
--
-- local database
--
SQL> exec dbms_mview.refresh('MCDONAC.T_MV@db121')
BEGIN dbms_mview.refresh('MCDONAC.T_MV@db121'); END;
*
ERROR at line 1:
ORA-20000: ORA-00979: illegal reference to remote database
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994
ORA-06512: at line 1
SQL> exec dbms_mview.refresh('MCDONAC.T_MV')@db121
BEGIN dbms_mview.refresh('MCDONAC.T_MV')@db121; END;
*
ERROR at line 1:
ORA-06550: line 1, column 41:
PLS-00103: Encountered the symbol "@" when expecting one of the following:
:= . ( % ;
The symbol ":= was inserted before "@" to continue.
SQL> exec dbms_mview.refresh@db121('MCDONAC.T_MV')
PL/SQL procedure successfully completed.
and we can see that last call was the one that worked
--
-- remote database
--
SQL> select * from t_mv where owner = 'MCDONAC';
no rows selected
Rating
(3 ratings)
Is this answer out of date? If it is, please let us know via a Comment