|If you ever tried to refresh materialized view and got an error like this: |
then it's probably because your schema was created by exporting and importing data from schema with another name. If you examine the content of a schema dump file like this:
then you may see undocumented arguments in commands that create materialized views:
The string "DB.GLOBAL.NAME" is obviously a global database name where you exported schema from. It is followed by a few digits and a name of the exported schema. There is also a lot of other stuff I have no idea about, but that probably does not matter.
The point is that if you import a dump file into schema with different name the "MYSCHEMA" argument will not be changed. So when you later try to refresh that materialized view Oracle will search for tables in schema "MYSCHEMA" instead of the current schema. And this is how we can get "ORA-00942: table or view does not exist" error.
So to fix that we should find where that "MYSCHEMA" is stored in the database and change it to the name of the schema the data was imported into. It turns out that it is stored in two tables: "SYS.SNAP$" and "SYS.SNAP_REFTIME$". Basically you need to connect to your database as SYSDBA and execute the following queries:
Of course you should examine contents of those tables before executing these queries in case there is a legitimate reason for some materialized views to have differences in those values.
Disclaimer: I have no responsibility for this advice. If you crashed a production database executing code you found on a fan site you have no one to blame but yourself.
|Category: Charlie's blog | Views: 5490 ||
|Total comments: 1|