Home » RDBMS Server » Server Administration » Mview is not refreshing (Oracle 11.1, Windows 2003 Server)
Mview is not refreshing [message #522874] Wed, 14 September 2011 05:30 Go to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
Hi,

I have create a mview using a dblink with the refresh difference of every 10min. Suddenly I check the mview refresh date and time it was not getting refreshed automatically at the time interval specified.
drop MATERIALIZED view log on afccv.tbl_voicechat;

drop MATERIALIZED VIEW SMSCHAT.TBL_VOICECHAT_NEW1 ;

 CREATE MATERIALIZED VIEW SMSCHAT.TBL_VOICECHAT_NEW1
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE SMSCHAT 
  BUILD IMMEDIATE
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE SMSCHAT 
  REFRESH FAST ON DEMAND START WITH sysdate+0 NEXT sysdate+10/86400
  WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
  AS SELECT TBL_VOICECHAT.ANI ANI,TBL_VOICECHAT.CHAT_ID CHAT_ID,TBL_VOICECHAT.GENDER GENDER,
  TBL_VOICECHAT.AGE AGE,TBL_VOICECHAT.RECORD_FILE RECORD_FILE,TBL_VOICECHAT.SUB_DATE_TIME SUB_DATE_TIME,
  TBL_VOICECHAT.STATUS STATUS,TBL_VOICECHAT.LOGIN_STATUS LOGIN_STATUS,TBL_VOICECHAT.BILLING_DATE BILLING_DATE,
  TBL_VOICECHAT.CIRCLE_NAME CIRCLE_NAME,TBL_VOICECHAT.INTEREST INTEREST,TBL_VOICECHAT.HOBBIES HOBBIES,
  TBL_VOICECHAT.LANGUAGE LANGUAGE,TBL_VOICECHAT.OLD_ID OLD_ID,TBL_VOICECHAT.SUB_FILE SUB_FILE,
  TBL_VOICECHAT.M_ACT M_ACT,TBL_VOICECHAT.LAST_CALL_DATE LAST_CALL_DATE,TBL_VOICECHAT.FLAG FLAG,
  TBL_VOICECHAT.SUNSIGN SUNSIGN,TBL_VOICECHAT.PSUNSIGN PSUNSIGN,TBL_VOICECHAT.BAL_STATE BAL_STATE,
  TBL_VOICECHAT.TOTALHIT TOTALHIT,TBL_VOICECHAT.NEXT_BILLED_DATE NEXT_BILLED_DATE,
TBL_VOICECHAT.LAST_BILLED_DATE LAST_BILLED_DATE,
  TBL_VOICECHAT.IS_PREPAID IS_PREPAID,TBL_VOICECHAT.DNDFLAG DNDFLAG,TBL_VOICECHAT.AMOUNT AMOUNT,
TBL_VOICECHAT.VALUE1 VALUE1 
  FROM AFCCV.TBL_VOICECHAT@SERVICEDB1 TBL_VOICECHAT;



I am trying to find out what is the cause of not refreshing and also googled for the same. got some link also either not linked to same problem or not cleared.. Where should i checked regarding the error cause.


Thanks

Pradeep

[Updated on: Sat, 24 September 2011 02:23] by Moderator

Report message to a moderator

Re: Mview is not refreshing [message #522881 is a reply to message #522874] Wed, 14 September 2011 05:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59165
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
REFRESH FAST ON DEMAND

You didn't ask to refresh every 10 minutes but on demand.

Regards
Michel

[Updated on: Wed, 14 September 2011 05:53]

Report message to a moderator

Re: Mview is not refreshing [message #522887 is a reply to message #522881] Wed, 14 September 2011 06:21 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Not true in 11g:

From the docs
You cannot specify both ON COMMIT and ON DEMAND. START WITH and NEXT take precedence over ON DEMAND. Therefore, in most circumstances it is not meaningful to specify ON DEMAND when you have specified START WITH or NEXT.

In earlier versions I believe the above syntax would be invalid.
Re: Mview is not refreshing [message #522892 is a reply to message #522887] Wed, 14 September 2011 06:51 Go to previous messageGo to next message
Michel Cadot
Messages: 59165
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Gee! stopped to scan the line when encountered "ON DEMAND".
Uselful info to know, I bet we will see errors in the futur delivered scripts.

Regards
Michel
Re: Mview is not refreshing [message #522893 is a reply to message #522881] Wed, 14 September 2011 06:51 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
Hi Michal,

Thanks for your response. As now I created this mview and it is in use currently. So I can't drop and recreate it.

So I am using the below command for alteration.
alter MATERIALIZED VIEW "SMSCHAT"."TBL_VOICECHAT_NEW1"  
REFRESH FASt START WITH sysdate+0 NEXT sysdate+10/86400

Output of the command is
SQL> alter MATERIALIZED VIEW "SMSCHAT"."TBL_VOICECHAT_NEW1"  REFRESH FASt START
WITH sysdate+0 NEXT sysdate+10/86400;

Materialized view altered.

SQL> 


After modification if I am checking the DDL for the meterlized view is the same as previous

SQL> l
  1  SELECT dbms_metadata.get_ddl(replace(OBJECT_TYPE, ' ', '_'), OBJECT_NAME,OW
NER)
  2  FROM DBA_OBJECTS
  3  WHERE OBJECT_TYPE in ('MATERIALIZED VIEW')
  4* AND OWNER = 'SMSCHAT'
SQL> /

DBMS_METADATA.GET_DDL(REPLACE(OBJECT_TYPE,'','_'),OBJECT_NAME,OWNER)
--------------------------------------------------------------------------------


  CREATE MATERIALIZED VIEW "SMSCHAT"."TBL_VOICECHAT_NEW1"
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOL

OGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SMSCHAT"
  BUILD IMMEDIATE
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SMSCHAT"
  REFRESH FAST ON DEMAND START WITH sysdate+0 NEXT sysdate+10/86400
  WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
  AS SELECT TBL_VOICECHAT.ANI ANI,TBL_VOICECHAT.CHAT_ID CHAT_ID,TBL_VOICECHAT.GE

NDER GENDER,
  TBL_VOICECHAT.AGE AGE,TBL_VOICECHAT.RECORD_FILE RECORD_FILE,TBL_VOICECHAT.SUB_

DATE_TIME SUB_DATE_TIME,
  TBL_VOICECHAT.STATUS STATUS,TBL_VOICECHAT.LOGIN_STATUS LOGIN_STATUS,TBL_VOICEC

HAT.BILLING_DATE BILLING_DATE,
  TBL_VOICECHAT.CIRCLE_NAME CIRCLE_NAME,TBL_VOICECHAT.INTEREST INTEREST,TBL_VOIC

ECHAT.HOBBIES HOBBIES,
  TBL_VOICECHAT.LANGUAGE LANGUAGE,TBL_VOICECHAT.OLD_ID OLD_ID,TBL_VOICECHAT.SUB_

FILE SUB_FILE,
  TBL_VOICECHAT.M_ACT M_ACT,TBL_VOICECHAT.LAST_CALL_DATE LAST_CALL_DATE,TBL_VOIC

ECHAT.FLAG FLAG,
  TBL_VOICECHAT.SUNSIGN SUNSIGN,TBL_VOICECHAT.PSUNSIGN PSUNSIGN,TBL_VOICECHAT.BA

L_STATE BAL_STATE,
  TBL_VOICECHAT.TOTALHIT TOTALHIT,TBL_VOICECHAT.NEXT_BILLED_DATE NEXT_BILLED_DAT

E,TBL_VOICECHAT.LAST_BILLED_DATE LAST_BILLED_DATE,
  TBL_VOICECHAT.IS_PREPAID IS_PREPAID,TBL_VOICECHAT.DNDFLAG DNDFLAG,TBL_VOICECHA

T.AMOUNT AMOUNT,TBL_VOICECHAT.VALUE1 VALUE1
  FROM AFCCV.TBL_VOICECHAT@SERVICEDB1 TBL_VOICECHAT



SQL>


also I checked that this mview refreshing properply til the time for created. for your information its created in 12:30PM approx.


Regards

Pradeep S

[Updated on: Sat, 24 September 2011 02:26] by Moderator

Report message to a moderator

Re: Mview is not refreshing [message #522895 is a reply to message #522893] Wed, 14 September 2011 06:56 Go to previous messageGo to next message
Michel Cadot
Messages: 59165
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is job_queue_processes set to a value > 0?

Regards
Michel
Re: Mview is not refreshing [message #522896 is a reply to message #522895] Wed, 14 September 2011 06:58 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
Hi Michal,

Yes Job_Queue_process parameter is greter then 0.

SQL> show parameter job_queue

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
job_queue_processes                  integer
1000
SQL>



Regards

Pradeep
Re: Mview is not refreshing [message #522934 is a reply to message #522896] Wed, 14 September 2011 10:17 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
Hi Michal/cookiemaster,

Please tell if there is any wrong. what else I should check. and where I can get the error for refreshment. Rest i will revert back if there is any issue in mview refreshment.

Re: Mview is not refreshing [message #522939 is a reply to message #522934] Wed, 14 September 2011 10:58 Go to previous messageGo to next message
Michel Cadot
Messages: 59165
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can have a look at alert.log file to see if there is any error.
YOu can also have a look at DBA_JOBS:
select job, failures, broken from dba_jobs where lower(what) like '%refresh%';

Regards
Michel
Re: Mview is not refreshing [message #524337 is a reply to message #522895] Fri, 23 September 2011 02:29 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
Hi,

As mentioned below Today Again the Mview not refershed after 8:28AM.

Below is the output of command.
SQL> select mview_name,last_refresh_date from dba_mviews;

MVIEW_NAME                     LAST_REFRESH_DATE
------------------------------ --------------------
MGMT_ECM_MD_ALL_TBL_COLUMNS    20-jan-2011 11:44:40
TBL_VOICECHAT_NEW1             23-sep-2011 08:28:19

SQL>    select job, failures, broken from dba_jobs where lower(what) like '%refr
esh%';

       JOB   FAILURES B
---------- ---------- -
        90          0 N

SQL>

alert log content at that time when it was last refreshed Where the mview exist i.e. vxmldb

Fri Sep 23 07:13:04 2011
Thread 1 advanced to log sequence 14149
  Current log# 7 seq# 14149 mem# 0: E:\ORADATA\SMSCHAT\REDO07.LOG
Fri Sep 23 07:39:22 2011
Thread 1 advanced to log sequence 14150
  Current log# 4 seq# 14150 mem# 0: E:\ORADATA\SMSCHAT\REDO04.LOG
Fri Sep 23 08:04:34 2011
Thread 1 advanced to log sequence 14151
  Current log# 5 seq# 14151 mem# 0: E:\ORADATA\SMSCHAT\REDO05.LOG
Fri Sep 23 08:27:27 2011
Thread 1 advanced to log sequence 14152
  Current log# 6 seq# 14152 mem# 0: E:\ORADATA\SMSCHAT\REDO06.LOG
Fri Sep 23 08:54:11 2011
Thread 1 advanced to log sequence 14153
  Current log# 7 seq# 14153 mem# 0: E:\ORADATA\SMSCHAT\REDO07.LOG
Fri Sep 23 09:14:38 2011
Thread 1 advanced to log sequence 14154
  Current log# 4 seq# 14154 mem# 0: E:\ORADATA\SMSCHAT\REDO04.LOG
Fri Sep 23 09:44:36 2011
Thread 1 advanced to log sequence 14155
  Current log# 5 seq# 14155 mem# 0: E:\ORADATA\SMSCHAT\REDO05.LOG
Fri Sep 23 10:06:50 2011
Thread 1 advanced to log sequence 14156
  Current log# 6 seq# 14156 mem# 0: E:\ORADATA\SMSCHAT\REDO06.LOG
Fri Sep 23 10:24:41 2011
Thread 1 advanced to log sequence 14157
  Current log# 7 seq# 14157 mem# 0: E:\ORADATA\SMSCHAT\REDO07.LOG
Fri Sep 23 10:46:44 2011
Thread 1 advanced to log sequence 14158
  Current log# 4 seq# 14158 mem# 0: E:\ORADATA\SMSCHAT\REDO04.LOG
Fri Sep 23 11:06:13 2011
Thread 1 advanced to log sequence 14159
  Current log# 5 seq# 14159 mem# 0: E:\ORADATA\SMSCHAT\REDO05.LOG
Fri Sep 23 11:24:23 2011
Thread 1 advanced to log sequence 14160
  Current log# 6 seq# 14160 mem# 0: E:\ORADATA\SMSCHAT\REDO06.LOG
Fri Sep 23 11:46:05 2011
Thread 1 advanced to log sequence 14161
  Current log# 7 seq# 14161 mem# 0: E:\ORADATA\SMSCHAT\REDO07.LOG
Fri Sep 23 12:03:31 2011
Thread 1 advanced to log sequence 14162
  Current log# 4 seq# 14162 mem# 0: E:\ORADATA\SMSCHAT\REDO04.LOG
Fri Sep 23 12:18:00 2011
Thread 1 advanced to log sequence 14163
  Current log# 5 seq# 14163 mem# 0: E:\ORADATA\SMSCHAT\REDO05.LOG
Fri Sep 23 12:34:32 2011
Thread 1 advanced to log sequence 14164
  Current log# 6 seq# 14164 mem# 0: E:\ORADATA\SMSCHAT\REDO06.LOG
Fri Sep 23 12:50:24 2011
Thread 1 advanced to log sequence 14165
  Current log# 7 seq# 14165 mem# 0: E:\ORADATA\SMSCHAT\REDO07.LOG




Kindly tell me why this view not getting refreshed as per the schedule time. or how to check what error coming at that time and where I should look for the error.



Regards

Pradeep
Re: Mview is not refreshing [message #524338 is a reply to message #524337] Fri, 23 September 2011 02:35 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
Hi,

One More thing I checked in the Alert log file creating on source table database name from where the mview is getting refreshed.

below is the Alert log file content for that time when the mview is running on destination server.

Fri Sep 23 07:35:47 2011
Thread 1 advanced to log sequence 76794
  Current log# 6 seq# 76794 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO06.LOG
Fri Sep 23 07:58:54 2011
Thread 1 advanced to log sequence 76795
  Current log# 4 seq# 76795 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO04.LOG
Fri Sep 23 08:23:11 2011
Thread 1 advanced to log sequence 76796
  Current log# 5 seq# 76796 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO05.LOG
Fri Sep 23 08:31:03 2011
Thread 1 advanced to log sequence 76797
  Current log# 6 seq# 76797 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO06.LOG
Fri Sep 23 08:53:59 2011
Thread 1 advanced to log sequence 76798
  Current log# 4 seq# 76798 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO04.LOG
Fri Sep 23 09:14:37 2011
Thread 1 advanced to log sequence 76799
  Current log# 5 seq# 76799 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO05.LOG
Fri Sep 23 09:41:47 2011
Thread 1 advanced to log sequence 76800
  Current log# 6 seq# 76800 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO06.LOG
Fri Sep 23 10:11:07 2011


***********************************************************************

Fatal NI connect error 12170.

  VERSION INFORMATION:
	TNS for 64-bit Windows: Version 11.1.0.6.0 - Production
	Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.1.0.6.0 - Production
	Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.1.0.6.0 - Production
  Time: 23-SEP-2011 10:11:07
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    
TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505
    
TNS-00505: Operation timed out
    nt secondary err code: 60
    nt OS err code: 0
  Client address: <unknown>
Fri Sep 23 10:15:41 2011
Thread 1 advanced to log sequence 76801
  Current log# 4 seq# 76801 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO04.LOG
Fri Sep 23 10:45:34 2011
Thread 1 advanced to log sequence 76802
  Current log# 5 seq# 76802 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO05.LOG
Fri Sep 23 11:10:03 2011
Thread 1 advanced to log sequence 76803
  Current log# 6 seq# 76803 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO06.LOG
Fri Sep 23 11:22:38 2011
Thread 1 advanced to log sequence 76804
  Current log# 4 seq# 76804 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO04.LOG
Fri Sep 23 11:35:04 2011
Thread 1 advanced to log sequence 76805
  Current log# 5 seq# 76805 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO05.LOG
Fri Sep 23 11:50:19 2011
Thread 1 advanced to log sequence 76806
  Current log# 6 seq# 76806 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO06.LOG
Fri Sep 23 12:08:11 2011
Thread 1 advanced to log sequence 76807
  Current log# 4 seq# 76807 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO04.LOG
Fri Sep 23 12:24:49 2011


***********************************************************************

Fatal NI connect error 12170.

  VERSION INFORMATION:
	TNS for 64-bit Windows: Version 11.1.0.6.0 - Production
	Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.1.0.6.0 - Production
	Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.1.0.6.0 - Production
  Time: 23-SEP-2011 12:24:49
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    
TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505
    
TNS-00505: Operation timed out
    nt secondary err code: 60
    nt OS err code: 0
  Client address: <unknown>
Fri Sep 23 12:26:01 2011
Thread 1 advanced to log sequence 76808
  Current log# 5 seq# 76808 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO05.LOG
Fri Sep 23 12:41:20 2011
Thread 1 advanced to log sequence 76809
  Current log# 6 seq# 76809 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO06.LOG
Fri Sep 23 12:59:02 2011
Thread 1 advanced to log sequence 76810
  Current log# 4 seq# 76810 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO04.LOG



Check this and kindly suggest what should i do for the problem resolving.

Regards

Pradeep
Re: Mview is not refreshing [message #524340 is a reply to message #524338] Fri, 23 September 2011 03:12 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
Hi Michal/CookieMaster,

where I ran this query now then it is showing the below output.

  1* select mview_name,last_refresh_date from dba_mviews
SQL> /

MVIEW_NAME                     LAST_REFRESH_DATE
------------------------------ --------------------
MGMT_ECM_MD_ALL_TBL_COLUMNS    20-jan-2011 11:44:40
TBL_VOICECHAT_NEW1             23-sep-2011 11:10:41



So I am confuse why this is showing now "23-sep-2011 11:10:41" because in previously it was showing "23-sep-2011 08:28:19"

Regards

Pradeep
Re: Mview is not refreshing [message #524343 is a reply to message #524340] Fri, 23 September 2011 03:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59165
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because, more likely, because it has executed between the 2.

Regards
Michel
Re: Mview is not refreshing [message #524344 is a reply to message #524343] Fri, 23 September 2011 03:17 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
Hi Michal,

not able to understand with "executed between the 2". kindly clear.


Regards

Pradeep S
Re: Mview is not refreshing [message #524348 is a reply to message #524344] Fri, 23 September 2011 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 59165
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It has been executed between "23-sep-2011 08:28:19" and "23-sep-2011 11:10:41".
Or if you prefer, it has been executed at "23-sep-2011 08:28:19" and "23-sep-2011 11:10:41".

Regards
Michel

[Updated on: Fri, 23 September 2011 03:49]

Report message to a moderator

Re: Mview is not refreshing [message #524355 is a reply to message #524348] Fri, 23 September 2011 04:23 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
Hi Michal,

Thanks for clearing but want to know why this is taking so much time. As I also checked something


SQL> l
  1  select LAST_DATE,LAST_SEC,THIS_DATE,THIS_SEC,NEXT_DATE,NEXT_SEC,TOTAL_TIME,BROKEN from dba_jobs
  2* where job=90
SQL> /

LAST_DATE            LAST_SEC THIS_DATE            THIS_SEC NEXT_DATE            NEXT_SEC TOTAL_TIME B                  
-------------------- -------- -------------------- -------- -------------------- -------- ---------- -                 
23-sep-2011 11:10:36 11:10:36 23-sep-2011 13:36:09 13:36:09 23-sep-2011 13:36:08 13:36:08     470489 N                  

SQL> spool off


LAST_DATE   - 
      Date that this job last successfully executed
   LAST_SEC
      Same as LAST_DATE. This is when the last successful execution started.
   THIS_DATE
      Date that this job started executing (usually null if not executing)
   THIS_SEC
      Same as THIS_DATE. This is when the last successful execution started.
   NEXT_DATE
      Date that this job will next be executed
   NEXT_SEC
      Same as NEXT_DATE. The job becomes due for execution at this time.
   TOTAL_TIME
      Total wallclock time spent by the system on this job,in seconds
   BROKEN
      If Y,no attempt is being made to run this job. See dbms_jobq.broken(job).



According to NEXT_DATE this job should executed on specified time and mview should be get refreshed with in 10 min... but now there is 3:00PM and value is not getting changed.

[Updated on: Sat, 24 September 2011 02:28] by Moderator

Report message to a moderator

Re: Mview is not refreshing [message #524361 is a reply to message #524355] Fri, 23 September 2011 04:51 Go to previous messageGo to next message
John Watson
Messages: 4576
Registered: January 2010
Location: Global Village
Senior Member
Your code schedules the refresh every 10 seconds, not every ten minutes. Are you sure the job isn't running right now? Does it take a long time to run?

select * from dba_jobs_running;

[update: typo]

[Updated on: Fri, 23 September 2011 04:51]

Report message to a moderator

Re: Mview is not refreshing [message #524365 is a reply to message #524361] Fri, 23 September 2011 05:27 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
Hi John,

Jobs is still running.

and altered the mview using the below command for increasing refreshment type.

alter MATERIALIZED VIEW "SMSCHAT"."TBL_VOICECHAT_NEW1" REFRESH FASt START WITH sysdate+0 NEXT sysdate+(10/86400*60)

But this command seems like hang state.

suggest what to do ... kill the job first ...or any thing else.


Pradeep
Re: Mview is not refreshing [message #524374 is a reply to message #524355] Fri, 23 September 2011 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 59165
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Puyt the following in your login.sql and glogin.sql

set trimout on trimspool on


Regards
Michel

Re: Mview is not refreshing [message #524375 is a reply to message #524365] Fri, 23 September 2011 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59165
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, kill the job.

Regards
Michel
Re: Mview is not refreshing [message #524460 is a reply to message #522874] Fri, 23 September 2011 23:53 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
Hi Michal,

Last Evening I again recreated the mview and checked for refreshment of the mview. today morning When I checked the mview refreshment time then it is showing "24-sep-2011 00:36:35". My Current Mview Script is

  CREATE MATERIALIZED VIEW "SMSCHAT"."TBL_VOICECHAT_NEW1"
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SMSCHAT" 
  BUILD IMMEDIATE
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SMSCHAT" 
  REFRESH FAST ON DEMAND START WITH sysdate+0 NEXT sysdate+(10/86400*60)
  WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
  AS SELECT "TBL_VOICECHAT"."ANI" "ANI","TBL_VOICECHAT"."CHAT_ID" "CHAT_ID",
"TBL_VOICECHAT"."GENDER" "GENDER",
  "TBL_VOICECHAT"."AGE" "AGE","TBL_VOICECHAT"."RECORD_FILE" "RECORD_FILE",
"TBL_VOICECHAT"."SUB_DATE_TIME" "SUB_DATE_TIME",
  "TBL_VOICECHAT"."STATUS" "STATUS","TBL_VOICECHAT"."LOGIN_STATUS" "LOGIN_STATUS",
"TBL_VOICECHAT"."BILLING_DATE" "BILLING_DATE",
  "TBL_VOICECHAT"."CIRCLE_NAME" "CIRCLE_NAME","TBL_VOICECHAT"."INTEREST" "INTEREST",
"TBL_VOICECHAT"."HOBBIES" "HOBBIES",
  "TBL_VOICECHAT"."LANGUAGE" "LANGUAGE","TBL_VOICECHAT"."OLD_ID" "OLD_ID",
"TBL_VOICECHAT"."SUB_FILE" "SUB_FILE",
  "TBL_VOICECHAT"."M_ACT" "M_ACT","TBL_VOICECHAT"."LAST_CALL_DATE" "LAST_CALL_DATE",
"TBL_VOICECHAT"."FLAG" "FLAG",
  "TBL_VOICECHAT"."SUNSIGN" "SUNSIGN","TBL_VOICECHAT"."PSUNSIGN" "PSUNSIGN",
"TBL_VOICECHAT"."BAL_STATE" "BAL_STATE",
  "TBL_VOICECHAT"."TOTALHIT" "TOTALHIT","TBL_VOICECHAT"."NEXT_BILLED_DATE" 
"NEXT_BILLED_DATE","TBL_VOICECHAT"."LAST_BILLED_DATE" "LAST_BILLED_DATE",
  "TBL_VOICECHAT"."IS_PREPAID" "IS_PREPAID","TBL_VOICECHAT"."DNDFLAG" "DNDFLAG",
"TBL_VOICECHAT"."AMOUNT" "AMOUNT","TBL_VOICECHAT"."VALUE1" "VALUE1"
  FROM "AFCCV"."TBL_VOICECHAT"@SERVICEDB1 "TBL_VOICECHAT";
 


Mview jobs is running

SQL> alter session set nls_date_format='DD-mon-yyyy hh24:mi:ss';

Session altered.

SQL> select mview_name,last_refresh_date from dba_mviews;

MVIEW_NAME                     LAST_REFRESH_DATE
------------------------------ --------------------
TBL_VOICECHAT_NEW1             24-sep-2011 00:36:35


  1* select job, failures, broken from dba_jobs where lower(what) like '%refresh
%'
SQL> /

       JOB   FAILURES B
---------- ---------- -
       110          0 N


SQL> l
  1* select * from dba_jobs_running
SQL> /

       SID        JOB   FAILURES LAST_DATE            LAST_SEC THIS_DATE            THIS_SEC   INSTANCE
---------- ---------- ---------- -------------------- -------- -------------------- -------- ----------
      2512        110          0 24-sep-2011 00:36:29 00:36:29 24-sep-2011 05:18:49 05:18:49          0

SQL> SELECT t.used_ublk, t.used_urec
  2  FROM v$session s, v$transaction t
  3  WHERE s.taddr=t.addr
  4  and s.SID =2512;

 USED_UBLK  USED_UREC
---------- ----------
     11162     446368

SQL>


I am not able to understand why this is happening..... first few hours it is running fine after that this looks like a hang stage.


REgards

Pradeep

[Updated on: Sat, 24 September 2011 02:29] by Moderator

Report message to a moderator

Re: Mview is not refreshing [message #524476 is a reply to message #524460] Sat, 24 September 2011 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 59165
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I bet this is because the refresh takes almost 5 hours.

Regards
Michel
Re: Mview is not refreshing [message #524481 is a reply to message #524476] Sat, 24 September 2011 01:16 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
Hi Michal,

According to your reply. When the When I created this mview this takes hardly 10 min to complete. after that I checked the refeshment time till one 1-2 hour it was showing the correct time and refreshing properly.

So How the refreshment time now increased upto 5 hours....

Little bit confusing... can you please tel me something more in this.



Regards

Pradeep S

Re: Mview is not refreshing [message #524483 is a reply to message #524481] Sat, 24 September 2011 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 59165
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The fast refreshment depends on how many modifications have been made between 2 refreshments.
As you can see in your last query, till the point you executed it, the refreshment reported 446368 modifications.

Regards
Michel
Re: Mview is not refreshing [message #524487 is a reply to message #524483] Sat, 24 September 2011 01:38 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
Hi Michal,

Then why mview is refreshing properly previously when it was created and it was refreshing in 10 min approx..


Thanx and Regards

Pradeep
Re: Mview is not refreshing [message #524491 is a reply to message #524487] Sat, 24 September 2011 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 59165
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can't say more.
If you think there is a bug as there are less modifications in the remote table than what the mview refresh
do then you have to call Oracle support.

Regards
Michel

[Updated on: Sat, 24 September 2011 01:45]

Report message to a moderator

Re: Mview is not refreshing [message #524495 is a reply to message #524487] Sat, 24 September 2011 02:05 Go to previous messageGo to next message
John Watson
Messages: 4576
Registered: January 2010
Location: Global Village
Senior Member
I'm sure Michel is right.
Do you know how to check the number of updates made to a table? If you do that regularly, you might find a sudden burst of activity which causes this workload. One way is to query dba_tab_modifications, to get up-to-date figures you have to execute dbms_Stats.flush_database_monitoring_info first. The view is cleared when the table is analyzed.
Applying changes to an MV can be quite slow, do you have any indexes on the container table?
Re: Mview is not refreshing [message #524497 is a reply to message #524495] Sat, 24 September 2011 02:40 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
SQL> connect afccv/**********
Connected.
SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

  1  select table_name,inserts,updates,deletes
  2  from user_tab_modifications
  3* where table_name='TBL_VOICECHAT'
SQL> /

TABLE_NAME                        INSERTS    UPDATES    DELETES
------------------------------ ---------- ---------- ----------
TBL_VOICECHAT                       33737     248849      27329

SQL>


Source table and destination mview having indexes


SQL> select index_name,index_type from  user_indexes where table_name='TBL_VOICECHAT' ;

INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
IDX_STATUS_VOICECHAT           NORMAL
IDX_GENDER_VOICECHAT           NORMAL
IDX_ANI_VOICECHA2              NORMAL
IDX_ANI_VOICECHAT              NORMAL
IDX_M_ACT                      NORMAL

SQL>

Destination mview server ...

SQL> select index_name from dba_indexes where owner='SMSCHAT' and table_name='TBL_VOICECHAT_NEW1';

INDEX_NAME
------------------------------
IDX_GNELOGAG_VOICENEW1
IDX_LOGIN_STATUS_NEW
SYS_C0034163
IDX_GENDER1
IDX_CIRCLE_NEW1
IDX_CHAT_ID1

6 rows selected.


Re: Mview is not refreshing [message #524499 is a reply to message #524497] Sat, 24 September 2011 02:59 Go to previous messageGo to next message
John Watson
Messages: 4576
Registered: January 2010
Location: Global Village
Senior Member
So there has been a lot of DML on your source table. Can you check out the execution plans for the updates and delets on the MV container table? Perhaps your indexes aren't being used. Have you analyzed it recently?
(and by the way, if something I say is helpful, please let me know rather than just posting something without a "thank you")
Re: Mview is not refreshing [message #524500 is a reply to message #524499] Sat, 24 September 2011 03:10 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
Hi John,

Table has been analyzd today morning....

SQL> select table_name,last_analyzed from user_tables where table_name='TBL_VOICECHAT';

TABLE_NAME                     LAST_ANALYZED
------------------------------ --------------------
TBL_VOICECHAT                  24-sep-2011 07:55:53

SQL>


And mostly I am reverting back with Thanks. if sometimes I forgot then I apologies next time I'll take care of this.

Thanks




Re: Mview is not refreshing [message #524501 is a reply to message #524500] Sat, 24 September 2011 03:15 Go to previous messageGo to next message
John Watson
Messages: 4576
Registered: January 2010
Location: Global Village
Senior Member
Yes, but when was the MV container table analyzed, nit jsut te source table? What I'm trying to get at is whether the CBO could be choosing not to use indexes when locating rows for update or delete because of bad statistics.
Re: Mview is not refreshing [message #524502 is a reply to message #524501] Sat, 24 September 2011 03:21 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
Hi John,
What do you mean by MV container Table. Are you saying about TBL_VOICECHAT_NEW1 ?? kindly clear....


Thanks
Re: Mview is not refreshing [message #524503 is a reply to message #524502] Sat, 24 September 2011 03:31 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
Hi John,

As I checked MV Container Table has not been refreshed from the time created.

select object_name,object_type,created from dba_objects where object_name='TBL_VOICECHAT_NEW1';

OBJECT_NAME		OBJECT_TYPE		CREATED
------------------	------------------	-------------------
TBL_VOICECHAT_NEW1	MATERIALIZED VIEW	23-SEP-011 16:44:40
TBL_VOICECHAT_NEW1	TABLE			23-SEP-011 16:37:53

select owner,table_name,last_analyzed from dba_tables
where table_name like 'TBL_VOICECHAT_NEW1';

OWNER		TABLE_NAME		LAST_ANALYZED
-------		------------------	-------------
SMSCHAT		TBL_VOICECHAT_NEW1	



Kindly check


thanks
Re: Mview is not refreshing [message #524504 is a reply to message #524503] Sat, 24 September 2011 03:35 Go to previous message
John Watson
Messages: 4576
Registered: January 2010
Location: Global Village
Senior Member
Better run dbms_stats against it now! No promises, but the CBO will be relying on stats gathered by optimizer_dynamic_sampling, which will have been done the first time the MV was refreshed. This might slow things down. And then I think you really need to see the exec plans being used to refresh it.
I'm off now, good luck.
Previous Topic: standby database
Next Topic: System TableSpace is so big
Goto Forum:
  


Current Time: Mon Sep 22 11:31:52 CDT 2014

Total time taken to generate the page: 0.11410 seconds