One one db, I have a table called xyz_rtab. The table structure doesn't really matter, it's just any table with a primary key. Then, I create a view on top of the table:
db2> create view xyz_v1 as select * from xyz_rtab;
View created.
Why do that? Well, my table is not really a table in my environment. The view on top of the table is really refactoring a business object that is used by several systems but whose time has come to change. But it doesn't matter, for the purposes of this demonstration.
Next, I change dbs and create the database link I'll use to get this data.
db1> create database link db2 using 'db2';
Database link created.
Now, I query across that dblink to verify that I can actually see the data:
db1> select count(*) from xyz_v1@db2;
COUNT(*)
----------
10
db1> select count(*) from xyz_rtab@db2;
COUNT(*)
----------
10
If I can query the data I should be able to create a mview, right? Lets try from the base table:
db1> create materialized view xyz_rtab_mview as select * from xyz_rtab@db2;
Materialized view created.
db1> exec dbms_mview.refresh(list=>'xyz_rtab_mview',method=>'C',atomic_refresh=>true);
PL/SQL procedure successfully completed.
No problem, that's what we expect. Now lets try from the view:
db1> create materialized view xyz_mv1 as select * from xyz_v1@db2;
create materialized view xyz_mv1 as select * from xyz_v1@db2
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 960
ORA-06512: at line 1
Nice.
13 comments:
I didn't get any similar error :
(10.2.0.1 on Windows)
SQL> drop materialized view mv_local;
drop materialized view mv_local
*
ERROR at line 1:
ORA-12003: materialized view "HEMANT"."MV_LOCAL" does not exist
SQL> create materialized view
2 mv_local
3 build deferred
4 REFRESH WITH ROWID
5 as SELECT * FROM TEST_TXN_TABLE ;
Materialized view created.
SQL>
SQL> drop materialized view mv_over_dblink;
drop materialized view mv_over_dblink
*
ERROR at line 1:
ORA-12003: materialized view "HEMANT"."MV_OVER_DBLINK" does not exist
SQL> create materialized view
2 mv_over_dblink
3 build deferred
4 refresh with rowid
5 as select * from test_txn_table@mydblink;
Materialized view created.
SQL>
21:59:27 SQL> exec dbms_mview.refresh('MV_LOCAL','C');
PL/SQL procedure successfully completed.
Elapsed: 00:00:26.14
21:59:53 SQL> exec dbms_mview.refresh('MV_OVER_DBLINK','C');
PL/SQL procedure successfully completed.
Elapsed: 00:01:50.37
22:01:43 SQL>
22:01:43 SQL> select count(*) from mv_local;
COUNT(*)
----------
154239
Elapsed: 00:00:13.46
22:01:57 SQL> select count(*) from mv_over_dblink;
COUNT (*)
----------
154239
Elapsed: 00:00:11.20
22:02:08 SQL>
Testing again with a View :
SQL>
SQL> rem alter session set events '10046 trace name context forever, level 8';
SQL>
SQL> select object_name, object_type from user_objects ;
OBJECT_NAME OBJECT_TYPE
------------------------- -------------------
GET_REDO_STATS PROCEDURE
MYSTAT TABLE
TEST_TXN_TBL_NDX INDEX
TEST_TXN_TABLE TABLE
V_TEST_TXN_TABLE_V VIEW
MYDBLINK.CHITALE DATABASE LINK
6 rows selected.
SQL>
SQL> drop view V_TEST_TXN_TABLE_V ;
View dropped.
SQL> create view V_TEST_TXN_TABLE_V as select * FROM TEST_TXN_TABLE ;
View created.
SQL>
SQL>
SQL> create materialized view
2 mv_local
3 build deferred
4 REFRESH WITH ROWID
5 as SELECT * FROM TEST_TXN_TABLE ;
Materialized view created.
SQL>
SQL>
SQL> create materialized view
2 mv_over_dblink
3 build deferred
4 refresh with rowid
5 as select * from test_txn_table@mydblink;
Materialized view created.
SQL>
SQL>
SQL> create materialized view
2 mv_on_view_local
3 build deferred
4 refresh with rowid
5 as select * from V_TEST_TXN_TABLE_V ;
Materialized view created.
SQL>
SQL>
SQL> create materialized view
2 mv_on_view_dblink
3 build deferred
4 refresh with rowid
5 as select * from V_TEST_TXN_TABLE_V@mydblink ;
Materialized view created.
SQL>
SQL> spool off
Similarly, the REFRESHs also succeeded.
Just browsing the internet, you have a very, very interesting blog.
What's the solution for this?
I am facing the same issue with Oracle 10g on AIX.
SQL> select count(*) from view1@db1;
select count(*) from view1@db1
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from db1
I need to select from a view through DBLINK
and the view internally is using another view.
Hi
We've seen the same issue, and I've yet to identify the cause. It will work fine in Dev, and then fail with exactly the same structures/users/etc. when we migrate to QA.
This evening our production batch decided to throw the same problem after operating normally for the past few months.
I've isolated the issue to those MVs based on views over tables. All other MVs based on the same DB Link accessing tables directly operate correctly.
So far our only solution is to replace all MVs with regular tables, using the SQL Copy From command to refresh them. This is now part of our Oracle application framework, so is no great design hardship. Performance is marginally slower, not enough to cause problems, but we'd rather have batch jobs that run a little slower than not at all....
Myk
According to metalink - it is a bug (5015547)in 10.2.0.2.
Fixed in 11.1.
A workaround they are suggesting is to "create the database link directly to the owner of the view".
Sorry, but the direct db link trick didn't work for me.
But "REFRESH COMPLETE WITH ROWID" - worked.
Hope this helps
Note that "ORA-12008: error in materialized view refresh path" is only the first error in refresh
In my case running the refresh as system the significant error was
ORA-00942: table or view does not exist
This error is not a bug but an improvement in security
From Toad
As dwstage (the owner of the mv) the refresh works
Time Start: 11/06/2009 10:42:38 AM
SQL> exec dbms_mview.refresh('dwstage.FACT_INS_MV_CLAIMSPROCESS')
PL/SQL procedure successfully completed.
Time End: 11/06/2009 10:58:46 AM
Elapsed Time for Script Execution: 16:07 mins
Error reproduced
As system user from Toad
- gets error as the grant on the component dwdev object (not ownned by dwstage) cannot be inherited
Time Start: 11/06/2009 11:03:33 AM
SQL> exec dbms_mview.refresh('dwstage.FACT_INS_MV_CLAIMSPROCESS')
BEGIN dbms_mview.refresh('dwstage.FACT_INS_MV_CLAIMSPROCESS'); END;
Error at line 2
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2545
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2751
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2720
ORA-06512: at line 1
Script Terminated on line 2.
Time End: 11/06/2009 11:05:06 AM
Elapsed Time for Script Execution: 01:32 min
Fix error by grant with admin option
Do grant to allow dwstage to pass on grant to whoever is running refresh
grant select on dwdev.dim_date to dwstage with grant option;
-- refresh now works as system (or other users)
Time Start: 11/06/2009 4:00:58 PM
SQL> exec dbms_mview.refresh('DWSTAGE.FACT_INS_MV_CLAIMSPROCESS')
PL/SQL procedure successfully completed.
Time End: 11/06/2009 4:16:05 PM
Elapsed Time for Script Execution: 15:06 mins
Instead of Trying as
create materialized view mv_over_dblink
build deferred
refresh with rowid
as select * from test_txn_table@mydblink;
Please try as
create materialized view
mv_over_dblink
build deferred
refresh with rowid
as select * from (select * from test_txn_table@mydblink);
I'm having same issue with our 10.2.0.3 databases.
In another place, I found solution similar to Ritu's comments such as create MV as follows
create materialized view ....
select * from (select * from yourview)
This solutions worked.
However, I'm trying to find the root cause for this? is this issue is with just 10.2.0.3 or any specific patch caused it?
If any one knew, post it here.
we have same issues on oracle 11.1.0.6 as well
Post a Comment