Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Posts
    77

    Unanswered: materialized views

    Hi guys, had a question on materialized views.

    I have trouble automatically replicating a materialized view.

    here is what I did

    First I created a materialized view log for the table in the master site. Then I created a data base link between the two databases.

    Then at the remote site I try to create the materialized view as follows

    create materialized view mv_students
    tablespace USERS
    refresh fast
    start with sysdate
    next sysdate + 10 /86400
    as
    select * from students@orcl;

    The matirialized view was created successfully but didn't replicate the changes to the materialized view.

    however when I issued the refresh command manually all the changes were upgraded

    execute dbms_mview.refresh('mv_students','f');

  2. #2
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204

    Re: materialized views

    Originally posted by vld
    Hi guys, had a question on materialized views.

    I have trouble automatically replicating a materialized view.

    here is what I did

    First I created a materialized view log for the table in the master site. Then I created a data base link between the two databases.

    Then at the remote site I try to create the materialized view as follows

    create materialized view mv_students
    tablespace USERS
    refresh fast
    start with sysdate
    next sysdate + 10 /86400
    as
    select * from students@orcl;

    The matirialized view was created successfully but didn't replicate the changes to the materialized view.

    however when I issued the refresh command manually all the changes were upgraded

    execute dbms_mview.refresh('mv_students','f');
    Look at dba_jobs and see if a refresh job was created. IF not, then create the job. If there is a job, then is JOB_QUEUE_PROCESSES set? This is needed to start the background process that runs the jobs in dba_jobs.

    HTH,
    Patrick

  3. #3
    Join Date
    Jan 2002
    Posts
    77
    Thanks for the reply patric, I checked the DBA_Jobs view, and the
    Next_day value was set to 01-Jan-00 but the last day value was set to
    29-Aug-03. I am not sure how that happened I am pretty sure I set the value of Next Day value to sysdate during materialized view creation.

    I am using Oracle 9i , and the job_queue_processes was set to a default value of 10. but when I issued following command

    ps -ef | grep snp

    I couldn't see any background processes, so I am not really sure , I am a bit confused about that as well. Any suggestions?

    Thanks.

Posting Permissions

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