Hi Tom,
In your reply you had mentioned that an option can be specified during the creation of MV to reflect the changes made in the base table. Will this work for only What

If a MV is based on say more than one table for e.g. 5 tables and has a number of columns from each base table.
What would happen if one of the column is updated in the base table. Will the change be reflected in the MV or the
"refresh on commit" would work for MV based on a single table.

Thanks,
Bharath

Hi Tom,
Can Materialized View be used in Oracle Discover? and If I want to create a MV, what should I set in the machine?
thanks ........

Can set operators like "union all", "minus" etc.. be used in a materialized view definition?

I was under the impression that it was not allowed (in either 8i or 9i) but looks like the person with the initial question was able to do it.

Thank you.

thanks ,
i wanna know is these materialized view could be refreshed on commit if the view is a join between two tables
on on my bdatabase and the other on another database(borland interbase) using a database link

how it could be

Hi Tom,
I have SELECT, INSERT, UPDATE, DELETE privs. on the SCOTT.EMP table and SELECT on MVIEW LOG table granted. When trying to create a Materialized View (REFRESH ON DEMAND) it works but fails when specified REFRESH ON COMMIT. What additional privs. are required for REFRESH ON COMMIT?

SQL> create materialized view mview_emp
  2  refresh fast on demand
  3  as
  4  select * from scott.emp
  5  /

Materialized view created.

SQL>
SQL> drop materialized view mview_emp
  2  /

Materialized view dropped.

SQL>
SQL> create materialized view mview_emp
  2  refresh fast on commit
  3  as
  4  select * from scott.emp
  5  /
select * from scott.emp
                    *
ERROR at line 4:
ORA-01031: insufficient privileges


SQL>
SQL> disconnect
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
SQL>
 

can we use multiple joins on MV's
REFRESH complete WITH ROWID
START WITH SYSDATE NEXT trunc(SYSDATE) + 1
AS
SELECT so.urefitem, fc.CREFITEM1 , Count(oi.prefitem) ItemCount
FROM tab1 so INNER JOIN tab2 oi ON
so.sorefitem = oi.sorefitem INNER JOIN tab3 pc ON
oi.prefitem = pc.prefitem INNER JOIN tab4 fc ON
pc.flatiD = fc.FLATID
WHERE so.ostatus = -1 AND so.osubstatus = -1 AND fc.CREFITEM1 <> 1
GROUP BY so.urefitem, fc.CREFITEM1 ;
like the above
iam getting ORA-00904 error

[tkyte@tkyte-pc-isdn tkyte]$ oerr ora 904
00904, 00000, "%s: invalid identifier"
// *Cause:
// *Action:


your query is "bad"

if you 

SQL> set autotrace traceonly explain
SQL> select .....

does the query even parse? 

create materialized view mv1
refresh fast
start with sysdate
next sysdate+1/500
with primary key
as select * from mv_tab@mydblink;

gives the error :ora-12015 cannot create a fast refresh materialized view from a complex query

I've been struggling to get ON COMMIT working on a simple join MV. I have finally been able to work out the problem and it appears to be down to my use of the newish ANSI join syntax.

When I try to create the MV using the 'inner join', I get "ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view".

When I switch the joins into the normal where clause syntax, all works fine.

I had a good look around in doco and can't find any reference to this restriction. Should I log this as a TAR or have I missed an obvious reference somewhere?

Test case follows :

drop table test_x;
drop table test_y;

create table test_x (a number, b varchar2(10));
create table test_y (a number, b varchar2(10));

drop materialized view test_mv;

create materialized view test_mv
BUILD IMMEDIATE
REFRESH on commit
ENABLE QUERY REWRITE
AS
select x.a xa
,x.b xb
,y.b yb
FROM test_x x
inner join test_y y
on (y.a = x.a)
/

create materialized view test_mv
BUILD IMMEDIATE
REFRESH on commit
ENABLE QUERY REWRITE
AS
select x.a xa
,x.b xb
,y.b yb
FROM test_x x
,test_y y
where y.a = x.a
/

insert into test_x values (1,'a');

commit;

select * from test_mv;

insert into test_y values (1,'b');

commit;

select * from test_mv;


Thanks for your great help.
Glen

Just to let you know, logged a TAR on the above issue (bug 4143202). The Oracle documentation will be amended to state ansi joins are not permitted in 'refresh on commit' MVs.

The reason is :
"Transformation for the ANSI INNER JOIN in oracle is as follows.
select x.a xa
,x.b xb
,y.b yb
FROM test_x x inner join test_y y
on (y.a = x.a)
/
.
will be tranformed to
.
select xa
, xb
, yb
from (select x.a xa, x.b xb, y.b yb
from test_x x, test_y y
where y.a = x.a) V ;
.
As there is an inline view in the query , creation of mview fails with error ORA-12054.
"

