Home » SQL & PL/SQL » SQL & PL/SQL » mview is not refreshing (oracle 11g , windows)
mview is not refreshing [message #556469] Mon, 04 June 2012 07:44 Go to next message
pradies
Messages: 238
Registered: May 2008
Senior Member
Hi,

I have a mview based on the database link. Mview created without the error and first time refrehment has been done properly in time, but after the first time it is not refreshing.


  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+(15/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;



18:07:58 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             04-jun-2012 17:47:14



I have set the refreshment time for mview for 15 min with the following command


alter MATERIALIZED VIEW SMSCHAT.TBL_VOICECHAT_NEW1  REFRESH FAST START WITH sysdate+0 NEXT sysdate+(15/86400*60);   




But now this mview is not refreshing and no jobs is showing in dba_jobs_running.


18:10:32 SQL> select LAST_DATE,LAST_SEC,THIS_DATE,THIS_SEC,NEXT_DATE,NEXT_SEC,TOTAL_TIME,BROKEN from dba_jobs where job=234;

LAST_DATE            LAST_SEC THIS_DATE            THIS_SEC NEXT_DATE            NEXT_SEC TOTAL_TIME B
-------------------- -------- -------------------- -------- -------------------- -------- ---------- -
04-jun-2012 17:47:13 17:47:13                               04-jun-2012 18:02:13 18:02:13         34 N

Elapsed: 00:00:00.29


Kindly help me to resolve this issue. Where to check this issue.


Thanks in Advance
Pradeep Sharma
Re: mview is not refreshing [message #556470 is a reply to message #556469] Mon, 04 June 2012 08:09 Go to previous messageGo to next message
Michel Cadot
Messages: 57605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post:
- Value of job_queue_processes parameter
- FAILURES column of DBA_JOBS
Have a look at alert.log for any error message related to jobs.

Regards
Michel
Re: mview is not refreshing [message #556882 is a reply to message #556470] Thu, 07 June 2012 02:49 Go to previous messageGo to next message
pradies
Messages: 238
Registered: May 2008
Senior Member
Hi,


last day I again recreated the mview and same problem appears again with the same code.

Current Situation is as follows

11:44:03 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             07-jun-2012 09:44:54

11:44:33 SQL> select * from dba_jobs_running;

       SID        JOB   FAILURES LAST_DATE            LAST_SEC THIS_DATE            THIS_SEC   INSTANCE
---------- ---------- ---------- -------------------- -------- -------------------- -------- ----------
      2893        252          0 07-jun-2012 09:44:51 09:44:51 07-jun-2012 11:14:06 11:14:06          0





SQL> SHOW PARAMETER JOB_QUEUE_PROCESS

NAME                                 TYPE
------------------------------------ ----------------------------
VALUE
------------------------------
job_queue_processes                  integer
1000


SQL> SELECT SCHEMA_USER, JOB, FAILURES FROM DBA_JOBS;

SCHEMA_USER                           JOB   FAILURES
------------------------------ ---------- ----------
WKSYS                                  27          0
WKSYS                                  26          0
SMSCHAT                               252          0
FLOWS_030000                         4001          0
FLOWS_030000                         4002          0
SYSMAN                                 30          0

6 rows selected.


Alert log file and associated incident details folder upload.



Please tell me what is the issue.
Re: mview is not refreshing [message #556883 is a reply to message #556882] Thu, 07 June 2012 02:54 Go to previous messageGo to next message
pradies
Messages: 238
Registered: May 2008
Senior Member
Alert log content

Thu Jun 07 08:35:13 2012
Thread 1 advanced to log sequence 26587
  Current log# 5 seq# 26587 mem# 0: E:\ORADATA\SMSCHAT\REDO05.LOG
Thu Jun 07 09:27:32 2012
Thread 1 advanced to log sequence 26588
  Current log# 6 seq# 26588 mem# 0: E:\ORADATA\SMSCHAT\REDO06.LOG
Thu Jun 07 09:58:49 2012
Thread 1 advanced to log sequence 26589
  Current log# 7 seq# 26589 mem# 0: E:\ORADATA\SMSCHAT\REDO07.LOG
Thu Jun 07 10:33:37 2012
Thread 1 advanced to log sequence 26590
  Current log# 4 seq# 26590 mem# 0: E:\ORADATA\SMSCHAT\REDO04.LOG
Thu Jun 07 11:05:40 2012
Thread 1 advanced to log sequence 26591
  Current log# 5 seq# 26591 mem# 0: E:\ORADATA\SMSCHAT\REDO05.LOG
Thu Jun 07 11:24:47 2012
Process W000 died, see its trace file
Thu Jun 07 11:27:59 2012
Thread 1 advanced to log sequence 26592
  Current log# 6 seq# 26592 mem# 0: E:\ORADATA\SMSCHAT\REDO06.LOG
Thu Jun 07 11:47:38 2012
Thread 1 advanced to log sequence 26593
  Current log# 7 seq# 26593 mem# 0: E:\ORADATA\SMSCHAT\REDO07.LOG
Thu Jun 07 12:10:45 2012
Thread 1 advanced to log sequence 26594
  Current log# 4 seq# 26594 mem# 0: E:\ORADATA\SMSCHAT\REDO04.LOG
Thu Jun 07 12:33:19 2012
Thread 1 advanced to log sequence 26595
  Current log# 5 seq# 26595 mem# 0: E:\ORADATA\SMSCHAT\REDO05.LOG
Thu Jun 07 12:47:42 2012
Thread 1 advanced to log sequence 26596
  Current log# 6 seq# 26596 mem# 0: E:\ORADATA\SMSCHAT\REDO06.LOG
Thu Jun 07 12:56:13 2012
Thread 1 advanced to log sequence 26597
  Current log# 7 seq# 26597 mem# 0: E:\ORADATA\SMSCHAT\REDO07.LOG
Thu Jun 07 13:01:30 2012
Immediate Kill Session#: 2893, Serial#: 55036
Immediate Kill Session: sess: 00000001ACEB4EE8  OS pid: 7600
Thu Jun 07 13:03:29 2012
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x75520BE, kgegec()+76]
Errors in file d:\app\administrator\diag\rdbms\vxmldb\vxmldb\trace\vxmldb_j002_1976.trc  (incident=8853367):
ORA-07445: exception encountered: core dump [kgegec()+76] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x75520BE] [UNABLE_TO_READ] []
ORA-00604: error occurred at recursive SQL level 1
ORA-02049: timeout: distributed transaction waiting for lock
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2537
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2743
ORA-06512: at "SYS.DBMS_IREFRESH", line 685
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
Incident details in: d:\app\administrator\diag\rdbms\vxmldb\vxmldb\incident\incdir_8853367\vxmldb_j002_1976_i8853367.trc
Thu Jun 07 13:05:16 2012
Trace dumping is performing id=[cdmp_20120607130516]
Thu Jun 07 13:07:24 2012
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x75520BE, kgegec()+76]
Errors in file d:\app\administrator\diag\rdbms\vxmldb\vxmldb\trace\vxmldb_j000_5872.trc  (incident=8854415):
ORA-07445: exception encountered: core dump [kgegec()+76] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x75520BE] [UNABLE_TO_READ] []
ORA-00604: error occurred at recursive SQL level 1
ORA-02049: timeout: distributed transaction waiting for lock
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2537
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2743
ORA-06512: at "SYS.DBMS_IREFRESH", line 685
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
Incident details in: d:\app\administrator\diag\rdbms\vxmldb\vxmldb\incident\incdir_8854415\vxmldb_j000_5872_i8854415.trc
Thu Jun 07 13:07:25 2012
Trace dumping is performing id=[cdmp_20120607130725]
Thu Jun 07 13:08:25 2012
Sweep Incident[8854415]: completed
Thu Jun 07 13:08:38 2012
Sweep Incident[8853367]: completed
Thu Jun 07 13:11:03 2012
Thread 1 advanced to log sequence 26598
  Current log# 4 seq# 26598 mem# 0: E:\ORADATA\SMSCHAT\REDO04.LOG



I am trying to upload incident details file folder but not able to upload.

Tell me how can we upload this.

Thanks

Pradeep
Re: mview is not refreshing [message #556888 is a reply to message #556883] Thu, 07 June 2012 03:24 Go to previous messageGo to next message
Michel Cadot
Messages: 57605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink and/or call Oracle support

You can also read this article: Troubleshooting Internal Errors.

Regards
Michel
Re: mview is not refreshing [message #556915 is a reply to message #556888] Thu, 07 June 2012 05:01 Go to previous messageGo to next message
pradies
Messages: 238
Registered: May 2008
Senior Member
Hi Michal,

Thanks for the reply.

after googling I found the metalink id 742764.1 is realted to this error.

[Edit MC: Metalink note content removed]

So Every time if we get this error then we have to recreate mview again from scratch.

OR Is there any other solution for this.

Pradeep

[Updated on: Thu, 07 June 2012 06:02] by Moderator

Report message to a moderator

Re: mview is not refreshing [message #556923 is a reply to message #556915] Thu, 07 June 2012 05:38 Go to previous messageGo to next message
Michel Cadot
Messages: 57605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please do not post content of Metalink notes, they are materials protected by copyright.
Only Oracle can answer you to this question, but if the note does not mention other workaround there are few chances there is one.

Regards
Michel

[Edit: english]

[Updated on: Thu, 07 June 2012 06:02]

Report message to a moderator

Re: mview is not refreshing [message #556924 is a reply to message #556923] Thu, 07 June 2012 05:44 Go to previous messageGo to next message
pradies
Messages: 238
Registered: May 2008
Senior Member
Hi Michal,

Sorry for the same content uploading in this site. I was not aware of this. In future I'll take care of this.


Thanks Again for your great Help.

Pradeep
Re: mview is not refreshing [message #556942 is a reply to message #556924] Thu, 07 June 2012 07:41 Go to previous messageGo to next message
pradies
Messages: 238
Registered: May 2008
Senior Member
Hi Michal,

As written above, there is a MATERIALIZEDview log created on AFCCV.TBL_VOICECHAT with the following command and the log table name is "MLOG$_TBL_VOICECHAT"

create MATERIALIZED view log on afccv.tbl_voicechat;


row count on this table increasing frequently as update is running.

So please tell me few things

1. Is there any issue with these number of rows getting increase.
2. If yes, Can we purge this log?
3. Log is getting purged automatically or we have to purge it manually??


Thanks

Pradeep

Re: mview is not refreshing [message #556950 is a reply to message #556942] Thu, 07 June 2012 08:40 Go to previous messageGo to next message
Michel Cadot
Messages: 57605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The purge is automatic when all remote sites have refreshed their mviews.

Regards
Michel
Re: mview is not refreshing [message #572344 is a reply to message #556950] Tue, 11 December 2012 01:17 Go to previous messageGo to next message
pradies
Messages: 238
Registered: May 2008
Senior Member
Hi,

I am having a metalized view on a single table.

Please tell me one thing what problem I can face if I will create multiple metalized view on a single table??


Thanks and Regards

Pradeep Sharma
Re: mview is not refreshing [message #572347 is a reply to message #572344] Tue, 11 December 2012 01:35 Go to previous message
Michel Cadot
Messages: 57605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No particular ones.

Regards
Michel
Previous Topic: CLOB TO BLOB UPDATION
Next Topic: Min and Max Rates with Corresponding Dates
Goto Forum:
  


Current Time: Thu Apr 17 06:26:06 CDT 2014

Total time taken to generate the page: 0.17009 seconds