Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: Problem with MATERIALIZED VIEW (snapshot)

    Hi,
    I've any problem with creating MATERIALIZED VIEW (snapshot)

    My table is ROOMS:

    SQL> DESC ROOMS
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    LS_ID VARCHAR2(32)
    BL_ID NOT NULL VARCHAR2(32) PRIMARY KEY1
    FL_ID NOT NULL VARCHAR2(4) PRIMARY KEY2
    RM_ID NOT NULL VARCHAR2(8) PRIMARY KEY3
    SITE_ID VARCHAR2(32)


    SQL> SELECT COUNT(*)
    2 FROM ROOMS;

    COUNT(*)
    ----------
    203973

    SQL> SELECT COUNT (TOT)
    2 FROM (SELECT COUNT(*) TOT, LS_ID, SITE_ID
    3 FROM ROOMS
    4 GROUP BY LS_ID, SITE_ID);

    COUNT(TOT)
    ----------
    11673

    I'd like to create one MATERIALIZED VIEW that refresh every 30 seconds when I insert, update or delete on ROOMS table.

    I tried this:


    CREATE MATERIALIZED VIEW ROOMS_SNAP
    BUILD IMMEDIATE
    REFRESH complete
    START WITH to_date(sysdate,'dd/mm/yyyy hh24:mi:ss')
    NEXT sysdate + 30/86400
    disable QUERY REWRITE
    AS
    SELECT LS_ID, SITE_ID
    FROM ROOMS
    GROUP BY LS_ID, SITE_ID

    but when I insert, update or delete one record on ROOMS table, ROOMS_SNAP not refresh.

    What I wrong?
    How Can I write MATERIALIZED VIEW to maintain synchronization
    between ROOMS_SNAP AND LS_ID, SITE_ID by ROOMS every 30 seconds?

    Thanks

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Do you have a job in the job queue for this? What is the setting for
    job_queue_processes

    HTH
    Gregg

  3. #3
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    When you create a MV, a refresh group with a child is created.
    I prefer to handle that refresh group manually.

    Why don't you try with:
    Code:
    CREATE MATERIALIZED VIEW ROOMS_SNAP
    AS
     SELECT LS_ID, SITE_ID
       FROM ROOMS
      GROUP BY LS_ID, SITE_ID
    /
    
    BEGIN
      DBMS_REFRESH.MAKE ( name => 'REFRESH_ROOMS'
                        , list => NULL
                        , next_date => SYSDATE
                        , interval => 'sysdate + 30/86400' );
    
      DBMS_REFRESH.ADD  ( name => 'REFRESH_ROOMS'
                        , list => 'ROOMS_SNAP' );
    END;
    /
    Saludos!

  4. #4
    Join Date
    Jul 2002
    Posts
    227
    I tried this example:

    SQL> create table rooms2 (id number);
    Table created.

    SQL> CREATE MATERIALIZED VIEW ROOMS_SNAP
    2 BUILD IMMEDIATE
    3 REFRESH complete
    4 START WITH to_date(sysdate,'dd/mm/yyyy hh24:mi:ss')
    5 NEXT sysdate + 30/86400
    6 disable QUERY REWRITE
    7 AS
    8 SELECT id
    9 FROM ROOMS2
    10 GROUP BY id
    11
    /
    Materialized view created.

    SQL> insert into rooms2 values(1);
    1 row created.

    SQL> commit;
    Commit complete.

    SQL> select * from ROOMS_SNAP;
    no rows selected

    after 5 minutes

    /
    no rows selected

    but IF I run:

    SQL> execute dbms_refresh.refresh('ROOMS_SNAP');

    PL/SQL procedure successfully completed.

    select * from ROOMS_SNAP;

    ID
    ---------
    1


    what means?
    My refresh is manual?

    How can I refresh automatically my tables ROOMS?

    Thanks

  5. #5
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    No, this means exactly what the database does.
    As far as I know, Oracle creates a job to execute "execute dbms_refresh.refresh('ROOMS_SNAP');".

    If it's not being refreshed automatically, check your user_jobs view.

    Saludos,
    DKG.

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Do you see a refresh job in the job queue (dba_jobs) ?
    If so, is it broken or what is the next execution time ?
    If all that looks okay, what is the value of job_queue_processes ?
    and if you are using less than Oracle9 then what is the value for
    JOB_QUEUE_INTERVAL ??

    sql> sho parameter JOB_QUEUE_INTERVAL

    sql> sho parameter job_queue_processes

    HTH
    Gregg

Posting Permissions

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