At least the doco will soon highlight the problem.

Thanks
Glen

Tom,

We use 9.2.0.4 version. Most of our tables are loaded using sql*loader. Sql*loader commits after every 10,000 rows (ROWS=10000). Most table loads are conventional.

I wanted to know if I create an MV on one of such tables using ON COMMIT clause, does the refresh occur multiple times (equal to the number of commits during sql*load) or only once?

Thank you.

we user oracle 9.2.0.4. I have a snapshot log and a materialized view created on a huge base table. One of
the column width in the base table has been increased.
Can we alter the snapshot log and mview to accomodate the
revised column definiton or we have to drop and re-create
snapshot log and mview which will take very long time.
Please advise thanks for your help.

The mview has been created using refresh force with primary key option

I would like to know, if a refresh of Materialized view fails, how can a user be notified, say by email

Thanks so much tom!
Can you please provide a sample code for custom job and email notification, or direct to the URL where it is already available.

Tom,
I am trying to refresh specific materialized view records based on a key-value (say employee_id), and leave the rest of the mview records unrefreshed.

dbms_mview.refresh('emp_mv', ???)
only for emp_mv.emp_id=123.

Can this be done?
Thanks

I wanted to create one I materialize view in an user (app_cpal_bi) with the following command:

create materialized view cpl_custo_consum_doentes_v2
build immediate
refresh fast on commit
enable query rewrite
as
select *
from app_cpal.cpl_custo_consum_doentes_v2;

app_cpal = another user

he gave the following error: "ORA-12054: it is not possible to define the attribute of renewal ON COMMIT for the materialized sight".
what I must make ?

Hi,

I am having two servers one at london another is in new york. On London server I can not do any changes in database while in new york server I can do any changes.  I want to replicate a data of a single table of london server to new york server. 

Suppose in London server I have table call 't' having 15 columns, which should be replaced on newyork server. Table 't' is currently having less number of insert/update records ( no delete ) and expected to large no of rows in a future. 

How can I achive this ? I thought using mv on newyork server using refresh fast but I can not create mv log in london server I had tried this

drop database link s0055;

create database link s0055
connect to scott
identified by tiger
using 'test'
/

create materialized view log on t@s0055 with rowid;

11:33:17 SQL> @b

Database link dropped.

Database link created.

create materialized view log on t@s0055 with rowid
*
ERROR at line 1:
ORA-00949: illegal reference to remote database

Kindly suggest me urgently, how can I achive this ( want some kind of  fast refresh to avoid huge network traffic )in Oracle 9.2 database ?

thanks & regards
PJP 
 

Hi Tom,

Thanks for your reply. I have following few questions regardsing mv refresh in 9.2 database !!!

1/ Is there any way to find out what way mv has been refreshed ? for e.g. I have a view call 'parag' and it has been refreshed last 2 hours. Now I want to know by what method it has been refreshed ( fast or full ). How can I find out ?

2/ Last timestamp of mv refreshed ?

3/ Amount of network traffic used due to mv refreshed on different servers. For e.g. my london server having mv log and newyork server is having mv. I want to find out if I do refresh of mv in fast mode how much network bandwidth is using it ?

thanks & regards
pjp

Hi,

Thanks for your help. Will you pl explain in more detail
<
query v$mystat to see how much you've used so far.
run your refresh interactively
query v$mystat to see how much MORE you've used so far.
subtract
>
of using v$mystat. Will you kindly show me "select" statment for v$mystat ?

Secondly, If I am having average row length then can I calculate avg row len * no of rows in mv log to find network traffic ?

thanks & regards
PJP

Hi Tom,

I am using oracle 9.2.0.6. I am facing a problem on creating fast refresh MV. Kindly suggest me workaround.

on Server A sombody created table

create table f ( timestamp timestamp primary key );

( column name same as datatype, which is causing a problem )

insert into f select localtimestamp from dual;
insert into f select localtimestamp from dual;
insert into f select localtimestamp from dual;

create materialized view log on f;

On Server B I am tring following

drop database link s0055
/
create database link s0055
connect to scott
identified by tiger
using 'orcl'
/
drop materialized view mvf
/
create materialized view mvf
parallel
build immediate
refresh fast on demand with rowid
as
select *
from scott.f@S0055
/

ERROR at line 7:
ORA-04052: error occurred when looking up remote object ATLAS.F@S0055
ORA-00604: error occurred at recursive SQL level 1
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is
incomplete or malformed
ORA-02063: preceding 2 lines from S0055

Will you pl suggest me workaround for creating MV without disturbing original table structure ? ( Can I create MV as FAST REFRESH on view, I have tried it but not succeeded )

thanks & regards
PJP

someone really did this:

( timestamp timestamp primary key );

and thought it was a "good idea"???  date/time fields are not good for primary keys first of all and naming the column after the datatype is "not smart"


