Materialized View Refresh Problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Materialized View Refresh Problem

  1. #1
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433

    Materialized View Refresh Problem

    My Database is oracle9.2.0

    I am synchornize data by using materialized view

    1. When Creating materialized view ->

    DEFINE START_TIME = "TRUNC(sysdate) + 12/24";
    DEFINE NEXT_TIME = "TRUNC(sysdate) + 13/24";

    --> My Purpose is to run the refresh every 1PM

    CREATE materialized view AR_CON_OPTION_DTL
    PCTUSED 40
    PCTFREE 10
    INITRANS 1
    MAXTRANS 255
    STORAGE (
    INITIAL 10M
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    BUFFER_POOL DEFAULT
    )
    TABLESPACE TS_ArchvMV
    LOGGING
    NOCACHE
    NOPARALLEL
    refresh FAST
    START WITH &&START_TIME
    NEXT &&NEXT_TIME
    WITH PRIMARY KEY
    USING DEFAULT ROLLBACK SEGMENT
    AS
    SELECT * FROM AR_CON_OPTION_DTL@FromRPTtoLive;

    2. I can use manual refresh to get the data-->

    DBMS_MVIEW.refresh('AR_CON_OPTION_DTL', 'F');

    3. I notice in DBA_JOBS ,
    the what columns indicate :
    dbms_refresh.refresh('"TNTRPT"."AR_CON_OPTION_DTL"');
    ( using different refresh mechanism ?)

    Broken column is 'N' and Failure is a number ( 4 or 5 , seems failed )

    Interval is "TRUNC(sysdate) + 13/24 "

    So I keep waiting but the materialized view is not refresh !

    What is the problem ?
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  2. #2
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    The one thing you have to remember with MV's is that they can only be manipulated by the owner. So, if you want to refresh the job or run it again log in as that user.

    I'm not saying that you weren't but, I've seen it in the past where people have logged into OEM as a different user and tried to "play" with the MV's and couldn't understand why they couldn't.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  3. #3
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    I am not sure if you are just going to create a sort of a read only snapshot site or the more complex full replication.
    Anyway, I hope you have a Materlzd View Logs on your source site to compliment your REFRESH FAST MV.

    I did try to accomplish the same task before, but the fastest/shortcut of doing to accomplish such task is to schedule such task in O.S.(Scheduled Task, cron), or in oracle RDBMS DBMS_JOB.
    And include this:
    begin
    dbms_refresh.refresh('REFRESH_GROUP');
    END;
    /

    where in REFRESH_GROUP includes ur MV.

    refresh group is:
    BEGIN
    DBMS_REFRESH.MAKE(
    name => '"PRISM"."REFRESH_GROUP"',
    list => '',
    next_date => to_date('01-01-2004 12:00:00','DD-MM-YYYY HH24:MI:SS'),
    interval => '/*1ays*/ sysdate + 1',
    implicit_destroy => FALSE,
    lax => FALSE,
    job => 0,
    rollback_seg => RBSBIG,
    push_deferred_rpc => FALSE,
    refresh_after_errors => TRUE,
    purge_option => NULL,
    parallelism => NULL,
    heap_size => NULL);
    END;
    /

    MV is:
    CREATE MATERIALIZED VIEW "SCHEMAOWN"."AMENDMENT_TYPES" PCTFREE 10
    PCTUSED 70 MAXTRANS 255
    STORAGE ( INITIAL 1024K NEXT 0K MINEXTENTS 1 MAXEXTENTS
    2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
    TABLESPACE "SNAPSHOT" BUILD IMMEDIATE
    USING INDEX
    TABLESPACE "SNAPSHOT" PCTFREE 10 INITRANS 2 MAXTRANS 255
    STORAGE ( INITIAL 1024K NEXT 0K MINEXTENTS 1 MAXEXTENTS
    2147483645 FREELISTS 1 FREELIST GROUPS 1 ) REFRESH FORCE
    WITH ROWID START WITH to_date('01-Jan-2004 12:11:09 PM',
    'dd-Mon-yyyy HH:MI:SS AM') NEXT /*1ays*/ sysdate + 1 AS
    SELECT "AMENDMENT_TYPES"."AMEND_CODE" "AMEND_CODE",
    "AMENDMENT_TYPES"."AMEND_DESC" "AMEND_DESC",
    "AMENDMENT_TYPES"."AMEND_ALIAS" "AMEND_ALIAS"
    FROM "AMENDMENT_TYPES"@SOURCEDB.COM "AMENDMENT_TYPES"
    /


    i am not sure if it can help.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Don't you mean "TRUNC(sysdate) + 1 + 13/24"?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    Originally posted by OracleDoc
    The one thing you have to remember with MV's is that they can only be manipulated by the owner. So, if you want to refresh the job or run it again log in as that user.

    I'm not saying that you weren't but, I've seen it in the past where people have logged into OEM as a different user and tried to "play" with the MV's and couldn't understand why they couldn't.
    Thanks a lot
    I verified , the login is perfect
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  6. #6
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    Originally posted by OracleDoc
    The one thing you have to remember with MV's is that they can only be manipulated by the owner. So, if you want to refresh the job or run it again log in as that user.

    I'm not saying that you weren't but, I've seen it in the past where people have logged into OEM as a different user and tried to "play" with the MV's and couldn't understand why they couldn't.
    Yes , I did , there is MV logs in the master site
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  7. #7
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    Originally posted by slimdave
    Don't you mean "TRUNC(sysdate) + 1 + 13/24"?
    Do you mean
    TRUNC(sysdate) + 13/24 is wrong ?

    IF I create MV today 11AM , can I use
    TRUNC(SYSDATE) + 13/24 ( to refresh it on 1PM today and everyday afterwards )
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You want it to evaluate to 1pm the next day, no?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    1PM today , next day and everyday
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

Posting Permissions

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



Click Here to Expand Forum to Full Width