This Error Appear when you try to create MATERIALIZED VIEW On remote View
First you have to know it
Bug 5583712 ORA-942 on create materialized view on remote view
| Product (Component) |
Oracle Server (Rdbms) |
| Range of versions believed to be affected |
Versions < 11 |
| Versions confirmed as being affected |
|
| Platforms affected |
Generic (all / most platforms affected |
You can check Metalink Doc : 5583712.8
But I would Share this solution All you Have to do is follow the below steps :
CREATE MATERIALIZED VIEW RHC
BUILD IMMEDIATE
USING INDEX
REFRESH COMPLETE
ON DEMAND START WITH SYSDATE+1 NEXT
SYSDATE + 8/24
AS SELECT * FROM vw_RHC@remotedb;
ORA-00942: TABLE OR VIEW does NOT exist
ORA-06512: AT “SYS.DBMS_SNAPSHOT_UTL”, line 960
ORA-06512: AT line 1
The Solution is :
CREATE MATERIALIZED VIEW RHC
BUILD IMMEDIATE
USING INDEX
REFRESH COMPLETE
ON DEMAND START WITH SYSDATE+1 NEXT
SYSDATE + 8/24
AS AS SELECT * FROM (SELECT * FROM vw_employees@remotedb);
Materialized VIEW created.
Thank you
Osama mustafa
Like this:
Like Loading...
Related
Published by Osama Mustafa
Osama considered as one of the leaders in Cloud technology, DevOps and database in the Middle-East. I have more than ten years of experience within the industry. moreover, certfied 4x AWS , 4x Azure and 6x OCI, have also obtained database certifications for multiple providers.
In addition to having experience with Oracle database and Oracle products, such as middle-ware, OID, OAM and OIM, I have gained substantial knowledge with different databases.
Currently, I am architecting and implementing Cloud and DevOps. On top of that, I'm providing solutions for companies that allow them to implement the solutions and to follow the best practices.
View all posts by Osama Mustafa
Technology Geek 
Grazie OSAMA very very thankyou 🙂
LikeLike
Fast, simple… thnx
LikeLike
Welcome
LikeLike
we hit this bug in prod but not other environments, so very thank you for the workaround.
LikeLike
Your Welcome
LikeLike
U really rocks…
LikeLike
Ditto Anonymous' comment from 1/31/14. Funny how it likes production environments best.
LikeLike
My trial to resolve [ORA-06512: at “SYS.DBMS_SNAPSHOT_UTL”, line 960] issue with [SELECT * FROM (SELECT * FROM vw_employees@remotedb);] did not work. Reason unknown to me then I tried the same approach in different way.1. Created a view for remote DB in local DB.Create View MyView AS SELECT * FROM vw_employees@remotedb;2. Created materialized view out of local view.Create Materialized View MyMV AS Select * from MyView;In essence the same solution in different approach. It might had been my local DB is 12c and Remote DB is in 10g.Thanks for the solution.
LikeLike