but I cannot reproduce:

ops$tkyte%ORA9IR2> create table f ( timestamp timestamp primary key );

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> insert into f select localtimestamp from dual;

1 row created.

ops$tkyte%ORA9IR2> insert into f select localtimestamp from dual;

1 row created.

ops$tkyte%ORA9IR2> insert into f select localtimestamp from dual;

1 row created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create materialized view log on f with rowid;

Materialized view log created.

ops$tkyte%ORA9IR2> create database link loopback@ora9ir2
  2  connect to ops$tkyte identified by xxxxx using 'ora9ir2';

Database link created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create materialized view mvf
  2  parallel
  3  build immediate
  4  refresh fast on demand with rowid
  5  as
  6  select *
  7  from f@loopback@ora9ir2
  8  /

Materialized view created.


so, I'll have to ask you to work this one via support. 

Hi Tom,

Thanks for your brilliant answer. But In my case still it is not working.

Server A1 under user ('a')

  1* create table f ( timestamp timestamp )
SQL> /

Table created.

SQL> create materialized view log on f with rowid;
Materialized view log created.

Server 'b1' under user 't'

drop database link a@atinp2
/
create database link a@atinp2
connect to atlas
identified by atlas
using 'atinp2'
/
drop materialized view mvz23
/
create materialized view mvz23
parallel
build immediate
refresh fast on demand with rowid
as
select *
from f@a@atinp2
/

Database link dropped.
Database link created.
drop materialized view mvz23
*
ERROR at line 1:
ORA-12003: materialized view "T"."MVZ23" does not exist

from f@a@atinp2
     *
ERROR at line 7:
ORA-04052: error occurred when looking up remote object F@A@ATINP2
ORA-00604: error occurred at recursive SQL level 1
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is
incomplete or malformed
ORA-02063: preceding 2 lines from A@ATINP2

Kindly suggest where I have gone wrong. 

In your example I assumed "loopback" is not having much meaning. ( I have tried above example with loopback@atinp2 but sam error ). I also raised a service request 5813754.992.

thanks & regards
PJP
 

Hi Tom,
Oracle version 9.2.0.7.0 - 64bit Production
OS: HP-UX

We have a job which refreshes a Mview group everyday at 8 AM
The group has a mview which is failing with this error and a result the job as well.
The particular mview has the following attributes:

Non-partitioned
References a Single Table from a remote site.
Refersh on Demand
Fast refresh
Read Only

The master table at the remote site is also non partitioned and has a mlog.

The mview was truncated a few days ago.
Then after getting the error I refreshed it manually using COMPLETE refresh. Then, changed the refresh mode to force and executed the job manually. The job executed successfully and got reset to the correct value of next_date. Also failures was 0 after running the job successfully.
But even after that I keep getting this error.
Please advice what am I doing wrong.
Thanks as always.

Hi Tom,
I havd read about the error and based on that did a complete refresh.
But, even after that it is generates this error.
Do you mean that after truncating the mview all subsequent refreshes have to be complete and fast refresh option has to be left out.

I did an explain plan using MV_CAPABILTIES_TABLE as suggested but did not know which attribute or data will specify the reason for failed fast refreshes.

CAPABILITY_NAME P REL_TEXT MSGTXT
------------------------------ - -------- ----------------------------------------------------------
PCT N
REFRESH_COMPLETE Y
REFRESH_FAST Y
REWRITE N
PCT_TABLE N WFRSAPPS relation is not a partitioned table
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML Y
REFRESH_FAST_AFTER_ANY_DML Y
REFRESH_FAST_PCT N PCT is not possible on any of the detail tables in the
materialized view

REWRITE_FULL_TEXT_MATCH N WFRSAPPS mv references a remote table or view in the FROM list
REWRITE_FULL_TEXT_MATCH N query rewrite is disabled on the materialized view
REWRITE_PARTIAL_TEXT_MATCH N materialized view cannot support any type of query rewrite
REWRITE_PARTIAL_TEXT_MATCH N query rewrite is disabled on the materialized view
REWRITE_GENERAL N materialized view cannot support any type of query rewrite
REWRITE_GENERAL N query rewrite is disabled on the materialized view
REWRITE_PCT N general rewrite is not possible and PCT is not possible on
any of the detail tables
Thanks as always.

Thanks for the response Tom.
That is exactly what I have done.
I refreshed the mview using COMPLETE Refresh.
But even after that the job is failing for the same mview with ORA-32320

Am I missing something?
Please advice.
Thanks as always.

Just a guess, but you'd probably need to purge or drop and recreate the mlog, then complete refresh the mview. Or something like that.

Hi Tom,

Suppose I created mv like this to refresh data in every 5 mins

CREATE MATERIALIZED VIEW T
REFRESH FAST START WITH SYSDATE NEXT 5/(24*60)
AS SELECT .....
from toto@remotedb <======== db link for remote database server

Will you pl explain what happens if my remote database is down for few hours for daily backup ? Does it will create any problem in local database ?

thanks & regards
PJP

Hi Tom,

Thanks for your answers to my questions. I have written following procedure to refresh mvs over dblink in every 5 mins

create or replace procedure refresh
as
kount number(9) := 0;
begin
begin
select count(*)
into kount
from t@test01; <= remote db is up or not checking
exception when others then
kount := 0;
end;

if kount > 0 then
dbms_mview.refresh('mv_test', 'F', '', TRUE, FALSE, 0, 0, 0, FALSE);
end if;
end;
/

variable n number;

begin
dbms_job.submit(:n, 'refresh;', sysdate, 'sysdate+5/(24*60)');
commit;
end;
/

Q1/ Do you think is it a good idea to refresh mv in this manner ?

I have written following SQL to observe network traffic due to mv refresh

select a.sid, a.statistic#, b.name, a.value
from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.STATISTIC# = 114
/

Q2. Is it correct ?
Q3 If I do refresh of mv it shows increase by 8 in value

SID STATISTIC# NAME VALUE
--- ---------- -------------------- ----------
12 114 redo entries 27

<--- refresh mv --->

SID STATISTIC# NAME VALUE
--- ---------- -------------------- ----------
12 114 redo entries 35

<--- refresh mv --->

SID STATISTIC# NAME VALUE
--- ---------- -------------------- ----------
12 114 redo entries 43

So can I conclude that due mv refresh network traffic is 8 os blocks ?

thanks & regards
PJP

Hi Tom,

I am not using buitin feature of mv for refresh because my remote database ( on remote server ) gets completly shutdown before EOD process. ( Timing is not fixed and it is for 1 hour approx ). So mv refresh will get failed after 16 attempts.

So according to me this kind of refresh can be done without any problem as on local database I am having full control and data in mv should be available to users early morning.

Secondly, my dbms_refresh will break down during backup. So I have no option to write a job to run broken job, so why not take care at time of refresh !!!

q1/ Do you think buitin feature will be more useful than
refresh thru procedure in a such situation ?

q2/ Will you pl answer my query regarding mv refresh data traffic monitoring ?

thanks & regards
PJP

Hi Tom,

I have asked you how to monitor how much data has been transferred over network due to mvs refresh ? You have given me following answer in same question above

<quote>

v$mystat - has your sessions statistics, join it to v$statname and use a where
clause to get "redo" information.
<quote end>

so I have written following query to monitor

select a.sid, a.statistic#, b.name, a.value
from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.STATISTIC# = 114
/

Have I missed out your point ? Pl tell me.

thanks & regards
PJP

you just queried out:

SID STATISTIC# NAME                      VALUE
--- ---------- -------------------- ----------
 12        114 redo entries                 43

redo entries.  One would normally query, well, network related statistics to see, well, network traffic no?


please don't use numbers by the way, 114 - that'll change from port to port and version to version.  Use the statistic names, and use one that at least has "Net" in it if you want "network" stuff


ops$tkyte%ORA10GR2> select name from v$statname where name like '%Net%';

NAME
--------------------------------------------------
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
bytes sent via SQL*Net to dblink
bytes received via SQL*Net from dblink
SQL*Net roundtrips to/from dblink

6 rows selected.

 

We want to create MV over big fact table, we are going to have aggregate values of fact table in MV.
Fact table will have high number of insert, and we have to clean the old data which are 3 weeks older.

Is it possible to preserve aggregate values in MV, even though some data deleted from fact table ?
New insert data to be aggregate in MV along with previously aggregate values.

This means MV must be in Synchronize with base table.
We want aggregated data in MV alone to be stored for longer period (2 years) and deleting fact data every 3 weeks. Is there any other way we can do this?
Kindly give your suggestion

Ram, you ought to be aware that query rewrite will be a tricky proposition with materialized views when you drop old data from the fact table and preserve it in the MV, as Oracle may recognise that it's cheaper to query a detailed fact table than it is to query the MV even if the MV is small, as the fact table will actually return no data for old periods. Using DBMS_Advanced_Rewrite is a better proposition as it is documented that a rewritten plan is always preferred over a non-rewritten plan. http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_advrwr.htm#i999507

Fortunately if you are going to be hand-coding your aggregate maintenance (not a bad option at all, in my opinion) then you'd be better off using DBMS_Advanced_Rewrite than you would with MV's. Unfortunately, 10g+ only.

I read this:
If a summary contains data that is based on data which is no longer in the fact table, maintain the materialized view using fast refresh ( http://lbdwww.epfl.ch/f/teaching/courses/oracle8i/server.815/a67775/ch5.htm#31381 )

Does that mean, fast refresh will retain data in the MV even if its deleted from the base fact table?
Also, will query rewrite work in such a case?

Will query rewrite work when the MV query contains Union?

Please see my question above "MV must be in Synchronize with base table" asked on July 9, 2007.

Instead of deleting old data, can we move old data in to separate storage and keep MV & fact table in Synchronize.
My projects is in telecom domain, we will have approximately 400+ fact table, all columns of this table are numbers and these table will have *only insert*. Inserts thousands of rows for every 15 minutes in each table. These data we call it as "raw" data. All read operation are read by hour/day/week/month aggregate.

I am think to do following steps :
Create partition by week and compress older partition table.
Create materialized view for hour/day/week/month/year.
Since we may not read raw data table for any future use, can we move these older partition table to some other storage device.
We are mainly looking for solution for disk space, where disk is getting filled up with "raw" data.
Let me know your suggestion for disk storage problem.

Regards
Ramki

ORA-12012: error on auto execute of job 7
ORA-12008: error in materialized view refresh path
ORA-04052: error occurred when looking up remote object LDS.LOTF@KFLOWF.LOCALHOST
ORA-00604: error occurred at recursive SQL level 3
ORA-03106: fatal two-task communication protocol error
ORA-02063: preceding line from KFLOWF
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line
I get following error in materialized view

Hi Tom,
Can we use "on commit" option for refresh when we are using dblinks? I tired it gave me error. Please suggest

We are having requirment to sink the backup(read only) database. Would it be better to use oracle replication (using streams) as compared to Materialize view.

Please suggest

Materialize view is created on separate Database and are getting refreshed using DBLink. For testing we put the Base Table user offline, we got build broken job message in the log.
Then we made the user online and manually refreshed the materialize view. But it didn't started getting refreshed automatically as earlier.
Table log was having the data, DB Link was also fine but still it was not getting refreshed. We have to manually refresh it.
To overcome this we had to drop and recreate the Materialize view. Could you please explain the possible steps/mechanism to avoid recreation of Materialize view in case of any failure at the base tables.

Hi Tom hope u r doing well,
I am unable to create fast refresh mview even after i have created mview log tables for the below query:(i need it urjent plz help me)
CREATE MATERIALIZED VIEW APPS.XX_SHOW_TEST1
TABLESPACE APPS_TS_TX_DATA
PCTUSED 0
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 128K
NEXT 128K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FAST
START WITH TO_DATE('26-maj-2011 08:55:15','dd-mon-yyyy hh24:mi:ss')
NEXT DECODE( SIGN ( 5 - TO_CHAR(SYSDATE, 'HH24') ) , 0 , SYSDATE + 2/24 , 1 ,SYSDATE + 6/24 , SYSDATE + 1/(24*6) )
WITH PRIMARY KEY
AS
/* Formatted on 2011/05/26 14:03 (Formatter Plus v4.8.8) */
SELECT hp.party_name customer_name,
DECODE (hp.party_type, 'PERSON', 'Y', 'N') person_flag,
oeh.attribute1 tax_payer_id,
DECODE (oeh.org_id, 2, 'COM03', 47, 'COM04') country_code,
oeh.order_number oracle_order_id, oeh.attribute15 order_type,
oel.orig_sys_document_ref master_order_id,
oel.orig_sys_line_ref psft_order_id, oel.ordered_item, mtl.description,
oel.flow_status_code line_item_status, oeh.attribute18 auth_return,
oeh.creation_date order_creation, confirm_date ship_confirm_date,
xp.pickup_date pickup_date, oel.attribute1 msisdn,
oel.attribute8 eu_first_name, oel.attribute9 eu_last_name,
wdd.tracking_number tracking_number, xa.delivery_phone delivery_phone,
xa.delivery_contact delivery_contact, oel.attribute5 sales_person,
oeh.sales_channel_code sales_channel_code,
oeh.attribute17 agreement_url, qpi.segment1 proposition_code,
qpi.description desc_proposition,
DECODE (s.orig_sys_document_ref, NULL, 'N', 'Y') return_created,

/*DECODE
(oeh.attribute15,
'RE', NULL,
'RP', NULL,
'EX', NULL,
DECODE ((SELECT COUNT (ROWID)
FROM oe_order_lines_all ay
WHERE ay.reference_header_id = oel.header_id
AND ay.reference_line_id = oel.line_id),
0, 'N',
'Y'
)
) return_created, */
xa.confirmation_email confirmation_email,
xa.confirmation_sms confirmation_sms,
NVL (xa.order_acceptance_status, 'NULL') order_acceptance_status,
--Added for B5921
NVL (xa.order_confirmation_sent, 'NULL') order_confirmation_sent,
--Added for B5921
xa.customer_ip customer_ip_address, --Added for B5921
xa.customer_timestamp customer_timestamp, --Added for B5921
get_hold_status_order (oeh.header_id) hold_status,
s.orig_sys_document_ref case_id, --NEW

/*DECODE ('**XXYYZZ**',
'**XXYYZZ**', (SELECT s.orig_sys_document_ref
FROM oe_order_lines_all s
WHERE s.reference_header_id =
oel.header_id
AND s.reference_line_id =
oel.line_id
AND ROWNUM <= 1)
) case_id, */
DECODE
('**XXYYZZ**',
'**XXYYZZ**', (SELECT description
FROM fnd_lookup_values
WHERE lookup_type = 'CREDIT_MEMO_REASON'
AND lookup_type LIKE 'CREDIT_MEMO_REASON'
AND lookup_code = s.return_reason_code) --NEW
) return_reason
FROM oe_order_headers_all oeh,
oe_order_lines_all oel,
xx_additional_order_info xa,
xx_pickup_dates xp,
mtl_system_items_b mtl,
hz_cust_site_uses_all hcs,
hz_cust_acct_sites_all has,
hz_party_sites hps,
hz_parties hp,
hz_locations hl,
oe_order_price_attribs oep,
xx_qp_items qpi,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
wsh_delivery_details wdd,
oe_order_lines_all s
WHERE oeh.header_id = oel.header_id
AND oeh.orig_sys_document_ref = xa.orig_sys_document_ref(+)
AND oeh.order_source_id = fnd_profile.VALUE ('XX_CRM_IMPORT_SOURCE') --NEW
AND oeh.org_id = xa.org_id(+)
AND s.reference_header_id(+) = oel.header_id
AND s.reference_line_id(+) = oel.line_id
--AND oeh.sold_to_org_id = has.cust_account_id
AND wdd.tracking_number = xp.tracking_number(+)
AND mtl.organization_id =
DECODE (oel.org_id,
2, 4,
47, 85
) -- NEW changed order of org and segment
AND mtl.segment1 = oel.ordered_item -- NEW changed order of org and segment
AND oeh.ship_to_org_id = hcs.site_use_id
AND has.cust_acct_site_id = hcs.cust_acct_site_id
AND hps.party_site_id = has.party_site_id
AND hp.party_id = hps.party_id
AND hl.location_id = hps.location_id
AND wnd.delivery_id(+) = wda.delivery_id
AND wda.delivery_detail_id(+) = wdd.delivery_detail_id
AND wdd.source_line_id(+) = oel.line_id
AND NVL (oeh.quote_number, 1) = NVL (oeh.quote_number, 1) -- NEW
AND oeh.order_type_id IN
(1193, 1274, 1199, 1214, 1253, 1197, 1276, 1255, 1201)
AND oeh.attribute15 != 'EX'
AND oeh.creation_date >
TRUNC
(SYSDATE - 365)
--between TRUNC (SYSDATE - 565) AND TRUNC (SYSDATE - 201)
AND oeh.order_category_code = 'ORDER' -- NEW
AND oep.header_id(+) = oel.header_id
AND oep.line_id(+) = oel.line_id
AND qpi.inventory_item_id(+) = oep.pricing_attribute1
AND NVL (qpi.organization_id, DECODE (oel.org_id, 2, 4, 47, 85)) =
DECODE (oel.org_id,
2, 4,
47, 85
);

Hi TOm,Iam very thankful to your reply.
and u asked me to think about sysdate for fast refresh materialized view.then i came to know that mine is complex query which is not eligible for fast refresh. Now my question is my force refresh materialized view is taking more time to get refreshed.Please help me so that i can reduce the time for process of my force refresh materialized view.

thanks
Raju

Hi tom, you are helping me alot.you suggested me to use atomic_refresh=false.But in my scenario i cant do this because they need to use this mview very frequently.So could you suggest me any other alternative way.

Hi,
Greetings.
We have a requirement to replicate 2 tables in a remote environment. So a obvious approach will be a materialized view. We are thinking of Materialized view with prebuilt table option and creating a materialized view logs for a fast refresh.

Environment details

1) Source environment- 2 physical tables are present say Table_A,Table_B
2) Remote Site - Above tables are required here, not on line but a refresh once a day after activity is over in Source database.

