Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2008
    Posts
    16

    Unanswered: Problem regarding Materialized View

    Hi,

    I have a master table "nms_num_schm" on SID-SLBANCS and its corresponding materialized view "mlog$_nms_num_schm". The MV is supposed to move data from this master table to a table "nms_num_schm_mv" on SID-ORCL.
    The MV is set to select data after every 5 mins and the db link is also set. also the grants have also been provided.

    I inserted the data in master table and data populated in the mlog table.But the problem is that its staying in mlog table and not moved to the "_mv" table even after 1 hr.Even though snaptime is populated with a time of refresh,the refresh didnt happen.

    So 3 things here -
    1) Why did the refresh didnt happen
    2) If the refresh didnt happen because of any issue/error, where can I check what was the issue.
    3) To make things worse, there are 17 more tables such similar setup. Some data refresh works sometimes, sometimes not.

    Please provide your valuable comments to resolve the same.


    Here's a snapshot of the mlog table -

    insert into MLOG$_NMS_NUM_SCHM (NMS_SEC_ID, NMS_NMBRNG_SCHM, SNAPTIME$$, DMLTYPE$$, OLD_NEW$$, CHANGE_VECTOR$$)
    values ('ZA00ISCDWD', 1, to_date('05-11-2008 11:17:07', 'dd-mm-yyyy hh24:mi:ss'), 'I', 'N', 'FEFF');

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    there should be jobs in the job queue for the mat view refreshes ... Do you have job_queue_processes set to 0 ???

    On some of your mat views you might could use "fast" refresh instead of on demand ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •