Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> STALE Materialized view
raju p
post Jul 14 2011, 06:22 AM
Post #1


Member
**

Group: Members
Posts: 16
Joined: 4-May 11
Member No.: 45,124



Hi All,
i have created FAST refresh materialized view.it is eligible for FAST refresh because when i executed SELECT * FROM USER_MVIEWS i could see FAST_REFRESHABLE=DIRLOAD_DML and STALENESS=STALE and it is not getting refreshed(through DBA_JOBS).Could you tell me the reason why it is in STALE status and how to resolve it. and one of my materialized views is in NEEDS_COMPILE status, Could you tell me the reason why it is and how to resolve it.Thanks in advance.
Go to the top of the page
 
+Quote Post
merrillcd_mem
post Jul 14 2011, 06:44 AM
Post #2


Member
**

Group: Members
Posts: 29
Joined: 30-June 11
From: Memphis TN
Member No.: 45,469



What happens if you manully run the refresh?

Did you alter the master table? That may be the reason for the status change.
Go to the top of the page
 
+Quote Post
HAL9000
post Jul 14 2011, 02:54 PM
Post #3


Advanced Member
***

Group: Members
Posts: 880
Joined: 25-September 07
Member No.: 12,336



"Could you tell me the reason why it is in STALE status and how to resolve it. "

Probably because the refresh is not refresh fast with "on commit", and the MV is truly stale:

The column value does not lie, see here:

http://dba-oracle.com/googlesearchsite_pro...2&sa=Search

Go to the top of the page
 
+Quote Post
raju p
post Jul 15 2011, 01:42 AM
Post #4


Member
**

Group: Members
Posts: 16
Joined: 4-May 11
Member No.: 45,124



QUOTE (HAL9000 @ Jul 14 2011, 02:54 PM) *
"Could you tell me the reason why it is in STALE status and how to resolve it. "

Probably because the refresh is not refresh fast with "on commit", and the MV is truly stale:

The column value does not lie, see here:

http://dba-oracle.com/googlesearchsite_pro...2&sa=Search

Actually the same code i have executed in 2 instances in 1 instance it is working fine(STALENESS=FRESH) but in another instance it is not getting refreshed(STALENESS=STALE).
Could you tell me why it is like this and how to resolve it.
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 25th October 2014 - 10:15 AM