Registration | Login

Upcoming Projects

Firefly Online
To be announced


Peter Panzerfaust Motion Comic
To be announced

Upcoming Events

Planet Comicon Kansas City
April 28-30, 2017

MCM Hannover Comic Con
May 20-21, 2017

MCM London Comic Con
May 26-28, 2017

MCM Belfast Comic Con
June 24-25, 2017

MCM Ireland Comic Con
July 1-2, 2017

The Happy Birthday thread
By chrisdvanne

The OFFICIAL Summer Glau Appreciation Page!
By Admirator

Summer Glau at Rose City Comic Con 2016
By chrisdvanne

The official funny thread
By chrisdvanne

What was the best Cameron fighting scene in TSCC?
By chrisdvanne

Submit a News or Article
By KevinInEngland

Summer Glau at Dallas Comic Con 2014
By chrisdvanne

Summer Glau at Planet Comicon Kansas City 2017
By chrisdvanne

Summer Glau at Comic Con Russia 2015
By chrisdvanne

River Tam Action Figures
By chrisdvanne




New comments

Admirator on: The Serenity cast hopes to make a trilogy in unearthed 2004 video interviews


chrisdvanne on: The Serenity cast hopes to make a trilogy in unearthed 2004 video interviews


chrisdvanne on: River Tam figure in the next Firefly Loot Crate!


chrisdvanne on: Have a question for Summer? Here's your chance to ask it!


William on: Have a question for Summer? Here's your chance to ask it!


chrisdvanne on: Have a question for Summer? Here's your chance to ask it!


resurgance2001 on: Have a question for Summer? Here's your chance to ask it!


dmitriy on: Have a question for Summer? Here's your chance to ask it!


chrisdvanne on: Have a question for Summer? Here's your chance to ask it!


Eric Hammers on: Have a question for Summer? Here's your chance to ask it!


New videos

00:05:50

00:05:42

00:01:20


latest images

ORA-00942 when calling dbms_mview.refresh in schema cloned by exp/imp

POSTED BY Charley_Dixon ON Jul 20, 2011 / 1 COMMENTS


If you ever tried to refresh materialized view and got an error like this:
Code
BEGIN dbms_mview.refresh('MYMVIEW'); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431
ORA-06512: at line 1

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:
Code
$ exp myschema@$ORACLE_SID file=myschema rows=n statistics=none
$ strings myschema.dmp|less

then you may see undocumented arguments in commands that create materialized views:
Code
CREATE SNAPSHOT "MYMVIEW" USING ("MYMVIEW", (8, 'DB.GLOBAL.NAME', 1, 0, 0, "MYSCHEMA", ...

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:
Code
update SYS.SNAP$ set MOWNER=SOWNER where MOWNER<>SOWNER;
update SYS.SNAP_REFTIME$ set MOWNER=SOWNER where MOWNER<>SOWNER;
commit;

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.



Like our Facebook fan page below to stay connected to the latest Summer Glau news!

Total comments: 1

  Message #1 | Leaidan | 2011 Nov 24, 00:03 GMT



Wow, that's a really clever way of tnhiikng about it!



 
Name *:
Email:
Code *: