OraFAQ Forum: SQL & PL/SQL » Materialized View Refresh (merged)

Home » SQL & PL/SQL » SQL & PL/SQL » Materialized View Refresh (merged) (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Materialized View Refresh (merged) [message #542443]

Tue, 07 February 2012 05:54

mvmkandan
Messages: 68
Registered: May 2010
Location: Trivendrum

Member

I have created a Table with one column in user 'Test' Database 'Test'. Then i have created a Materialized view based on the Table in 'TestMV' in same database 'Test'.

Its working fine. But after adding one column in the table, if i go to refresh the Materialized view the following Error has occured.

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00913: too many values
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 2
**

Kindly Give your views to resolve this issue.

Thanks in Advance
Veera

Report message to a moderator

Re: Materialized View Refresh [message #542446 is a reply to message #542443]

Tue, 07 February 2012 06:00

Michel Cadot
Messages: 68773
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Recreate the MVIEW.

Regards
Michel

Report message to a moderator

Re: Materialized View Refresh [message #542447 is a reply to message #542443]

Tue, 07 February 2012 06:01

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I bet you created a materialized view using a '*':

CREATE MATERIALIZED VIEW mv_whatever AS SELECT * FROM ...

You should have put some more effort and actually typed column list in there:

SELECT col1, col2, ...

If you did that, you wouldn't have that problem.

What to do? Recreate the materialized view so that it reflects a new table outlook.

Report message to a moderator

Re: Materialized View Refresh [message #542449 is a reply to message #542443]

Tue, 07 February 2012 06:05

arup_kc
Messages: 9
Registered: January 2012
Location: India

Junior Member

better you re create the materialized view once again and then test.

Report message to a moderator

Re: Materialized View Refresh [message #542452 is a reply to message #542449]

Tue, 07 February 2012 06:27

mvmkandan
Messages: 68
Registered: May 2010
Location: Trivendrum

Member

Hi LittleFoot,
I got it.

Thanks
Veera

Report message to a moderator

Refresh View [message #542453 is a reply to message #542443]

Tue, 07 February 2012 06:29

mvmkandan
Messages: 68
Registered: May 2010
Location: Trivendrum

Member

Hi,
I have created a table with 2 columns and i have created the view as follows

'Create view testview as select * from testtable'

view is created successfully. After adding one column in the testtable, the view will not get added the newly created column.

If i ve to refresh or recreate it.

Thanks in advance

Veera

Report message to a moderator

Re: Refresh View [message #542454 is a reply to message #542453]

Tue, 07 February 2012 06:34

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

View is just a stored query. Why do you think that queries should modify themselves?

Report message to a moderator

Re: Refresh View [message #542455 is a reply to message #542454]

Tue, 07 February 2012 06:35

mvmkandan
Messages: 68
Registered: May 2010
Location: Trivendrum

Member

In that cases i have to create the view to add the newly added column?

Becoz i ve created an view as 'Select * from '.

Thanks
Veera

Report message to a moderator

Re: Refresh View [message #542456 is a reply to message #542455]

Tue, 07 February 2012 06:38

Michel Cadot
Messages: 68773
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and do not use IM/SMS speak.

select text from user_views where view_name = '<your view>;

Then do you understand why?

Regards
Michel

[Updated on: Wed, 08 February 2012 04:09]

Report message to a moderator

Re: Refresh View [message #542458 is a reply to message #542455]

Tue, 07 February 2012 06:40

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

It doesn't matter; as I said, a view is a stored query. SELECT statement looks the way as you wrote it when you created a view:

select * from some_table

which was evaluated to

select col1, col2 from some_table

Now you altered a table and added another column. View source is still the same:

select col1, col2 from some_table

So what do you think, what do you have to do?

Report message to a moderator

Re: Refresh View [message #542616 is a reply to message #542458]

Wed, 08 February 2012 03:55

mvmkandan
Messages: 68
Registered: May 2010
Location: Trivendrum

Member

Ya Little

Its Correct, I understood
Thanks
Veera

Report message to a moderator

Previous Topic:	SQL PIVOT
Next Topic:	Split Gender to Two Column (merged 2)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 26 08:49:49 CST 2025