What are the best ways to have this replicated in the Remote 2 site.

We are thinking of creating a materialized view on pre built table and create materialized view logs on the source Database for a Fast Refresh. We are running with 10g Release 2 i.e 10.2.0.1.0 and will use a Atomic Refresh - TRUE.

We plan for a materialized view on a prebuilt table because further to creation of materialized view, its our understanding that any query written on those table(s) will be queried from the materialized view. So if this is TRUE i shall resort to this option instead of creating a materialized view for different queries on same 2 tables.

We dont want a View because the data needs to travel from 2 remote locations where we dont enjoy a good network bandwidth but where as a Refresh of Materialized view is one time activity.


Please suggest a best alternative to have this done or if prebuilt table option is not right.

Hi
Thanks for your post. Can you please explain in detail on what do you mean by "prebuilt table for offline instantiation typically", am not able to comprehend this.
also explain us on What is the main use of this clause "prebuilt table" while creating Materialized views.

Thanks in advance

Hi Tom,hope doing well.I have created a FAST REFRESH materialized view but i wanted to know how long it is getting refreshed.please expalin me with the syntax.
and if at all it goes for long time how can i reduce the refresh time.

Hi Tom,

Hope you are doing well.
I have created a fast refresh materialized view but it is not performing any refresh.I checked in DBA_JOBS table for my specific materialized view then i came to know that no refresh happened and it is not going to do any refresh in the future (by seeing last_date,next_date in dba_jobs table).Then i have checked my materialized view capabilities in mv_capabilities_table i got a message that" materialized view log is newer than last full refresh". kindly help me . Thanks in advance.

