Materialized View Refresh Problem

[ligang]

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 ?

[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.

[reydp]

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.

[slimdave]

Don't you mean "TRUNC(sysdate) + 1 + 13/24"?

[ligang]

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

[ligang]

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

[ligang]

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 )

[slimdave]

You want it to evaluate to 1pm the next day, no?

[ligang]

1PM today , next day and everyday