hi Tom,

As you suggested i have done complete refresh but still no use.Could you give me any other solution.Thanks in advance.

Hi Tom,
i have created FAST refresh materialized view.it is eligible for FAST refresh because when i executed SELECT * FROM USER_MVIEWS i could see FAST_REFRESHABLE=DIRLOAD_DML and STALENESS=STALE and it is not getting refreshed(through DBA_JOBS).Could you tell me the reason why it is in STALE status and how to resolve it. and one of my materialized views is in NEEDS_COMPILE status, Could you tell me the reason why it is and how to resolve it

How to use a materialised view refresh,where the refresh is done for a table daily but the updated date doesnt change.It changes only when there is change in the project for an employee.Kindly reply ASAP.

Hello Tom.

Is it possible to preserve the incremental refresh capabilities of a materialized view, in the face
of partition operations, when the base table(s), mlog and mvlog are all equipartitioned?

I can see why the behaviour below is sensible in general but, unless I misunderstand something, I
think it is safe in our case.

The reason we wish to do this is that we'd like to enforce business-rule integrity constraints via
ON COMMIT mviews (they're ON DEMAND in the example just to make it easier to test).
We'd probably be happy to suffer an individual customers constraints needing to be fully rechecked
when their partitions are moved but needing to recheck every customer's constraints is likely to be
unworkable (as I believe we'd need to cease all DML by all customers until we can alter the mv back
to REFRESH FAST ON COMMIT).

Any thoughts?

Thanks.


drop table test1;
drop table test2;
drop table tmp;
drop materialized view mv_test1;
drop materialized view mv_test2;
drop materialized view mv_tmp;
drop table mv_test1;
drop table mv_test2;
drop table mv_tmp;


create table test1
(
CUSTOMERID VARCHAR2(35),
test NUMBER(*,0)
)
partition BY list(CUSTOMERID)(Partition defaultpartition VALUES(NULL));

create table test2
(
CUSTOMERID VARCHAR2(35),
test NUMBER(*,0)
)
partition BY list(CUSTOMERID)(Partition defaultpartition VALUES(NULL));

create table tmp
(
CUSTOMERID VARCHAR2(35),
test NUMBER(*,0)
);

CREATE UNIQUE INDEX test1_PKidx ON test1 (CUSTOMERID, test) LOCAL;
CREATE UNIQUE INDEX test2_PKidx ON test2 (CUSTOMERID, test) LOCAL;
CREATE UNIQUE INDEX tmp_PKidx ON tmp (CUSTOMERID, test) ;

ALTER TABLE test1 ADD CONSTRAINT test1_PK PRIMARY KEY (CUSTOMERID,test) ENABLE NOVALIDATE;
ALTER TABLE test2 ADD CONSTRAINT test2_PK PRIMARY KEY (CUSTOMERID,test) ENABLE NOVALIDATE;
ALTER TABLE tmp ADD CONSTRAINT tmp_PK PRIMARY KEY (CUSTOMERID,test) ENABLE NOVALIDATE;


CREATE MATERIALIZED VIEW LOG ON test1
partition BY list(CUSTOMERID)(Partition defaultpartition VALUES(NULL))
WITH
PRIMARY KEY,
ROWID
;
CREATE MATERIALIZED VIEW LOG ON test2
partition BY list(CUSTOMERID)(Partition defaultpartition VALUES(NULL))
WITH
PRIMARY KEY,
ROWID
;
CREATE MATERIALIZED VIEW LOG ON tmp
WITH
PRIMARY KEY,
ROWID
;

create table mv_test1
partition BY list(CUSTOMERID) (Partition defaultpartition VALUES(NULL))
as select customerid, test from test1
where 1 = 2;

create table mv_test2
partition BY list(CUSTOMERID) (Partition defaultpartition VALUES(NULL))
as select customerid, test from test2
where 1 = 2;

create table mv_tmp
as select customerid, test from tmp
where 1 = 2;


CREATE MATERIALIZED VIEW mv_test1
on prebuilt table
refresh fast on demand
as select customerid, test from test1;

CREATE MATERIALIZED VIEW mv_test2
on prebuilt table
refresh fast on demand
as select customerid, test from test2;


CREATE MATERIALIZED VIEW mv_tmp
on prebuilt table
refresh fast on demand
as select customerid, test from tmp;





alter table test1 add partition CUST01 values ('CUST01');
alter table MLOG$_test1 add partition CUST01 values ('CUST01');
alter table MV_test1 add partition CUST01 values ('CUST01');

alter table test1 add partition CUST02 values ('CUST02');
alter table MLOG$_test1 add partition CUST02 values ('CUST02');
alter table MV_test1 add partition CUST02 values ('CUST02');

alter table test2 add partition CUST01 values ('CUST01');
alter table MLOG$_test2 add partition CUST01 values ('CUST01');
alter table MV_test2 add partition CUST01 values ('CUST01');

insert into test1 ( customerid, test) values ('CUST01', 10);
insert into test1 ( customerid, test) values ('CUST01', 11);
insert into test1 ( customerid, test) values ('CUST02', 20);
insert into test1 ( customerid, test) values ('CUST02', 21);
commit;

exec dbms_mview.refresh('MV_TEST1', 'f');

insert into test1 ( customerid, test) values ('CUST01', 13);
insert into test1 ( customerid, test) values ('CUST01', 14);
insert into test1 ( customerid, test) values ('CUST02', 23);
insert into test1 ( customerid, test) values ('CUST02', 24);
commit;

--Ensure that no DML is occurring to any CUST01 related partition
alter table mv_test1 exchange partition CUST01 with table mv_tmp INCLUDING INDEXES WITHOUT VALIDATION;
alter table MLOG$_test1 exchange partition CUST01 with table MLOG$_tmp INCLUDING INDEXES WITHOUT VALIDATION;
alter table test1 exchange partition CUST01 with table tmp INCLUDING INDEXES WITHOUT VALIDATION;

--All partitions in MLOG are truncated and following refresh fails due to MLOG being newer than last complete refresh
exec dbms_mview.refresh('MV_TEST1', 'f');

Ok, thanks.

So, the conclusion I have reached is that partition exchange operations are incompatible with mlogs
and, by consequence, incremental refresh of mviews. Hence partition exchange rules out enforcing
any complex integrity constraints where you can't get a complete refresh done quick enough for 'on
commit' usage.

PCT won't help this situation as we need incremental update on all DML.

And this is a problem even when everything is equipartitioned and you are able to manually ensure
that the base tables, mlogs and mviews are all in synch as you can't assert that you've put the
right data into the mlog (sort of like what you can do for mviews using prebuilt tables).

Hi Tom,

We have created materialized view having multiple base tables and used cast function for few columns in MV. The database is running in Archive mode.

After these changes the MV's are generating lot of Archive 6GB/Hr resulting in space issues which was normally 1GB/Day

Is this behaviour normal or we needed to add some hint in MV's or tune some DB parameter.

Thanks for your recommendation.
My pain point is using the CAST function. When my MV is not using the cast function and is based on single base table, logs generated are within limits.
If my MV uses the cast function and access multiple base tables, log generation increase too many folds.
Each of my base tables has 1-2 million records and gets 60-70K update per day. We have refresh frequency of 5 min for MV.
Can you please help me understand where my approach is off track?
Can we go by the complete refresh route?

ORA-32320: REFRESH FAST of "E****"."RM*********" unsupported after container table PMOPs

ops$tkyte%ORA11GR2> !oerr ora 32320
32320, 00000, "REFRESH FAST of \"%s\".\"%s\" unsupported after container table PMOPs"
// *Cause:  A Partition Maintenance Operation (PMOP) has been performed on the
//          materialized view, and no materialized view supports
//          fast refersh after container table PMOPs.
// *Action: Use REFRESH COMPLETE.  Note: you can determine why your
//          materialized view does not support fast refresh after PMOPs using
//          the DBMS_MVIEW.EXPLAIN_MVIEW() API.

Hi Tom,
I have some doubt on "ON COMMIT" refresh.

1. Does on commit refresh will refresh the mview only for the updated master table's data or it will do the updated values of the select query inside the mview.

2. what are the advantages and disadvantages of on commit refreshes.

3.I read in the some ML note that on commit refresh will genterate the mview logs. what is the use of that logs.



In our database we have 3 mviews which are doing the complete refresh every 3 min(due to this we have more redo generation.). I'm Planing to change this refresh to on commit.

4. Does On commit refresh will generate the less redos as compared to complete refresh.

Please suggest me how to avoid/ minimize the redo generations.

Thanks