alter session set query_rewrite_integrity = stale_tolerated;

If I use this statement, my result is not up to date;
I try to refresh like this :
exec dbms_mview.refresh('dtl_budget_mv');

My query still can't use this materialized view.
some missing my parameter ?

You must have skipped something somewhere.  I would need a test case from start to finish in order to see where.  Here is one that shows

a) the MV is used
b) we make the MV "stale" and see it does not get used
c) we refresh it
d) we see the MV getting used again

I would need something similar from you showing something different.


ops$tkyte@ORA817DEV.US.ORACLE.COM> create table my_all_objects nologging
  2  as select * from all_objects;

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table my_all_objects add constraint my_all_objects_pk primary key(object_id);

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create snapshot log on my_all_objects with rowid ( owner ) including new values;

Materialized view log created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table my_all_objects compute statistics;

Table analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> create materialized view my_all_objects_aggs
  2  build immediate
  3  refresh fast
  4  enable query rewrite
  5  as
  6  select owner, count(*)
  7    from my_all_objects
  8   group by owner
  9  /

Materialized view created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table my_all_objects_aggs compute statistics;

Table analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set query_rewrite_enabled=true;

Session altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set query_rewrite_integrity=enforced;

Session altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace traceonly
ops$tkyte@ORA817DEV.US.ORACLE.COM> select owner, count(*)
  2    from my_all_objects
  3   group by owner;

31 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=31 Bytes=310)
   1    0   TABLE ACCESS (FULL) OF 'MY_ALL_OBJECTS_AGGS' (Cost=1 Card=31 Bytes=310)




Statistics
----------------------------------------------------------
         11  recursive calls
         12  db block gets
         18  consistent gets
          0  physical reads
          0  redo size
       2127  bytes sent via SQL*Net to client
        647  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
         31  rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace off
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into my_all_objects
  2  ( owner, object_name, object_type, object_id, created, last_ddl_time )
  3  values
  4  ( 'New Owner', 'New Name', 'New Type', 1111111, sysdate, sysdate );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace traceonly
ops$tkyte@ORA817DEV.US.ORACLE.COM> select owner, count(*)
  2    from my_all_objects
  3   group by owner;

32 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=59 Card=31 Bytes=155)
   1    0   SORT (GROUP BY) (Cost=59 Card=31 Bytes=155)
   2    1     TABLE ACCESS (FULL) OF 'MY_ALL_OBJECTS' (Cost=10 Card=22832 Bytes=114160)




Statistics
----------------------------------------------------------
        121  recursive calls
         72  db block gets
        357  consistent gets
          0  physical reads
      10352  redo size
       2173  bytes sent via SQL*Net to client
        647  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
         32  rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace off
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_mview.refresh( 'my_all_objects_aggs' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace traceonly
ops$tkyte@ORA817DEV.US.ORACLE.COM> select owner, count(*)
  2    from my_all_objects
  3   group by owner;

32 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=31 Bytes=310)
   1    0   TABLE ACCESS (FULL) OF 'MY_ALL_OBJECTS_AGGS' (Cost=1 Card=31 Bytes=310)




Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
        319  consistent gets
          0  physical reads
          0  redo size
       2173  bytes sent via SQL*Net to client
        647  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         32  rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace off
 

Toms, I try your demonstration, and successfully.
the materialized view can use after refresh.
but.
If I include sum function to materialized view,
( ....
as
select owner, count(*), sum(object_id)
...
)
after refresh, this query does not use materialized view.
Why ?

thanks tom, for your advice

best regards

sigh, still no complete example.

I'll guess.  You simply added sum(object_id) to the MV and did nothing else.

You cannot fast refresh that one, your refresh didn't refresh.  You need to add this as well:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create snapshot log on my_all_objects with rowid ( owner<b>, object_id</b> ) including new values;

Materialized view log created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> create materialized view my_all_objects_aggs
  2  build immediate
  3  refresh fast
  4  enable query rewrite
  5  as
  6  select owner, count(*), <b>count(object_id),</b> sum(object_id)
  7    from my_all_objects
  8   group by owner
  9  /

Materialized view created.


Then the view can fast refresh, the refresh will actually refresh (check user_mviews to verify that) and the query rewrite will work.

Refer to the data warehousing guide, chapter on MV's.  It goes into what must be included in the MV in order to fast refresh. 

see oracle bug no 2088032

Dear Tom,
I really confused about join and aggregates function in materialized view.
I read in oracle documentation part Materialized view, they say :
If want materialized view can refresh fast, count(*) must be include with sum(expr)

I create demonstration table like this :

drop table sales;
drop table dept;
create table dept(
deptno varchar2(10), location varchar2(30),
constraints dept$pk primary key(deptno));

create table sales(
nobukti varchar2(5), tanggal date,
deptno varchar2(10), qty number,
constraints sales$pk primary key(nobukti),
constraints sales$deptno foreign key(deptno)
references dept(deptno));

create snapshot log on dept with
rowid(deptno, location) including new values;

create snapshot log on sales with
rowid(nobukti,tanggal,deptno,qty);

drop materialized view sales_mv;

create materialized view sales_mv
build immediate
refresh fast
enable query rewrite
as
select dept.location, sum(sales.qty), count(sales.qty), count(*)
from dept, sales
where dept.deptno = sales.deptno
group by dept.location;

after I refresh :
exec dbms_mview.refresh('sales_mv','F');
select * from user_mviews;

I look in column AFTER_FAST_REFRESH is STALE.
So my query does not use this materialized view.

What I missing in this case ?. I already add count(*) and sum(expr) in materialized view declaration, like in oracle documentation

Best regards



Evara Samsyiar

Tom,

We are writing a reporting system, and we have decided to summarize the data using MVs, so query time would be nearly instantaneous. However, when we enter a new record into the database, the insert time is long because at every new DML we manually refresh the MVs. That's because we cannot tolerate any staleness. That was the tradeoff. This was very useful because our system is very query-intensive, with little DML. Therefore, we had to find some way to speed up a liitle the data entries. We are now using 8.1.7 and are migrating to 9.2.

Ideally we would like to use ON COMMIT, so we would not have to manually refresh our MVs. However, we have a lot of restrictions telling us that we should use ON DEMAND, like multiple joins, aggragate funnctions, etc.

Q1. One thing we were not able to test and maybe you could tell us is, in case we use ON COMMIT, would then this refresh be carried out "in background", that is, so I would have my prompt immediately back after the COMMIT in my session, not having to wait for the refresh to complete ?

Q2. In case the refresh occurs in background, can I somehow fragment my MV select in multi-tier views so I could get rid of the restrictions and be able to use ON COMMIT ?

Q3. My final attempt is using a "dirty" approach. I have created a procedure that commits and refreshes the MVs. This refresh is done by using Java to call a background UNIX process that refreshes the MVs. It works, but I would like to do it in the database. Do you see any other solution ?

Thank you very much !

Dear tom
Thanks for your adviced.
This is my script to demonstrated about fast refresh on materialiazed view. I create this script refer from your answer.

drop table sales;
drop table dept;
create table dept(
deptno varchar2(10),
location varchar2(30),
constraints dept$pk primary key(deptno));

create materialized view log on dept
with rowid (deptno, location)
including new values;

analyze table dept compute statistics
for table for all indexes for all indexed columns;

create table sales(
nobukti varchar2(10), tanggal date,
deptno varchar2(10), qty number default 0,
constraints sales$fk foreign key(deptno)
references dept(deptno));

create materialized view log on sales
with rowid(nobukti,tanggal,deptno,qty)
including new values;

analyze table sales compute statistics
for table for all indexes for all indexed columns;

drop materialized view sales_mv;

create materialized view sales_mv
build immediate
refresh fast
enable query rewrite
as
select a.location, count(*) as cnt,
count (b.qty) as cnt_qty, sum(b.qty) as sum_qty
from dept a, sales b
where a.deptno = b.deptno
group by a.location;

analyze table sales_mv compute statistics
for table for all indexes for all indexed columns;

alter session set query_rewrite_enabled = true;
alter session set query_rewrite_integrity = enforced;

I test this script in Oracle 817.
I look that fast refresh does not work in oracle 817.
I test again, again and again for 3 days before.
I frustation why my materialized view cannot refresh using fast refresh method ?

And then, I try to test in Oracle 901...beautifull..
My materialized view can fast refresh successfully :)

So, What difference in oracle 817 and oracle 901 about materialized view fast refresh method.

Please tell me, what I wrong in this test ?

Best regards

Dear Tom,
U'r response was useful. I have a different issue . Pls throw some light on this
In my development environment I created a materialized view as
CREATE MATERIALIZED VIEW cc_cdd_view
BUILD IMMEDIATE
REFRESH FORCE ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT CK006.CK006_CC_CODE, CK006.CK006_CC_NAME,ck006.ck006_modern_cc,CK001.CK001_CDD_CODE, CK001.CK001_CDD_DESC
FROM CK006_CC_MST CK006,
CK009_GRC_CONSTCY_MST CK009,
CK008_CDC_GRC_MST CK008,
CK007_CDD_CDC_MST CK007,
CK001_CDD_MST CK001
WHERE CK006.CK004_CONSTCY_CODE = CK009.CK004_CONSTCY_CODE
AND CK009.CK003_GRC_CODE = CK008.CK003_GRC_CODE
AND CK008.CK002_CDC_CODE = CK007.CK002_CDC_CODE
AND CK007.CK001_CDD_CODE = CK001.CK001_CDD_CODE;

Here this user is granted with "create materialized view" and "query rewrite" in addition to the connect and resource roles. The init parameter values related to this are kept as
QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED. Here I did not create any materialized view logs. Whenever any change is there in any of the related tables it is getting properly reflected in the materialized view immediately after commit.
In my production server in addition to all these privilages ,DBA role is also granted . Now when I try to create the above mentioned materialized view it gives the error, "ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view".
What could be the reason that it is happening only in Production server ?. Is there any more settings to be done for creating materialized views ?.

When I executed

select * from v$version;, I got the same result from development and production as

Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for Solaris: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

So any other possibilities ???

in the mv there are some sum(decode(....))?

in the materialized view, there are some sum(decode()) like:

sum(decode(col1,1,col2,2,col3,3,col4....))?

The reason I asked is this sum can be sum(col2),sum(col3),sum(col4)..., depending on the values of col1. To make fast refresh possible, we need to include count(*), count(col1),count(col2),count(col3)...? Or it is just impossible to do fast refresh at all?

What to do you mean by stale tolerated.I have gone thru the oracle documentation still i am not getting clear answer.


Thx
Bharath

Dear Tom,

I searched all topics and looked over Oracle doc I had, there is no where indicated that drop of MV on snapshot site will cause the associated log table on master site purged.

We ran into severe production problem on master site by filling up all archive logs (total of 5GB). By killing the process, the statement which was killed is the deletion of from one of the log table; meanwhile, the drop of MV was knocked off with error msg: ORA-02067: transaction or savepoint rollback required. and drop of MV is the only thing I could identified during that time frame and with the OSUSER for that process.

so my question is: how the 'drop materialized view...' statement on snapshot site trigger the 'delete from mastersite.mlog$...' statement? we had db link between snapshot site and mastersite. what should I do if I want to drop MV table with log table on remote (log table will serve for other snapshot accounts too)?

any suggestion will be appreciated.

last refresh time stamp for the MV (assume on site A) I was trying to drop is on 6/28/01. after that, there are approximately half million records been refreshed via fast refresh mode for other production snapshot site (such as site B). I have no way to identify which records it was going to delete by the time we killed the process.

so from what implicated, it is possible that even though site B has all data refreshed from master log table and still doing daily refresh, when site A tried to drop MV which has not been refreshed for long time, Oracle internally tried to purge data on or before 6/28/01 (about half million) in log table? what is the suggested way to re-organize snapshot(such as moving tablespace, drop and do complete refresh) with log table referenced by multiple sites ? Thanks very much!

Hi Tom,

I have a couple of views which were created using huge tables (2 Gigabyte each), and this views take forever to return the results. The explain plan for them are ok but the problem is connected with the amount of records that they have to scan is a lot. I created two materialized views which resolve the most complicate and common logic of those views and I replace those huge tables in the views with the MV's, but as soon as I implemented the MVs all the batch processes and the applications which need to insert and update the big tables (which are being used by the Mvs) began to experience waits for enqueue, library cache lock, row cache lock, etc. All these sessions were having problems of lock type JI with the materialized view log on those big tables. The solution that I found was dropping the materialized views and logs view and after this everything was ok again.

My question is, if there is an enviroment with a lot of insert/update/deletes is there any way to have MVs refresh on commit without facing lock type JI every time when many transactions need to insert/delete/update a row on the master tables ?

Thanks in advance for any help

Q1.
I created a MV on all_objects table created in my schema
with only the following fields
my_all_objects=(owner,object_name, object_type, object_id)

create materailized view mv_all_obj
build immediate
enable query rewrite
referesh on commit
as
select owner, count(*) from my_all_objects group my owner;

I set compatibility option to 8.1.0
issued ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE
ALTER SESSION SET QUERY_REWRITE_INTEGRITY=ENFOREDE (I tried with all the three options too)

But still my view is not used by the oracle-no query rewriting happens!


Q2.
When trying to create a materialized view I get an error:
"ON COMMIT attribute is incompatible with other options"
When I remove the "REFRESH ON COMMIT" clause from the "CREATE MATERIALZED VIEW" statement, the operation is successfull. But the MV does not get updated after an update on base table.

1) What are the other refresh options.
2) How they are specified/enforced
3) What are the relative adv/disadv

Thanks in advance

Praveen

Hi Tom,

I created a materialized view joining multiple tables. I enabled query rewrite on the materialized view. I am confused on one thing though.

Let's say that, I have 3 tables t1, t2, t3. I joined these 3 tables and created the materialized view and grouped by t2.fld1. Now, when i try to run a query that includes one more field t1.fld1 along with or without the t2.fld1, i see the explain plan and i see that it is not using the MView. That is bowling me over. That means that i can only use the t2.fld1 to be able to use the MView.

I need your help badly on this. Even if you can point me to some docs anywhere on this, i would really appreciate your help...

Thanks

VJ.

I could understand that the detail is lost. I am sorry i did not provide complete information. But here you go...

3 Tables - t1,t2,t3. t2 references t1 and t3 references t2. It is a fanning out one-many relationship (t1->t2->t3)

Now when i created the materialized view, i created it at the t2 level.
Create materialized view x_mv
enable query rewrite
as
select t2.fld1, count(t3.fld2)
from t2, t3
where t2.fld1=t3.fld1
group by t2.fld1.

Now when i write a query like this, it is not using the MView.

Select t1.fld1, t2.fld1,count(t3.fld2)
from t1, t2, t3
where t1.fld1=t2.fld2
and t2.fld1=t3.fld1
group by t1.fld1, t2.fld1

I would expect it to be rewritten to use the MView but it isn't. Please explain, if i am making any mistakes...

Thanks
VJ

Tom,

In your book expert-on-one page 597, materialized views , I have similar situation, but i want to accomplish the same in 805.

Thanks

I created previously so I recreate again.

SQL> grant query rewrite to adm;

Concesi¾n terminada con Úxito.

SQL> DROP materialized view log on adm.personas_me;

Log de rÚplica borrado.

SQL> drop materialized view adm.perci;

RÚplica borrada.

SQL> create materialized view log on adm.personas_me
  2  with ROWID, (per_NOMBRE)
  3  including new values
  4  ;

Log de rÚplica creado.

SQL> create materialized view adm.perci
  2  BUILD IMMEDIATE
  3  refresh fast
  4  ON COMMIT
  5  as select per_NOMBRE,count(*) from adm.personas_me
  6  group by per_NOMBRE;

RÚplica creada.

SQL>
SQL>
SQL> alter system set query_rewrite_enabled = true;

Sistema modificado.

SQL> EXECUTE DBMS_MVIEW.REFRESH('PERCI');

Procedimiento PL/SQL terminado con Úxito.

SQL> select per_nombre,count(*) from adm.PERCI
  2  group by per_nombre;

PER_NOMBRE                            COUNT(*)
------------------------------------ ---------
CARLOS JOSE                                  1
LIGIA YANET                                  1
NO IDENTIFICADO                              1

SQL> select per_nombre,count(*) from adm.PERSONAS_ME
  2  group by per_nombre;

PER_NOMBRE                            COUNT(*)
------------------------------------ ---------
CARLOS JOSE                                  1
LIGIA YANET                                  1
NO IDENTIFICADO                              6

SQL>


It seems that SQL> EXECUTE DBMS_MVIEW.REFRESH('PERCI');
doesn't works, and it didn't gave any error. 

Hi Tom, thanks, I am connect as ADM,
I tried with and without schema

SQL>  EXECUTE DBMS_MVIEW.REFRESH('ADM.PERCI');

Procedimiento PL/SQL terminado con Úxito.

SQL> select per_nombre,count(*) from adm.PERCI
  2     group by per_nombre;

PER_NOMBRE                            COUNT(*)
------------------------------------ ---------
CARLOS JOSE                                  1
LIGIA YANET                                  1
NO IDENTIFICADO                              1

I starting to think this is another bug of my database release. 

Hi Tom,

Is it possible to control the period of staleness when using query rewrite to a Materialized View. For example:

If the data in this stale Materialized View is less than 2 hours stale, then user query rewrite to it, otherwise not.


Thanks.

Hi Tom,

I have a very quick question related to materialized view here.

1. Can we create Mviews over views.
2. Can we create Mviews over views on tables in remote database.
3. What all Mviews (what I mean is FAST or COMPLETE, ON COMMIT or ON DEMAND etc.)we can create in both the above cases.

Thanks in advance

1) yes

ops$tkyte@ORA920> create table t ( x int primary key, y int  );
 
Table created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace view v
  2  as
  3  select * from t where x > 50;
 
View created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> create materialized view mv
  2  as
  3  select * from v;
 
Materialized view created.
 
2) yes

3) complete for mvs of views.  fast will get you


ops$tkyte@ORA920> create materialized view mv
  2  refresh fast
  3  as
  4  select * from v;
select * from v
              *
ERROR at line 4:
ORA-12015: cannot create a fast refresh materialized view from a complex query

on commit will get you
ops$tkyte@ORA920> create materialized view mv
  2  refresh fast on commit
  3  as
  4  select * from v;
select * from v
              *
ERROR at line 4:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view


but


ops$tkyte@ORA920> create materialized view mv
  2  refresh complete on demand
  3  as
  4  select * from v;
 
Materialized view created.

works 

Hi Tom,

One more question which I forgot to post earlier:-

Do we always need a materialized view log while dealing with materialized views ,i.e. even if the refresh type is COMPLETE (not FAST).

Once again thanks for the quick response.

This works in 9.0.1.0 and up.

1. run the $ORACLE_HOME/rdbms/admin/utlxmv.sql
This creates a table called MV_CAPABILITIES_TABLE
2. exec dbms_mview.explain_mview
3. select * from mv_capabilities_table or I use
select capability_name, possible, related_text, msgtxt
from mv_capabilities_table
where mvname = '<MV NAME HERE>';

the second step should in the last post should be

2. dbms_mview.explain_mview ( 'YOUR_VIEW_NAME_HERE' )

I am using oracle 8.1.5 work group server.

While creating materialized view, "refresh on demand" or with schedule (REFRESH START WITH SYSDATE NEXT sysdate+1/24) is working.

But refresh on commit (of) fast refresh is not working.

Pl clarify whether, it is not allowed in 8.1.5 work group server.

Hi Tom,
I use 9i r2 and created Materialized View on 200 million records EVENTS table.
The MV_DD_EVENTS is simple GROUP BY to deduplicate events and is used by smaller 2nd-level MVs. I can't merge deduping with other MVs cause it will make MV non-fast refreshable.

I need to refresh MV_DD_EVENTS after each batch insert wich I want to perform frequently (let's say every 5 minutes 3000-5000 records).
The problem is that refresh takes about 15 minutes regardless of number of records inserted. I don't have visibility into refresh process, autotrace and SQL_TRACE don't show things within, cause it's PL/SQL package.
I have a strong feeling that it spends most of the time doing full table scan of 200 million records, cause I see lot's of IOs via iostat and refresh time varies only slightly if I insert 1K or 500K records (15 minutes vs. 16 minutes).
How do I get a visibility into refresh process to tune it?

I'm pretty confident with my ability to tune the query, but I don't know what's happening in DBMS_MVIEW.REFRESH. Even when I get the query from session that runs DBMS_MVIEW.REFRESH from v$SQLAREA, I can't run it standalone to tune it.

I really need you advice cause I exhausted my troubleshooting skills on this one :(
Am I missing something obvious?

Thank you very much for you help and sorry if I omitted some important details, please let me know.
Appreciate your time,
David

Hi Tom,
Coluld you please explain.
I have a MV, can I write a query to access MV directly or shall I make use of MV in a query?

Thanks

ARC

SQL> create materialized view bbc1_mv
  2  build immediate
  3  refresh on commit
  4  enable query rewrite
  5  as

Error I got while creating:
ORA-00439: feature not enabled: Materialized view rewrite

Version 8.1.5 work group server
 

Will the tables used will get locked when materialized view is refreshing.

Is this applicable to all kind of materialised views?

Tom,
I run sql trace and plsql trace,
here is the output but it has nothing on the actual MV refresh operation.
The trace can't fit into this post, so I provide the link:
</code> http://www.geocities.com/katsapov/oracle/FAST_REFRESH_trace.html <code>

In the end I put sql statement from V$SQLAREA that looks more like the actual refresh operation.

You wrote:
>sql_trace certainl does show us the SQL executed. turn on >sql_trace and lets
>see what it is doing.

I am writing this for 1st time, we have oracle 9.2 database
did this 1st -
DROP MATERIALIZED VIEW LOG ON mwebwork;
CREATE MATERIALIZED VIEW LOG ON mwebwork WITH ROWID, SEQUENCE
(work_id, WORK_ENTITY_TYPE, work_level, work_currency)
INCLUDING NEW VALUES;

work_id is Primary key and no FK in this example.

SQLWKS> CREATE MATERIALIZED VIEW mv_mwebwork
2> BUILD DEFERRED
3> REFRESH FAST ON DEMAND
4> AS
5> select count(*), Work_ID, Work_Level, Work_Entity_Type, Work_Currency
6> from mwebwork
7> where work_entity_type in (3, 4)
8> group by Work_ID, Work_Level, Work_Entity_Type, Work_Currency
9> ;
Statement processed.
SQLWKS> exec dbms_mview.refresh('mv_mwebwork','F');
exec dbms_mview.refresh('mv_mwebwork','F')
*
ORA-00900: invalid SQL statement
SQLWKS> execute dbms_mview.refresh('mv_mwebwork','F');
ORA-12057: materialized view "V52DEVOR"."MV_MWEBWORK" is INVALID and must complete refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 2
Why do I get this error ? What am I missing ?
I really want to do fast refresh.. as these MV are going to have huge number of records to process.. so I want to update on demand ( we will run this say twice a day) and just update things that got changed..
Thanks
Sonali

you did a deferred build?  so the MV doesn't "exist" really.  you need to do a complete FIRST in order to FAST (incrementally -- which may not necessarily be faster then a complete, just incremental) refresh in the future!



but it is funny here -- if work_id is the primary key, to what end is this MV in the first PLACE????  count(*) = 1 in all cases??

ops$tkyte@ORA920LAP> create table mwebwork
  2  ( work_id int primary key,
  3    work_level varchar2(10),
  4    work_entity_type int,
  5    work_currency varchar2(10),
  6    data varchar2(10)
  7  )
  8  /

Table created.

ops$tkyte@ORA920LAP> exec gen_data( 'MWEBWORK', 100 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> update mwebwork set work_entity_type = 3;

100 rows updated.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> CREATE MATERIALIZED VIEW LOG ON mwebwork WITH ROWID, SEQUENCE
  2  (work_id, WORK_ENTITY_TYPE, work_level, work_currency)
  3  INCLUDING NEW VALUES;

Materialized view log created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> CREATE MATERIALIZED VIEW mv_mwebwork
  2   BUILD DEFERRED
  3   REFRESH FAST ON DEMAND
  4   AS
  5   select count(*), Work_ID, Work_Level, Work_Entity_Type, Work_Currency
  6   from mwebwork
  7   where work_entity_type in (3, 4)
  8   group by Work_ID, Work_Level, Work_Entity_Type, Work_Currency
  9  /

Materialized view created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select count(*) from mv_mwebwork;

  COUNT(*)
----------
         0

ops$tkyte@ORA920LAP> exec dbms_mview.refresh( 'MV_MWEBWORK', 'F' );
BEGIN dbms_mview.refresh( 'MV_MWEBWORK', 'F' ); END;

*
ERROR at line 1:
ORA-12057: materialized view "OPS$TKYTE"."MV_MWEBWORK" is INVALID and must complete refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1


ops$tkyte@ORA920LAP> select count(*) from mv_mwebwork;

  COUNT(*)
----------
         0

ops$tkyte@ORA920LAP> exec dbms_mview.refresh( 'MV_MWEBWORK', 'C' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> select count(*) from mv_mwebwork;

  COUNT(*)
----------
       100

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> exec gen_data( 'MWEBWORK', 5 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> update mwebwork set work_entity_type = 3 where work_id not in ( select work_id from mv_mwebwork );

5 rows updated.

ops$tkyte@ORA920LAP> select count(*) from mv_mwebwork;

  COUNT(*)
----------
       100

ops$tkyte@ORA920LAP> exec dbms_mview.refresh( 'MV_MWEBWORK', 'F' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> select count(*) from mv_mwebwork;

  COUNT(*)
----------
       105
 

If I got it, did you say take out count(*)..
If I take out count(*), I get this error.. so I thought that for FAST REFRESH I would need count(*).. even when work_id is PK and its always going to be 1..
SQLWKS> CREATE MATERIALIZED VIEW mv_mwebwork
2> BUILD DEFERRED
3> REFRESH FAST ON DEMAND
4> AS
5> select Work_ID, Work_Level, Work_Entity_Type, Work_Currency
6> from mwebwork
7> where work_entity_type in (3, 4)
8> ;
from mwebwork
*
ORA-23415: materialized view log for "V52DEVOR"."MWEBWORK" does not record the primary key

Other question, how come fast refresh which is incremental is same as complete refresh ( which is going to delete everything 1st and then readd everything) ?

What does query rewrite enabled do ? I read lots of oracle documents, but did not get what exactly it does.

Thanks again

Consider a situation where an OLTP environment switches temporarily to Batch environment for conversion. Huge volume of data will be inserted and updated for this short span of time. There are MVs on these tables. Will switching off the MV refresh during this period help? After the conversion we can refresh the MVs. (Similar to indexes being disabled and enabled before and after heavy insert.

If so, how to do this?

Will I be able to use a materialized view if I have a user defined function in the select clause of the query ?

Assuming the materilized view is replicating data, will I be able to query the mv than the underlying base tables (ie without the need for setting query_rewrite) ?

Hi Tom,

Is there any limitation on using mv in RBO without query_rewrite/query_rewrite_integrity not set? If so I am planning to use the mv as a data aggregation tool whereby on EOD I can run dbms_job to populate the mv in an OLTP environment. Can you think of any pitfalls to this approach ?


orcl9i> drop snapshot big_table_mv
2 /

Materialized view dropped.

Elapsed: 00:00:00.03
orcl9i> drop table big_table
2 /

Table dropped.

Elapsed: 00:00:00.00
orcl9i>
orcl9i> create table big_table as select * from all_objects
2 /

Table created.

Elapsed: 00:00:01.09
orcl9i> alter session set query_rewrite_enabled=true;

Session altered.

Elapsed: 00:00:00.00
orcl9i>
orcl9i> alter session set query_rewrite_integrity=TRUSTED;

Session altered.

Elapsed: 00:00:00.00
orcl9i>
orcl9i> create or replace function foo( x in varchar2 ) return
2 varchar2
3 DETERMINISTIC
4 as
5 begin
6 return x;
7 end;
8 /

Function created.

Elapsed: 00:00:00.00
orcl9i>
orcl9i> set autotrace traceonly
orcl9i> select foo(status), count(*) from big_table group by status;

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'BIG_TABLE'




Statistics
----------------------------------------------------------
22 recursive calls
0 db block gets
360 consistent gets
345 physical reads
0 redo size
446 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

orcl9i>
orcl9i> create materialized view big_table_mv
2 build immediate
3 enable query rewrite
4 as
5 select foo(status), count(*)
6 from big_table
7 group by status
8 /

Materialized view created.

Elapsed: 00:00:00.03
orcl9i>
orcl9i> analyze table big_table_mv compute statistics;

Table analyzed.

Elapsed: 00:00:00.00
orcl9i>
orcl9i> variable x varchar2(20);
orcl9i> exec :x := 'VALID';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
orcl9i>
orcl9i> set autotrace traceonly
orcl9i> select foo(status), count(*) from big_table group by status;

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'BIG_TABLE'




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
349 consistent gets
345 physical reads
0 redo size
446 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

orcl9i>
orcl9i>
orcl9i>

Quote "beyond the fact that MV's don't kick in UNLESS you are using the CBO, RBO does not "seem them"
". Could you please clarify why MV's cannot be used directly in RBO as a summary table mechanism. I will not need query_rewrite features.

Hi Tom,

Thanks for replying to the previous threads. I understand having user defined function will nullify the fast refresh option. I have a union-all query with user defined function declared. Just to populate the mv takes about 2 hours. Is there any way to speed up/ overcome this ?

Hi Tom,
Thanks for your reply, I thought of creating the materialized without the udf and creating a view on top of mv with the udf. Hope it will work. Another issue I am having trouble is fast refresh on a union-all query. I did create a mv log, and also included rowid's of all tables accessed, and marker in each query. The tables in from clause come from multiple schemas and I have prefixed with the schema name. Still to no avail. Am I still missing something.

drop materialized view log on emp;
drop materialized view log on emp2;

create materialized view log on emp with rowid;
create materialized view log on emp2 with rowid;

drop materialized view mv_scott_test
/

create materialized view mv_scott_test build immediate refresh complete on demand
as select
1 umarker, a.rowid arowid, b.rowid browid, a.empno, a.ename, a.sal
from emp a, emp2 b
where a.empno = b.empno
union all
select 2 umarker, a.rowid arowid, b.rowid browid, a.empno, a.ename, a.sal
from emp a, emp2 b
where a.empno = b.empno
/

alter materialized view mv_scott_test refresh fast on demand
/
exec dbms_mview.refresh('mv_scott_test');
*
ERROR at line 1:
ORA-12015: cannot create a fast refresh materialized view from a complex query

ops$tkyte@ORA920> create table emp as select * from scott.emp;
 
Table created.
 
ops$tkyte@ORA920> create table emp2 as select * from scott.emp;
 
Table created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> alter table emp add constraint emp_pk primary key(empno);
 
Table altered.
 
ops$tkyte@ORA920> alter table emp2 add constraint emp2_pk primary key(empno);
 
Table altered.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> create materialized view log on emp with rowid;
 
Materialized view log created.
 
ops$tkyte@ORA920> create materialized view log on emp2 with rowid;
 
Materialized view log created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> create materialized view mv_scott_test
  2  build immediate<b>
  3  refresh fast</b>
  4  on demand
  5  as
  6  select
  7  1 umarker, a.rowid arowid, b.rowid browid, a.empno, a.ename, a.sal
  8  from emp a, emp2 b
  9  where a.empno = b.empno
 10  union all
 11  select 2 umarker, a.rowid arowid, b.rowid browid, a.empno, a.ename, a.sal
 12  from emp a, emp2 b
 13  where a.empno = b.empno
 14  /
 
Materialized view created.

ops$tkyte@ORA920> exec dbms_mview.refresh('mv_scott_test');
 
PL/SQL procedure successfully completed.


<b>don't know your version.  don't know your tables.  don't know your keys... but -- I can get it to work</b>

 

Hi Tom,
We have an informatica workflow executing unix scripts that refresh 7 materialized views around the same time( within an interval of 30mins). One particular mview m_trade_fact( the master table had 25 Million rows) gave this error
"
ORA-12012: error on auto execute of job 1094
ORA-12008: error in materialized view refresh path
ORA-12801: error signaled in parallel query server P010
ORA-04030: out of process memory when trying to allocate 56 bytes (cursor work he,kenv: ktfcsnp)
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 189
ORA-06512: at line 1
"
The script itself had this error
"ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1"

Though it gave this error, When I look at all_mviews, the status was VALID and the refresh_type was COMPLETE. ( The script does a fast refresh )

We placed a TAR on this and sent dumps and logs. We havent got any suggestion on this yet except that they referred to Bug 3282771 - which I could not get from metalink.

I added sleep for 10mins (since I know for sure that atleast 5 of the 7 mviews get refreshed within this time ) within the script to refresh m_trade_fact. It seems to be working fine.

My question is:
Is there any Init Parameter that I should tune so that the above error doesnt happen. If so, which one. Anything that I need to look at in particular? Any suggestions?
Thanks for your time and I appreciate your help.

Regards

Hi Tom
Thanks for your response and suggestion. Here are some statistics

We have two boxes.
Both BOX1 and BOX2 have 16GB RAM
BOX1 hosts Informatica and the unix scripts.
BOX2 hosts two databases ODS and DM
It is only the informatica workflow that is being executed at this time. No other process runs.

ODS and DM are setup the same way

Total System Global Area 3223820768 bytes
Fixed Size 735712 bytes
Variable Size 2164260864 bytes
Database Buffers 1056964608 bytes
Redo Buffers 1859584 bytes

PGA Aggregate Target : 1048576000
sort_area_size : 524288
hash_area_size : 1048576

It looks like there is sufficient RAM. Should we increase the sort_area_size? Any suggestion with any numbers?
Thanks so much
Regards

Hi Tom
Thanks for your response and suggestion. Here are some statistics

We have two boxes.
Both BOX1 and BOX2 have 16GB RAM
BOX1 hosts Informatica and the unix scripts.
BOX2 hosts two databases ODS and DM
It is only the informatica workflow that is being executed at this time. No other process runs.

ODS and DM are setup the same way

Total System Global Area 3223820768 bytes
Fixed Size 735712 bytes
Variable Size 2164260864 bytes
Database Buffers 1056964608 bytes
Redo Buffers 1859584 bytes

PGA Aggregate Target : 1048576000
sort_area_size : 524288
hash_area_size : 1048576

It looks like there is sufficient RAM. Should we increase the sort_area_size? Any suggestion with any numbers?
Thanks so much
Regards

Hi Tom

workarea_size_policy is AUTO
and the OS is SunOS 5.8

Thanks & Happy New Year!!!

Hi Tom

Oracle release is :
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production

maximum file descriptor = 4096
maximum size of stack segment = 8192

Tom, I see some ORA-07445 errors in the alertlog (Not due to the MView but some other select statements or index enable parallel. I could see it happening a few times in the last three months ). I will place a separate tar on this with Oracle. I thought I would mention it since this could trigger some reasoning behind the mview failure as well?

Thanks for your time & help.
Regards

Here it is

$ ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 8192
coredump(blocks) 0
nofiles(descriptors) 4096
vmemory(kbytes) unlimited

Thanks & Regards

Thanks Tom.
Documentation says "For workarea policy: Specifying MANUAL may result in sub-optimal performance and poor PGA memory utilization."

Can i update the workarea policy to Manual and increase Sort_area_size to 1M? Should I set this only for the sessions that refresh the materialized views?

I did update the tar with the bug #.
Thanks for your help Tom. Appreciate your time.
Regards

Hi Tom,

we had some ORA-07445 messages in alert log for which i opened another tar. The analyst had referred to 2805580 for patch #. The analyst did mention that
"* Does not occur when pga_aggregate_target is off."

Is it better to have the pga_aggregate_target as OFF?

I hope this isnt diverting from the topic too much.
Also I am going through your archives for more information on this one (Difference between automatic vs manual PGA setting).

Thanks

Hi Tom
That is a bug# which doesnt have much information.
<Bug:2805580> SKGM.C: SEGV IN KGHALF() - LRGI3:TKITPART.TSC:TKITPART3.SQL

1.Tom, does Bug# 3153463 imply that i could set PGA_aggregate_target to more than 1GB and this wouldnt occur?

2.If I set SAS and HAS for this particular session (MView refresh ) and if there are other sessions that are invoked around the same time, will there be issues with PGA workareas?

3.These jobs get executed between 5-7AM. I see that
"over allocation count is = 0" which is ideal right? There are very few onepass-executions starting with PGA size
4mb <= PGA < 8mb. This implies that 1GB of PGA_aggregate_Target Size is good enough, right?

I am sorry to bother you with so many questions. I am looking for some handle here to get this rectified.
Thanks & Appreciate your time

Thanks Tom. You have been responsive and of great support. I did not get much response from the Analyst except that it is an open issue.

I appreciate your time and help.
Thanks again for your suggestions.
Best regards

Hi

Do you know why I am getting this error?

CREATE OR REPLACE PROCEDURE MV_REFRESH(P_OWNER VARCHAR2, P_NOM_VM VARCHAR2)
AS
BEGIN
EXECUTE IMMEDIATE 'alter session set workarea_size_policy = manual';
EXECUTE IMMEDIATE 'alter session set hash_area_size = 67108864';
EXECUTE IMMEDIATE 'alter session set sort_area_size = 67108864';
dbms_mview.refresh(tab => P_OWNER||'.'||P_NOM_VM,
method = 'c');
EXCEPTION
WHEN OTHERS THEN dbms_output.put_line(sqlcode);
END;
/

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/4 PL/SQL: Statement ignored
8/23 PLS-00201: identifier 'METHOD' must be declared

Hello Sir,
After we refresh an MV do we also need to explicitly
analyze the mv for updated stats on table and indexes or this is built in the refresh (dbms_mv.refresh) itself ?

What is the best way to get structural changes reflected in materialized views? If a column gets added to a table we want materialized view to show that column as well, is that even possible without recreating the MV ?

Hi,
I've executed following code,
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

SQL> declare
  2  v_mv_refresh_failures integer:=0;
  3  begin
  4  DBMS_MVIEW.REFRESH_ALL_MVIEWS(v_mv_refresh_failures);
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 617
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 1078
ORA-06512: at line 4


Could you pls. tell me what grants/privs. should i give to this user.

TIA 

Our 8i Database has a particular schema, which is considered very critical and no data loss can be afforded. Previous night's backup are good enough for the other schemas. We do not want the 8i standby feature as it might involve some data loss due to loss of redo logs.

I am toying with the idea of using materialized views (simple one-to-one mappings of views to tables in the relevant schema) with ON COMMIT refresh attribute for replicating the critical schema only, so that in case of a database recovery scenario, we can recover the entire database from backup, and then just drop and import the critical schema from the MVs in the replicated database.

Questions:
1. From your reply to a previous query on this site, I understand that the MV becomes part of a transaction involving base tables at the master site. Does it mean I can be assured of absolutely no data loss for the relevant tables?
2. If the site involves around 1000 transactions per day, would there be any performance degradation as a consequence of using refresh on commit.

Thanks

Hi Tom, sorry if this is a simple question, but I read what I could and can't understand it. Please if you know why.
oracle 9.2.0.2 standard edition

the first time I do a insert, or delete the materialized view is updated after that, never more is updated again. I t ried several times.

thanks.
SQL> alter session set query_rewrite_enabled = true;

Sesi¾n modificada.

SQL> alter session set query_rewrite_integrity = enforced;

Sesi¾n modificada.

SQL>
SQL> drop MATERIALIZED VIEW LOG ON fon.comcli_rw;

Log de rÚplica borrado.

SQL> CREATE MATERIALIZED VIEW LOG ON fon.comcli_rw
  2  --WITH ROWID,PRIMARY KEY (COC_COMISION)
  3  WITH ROWID (coc_codcli,COC_COMISION)
  4  INCLUDING NEW VALUES;

Log de rÚplica creado.

SQL>
SQL> drop MATERIALIZED VIEW FON.MVW_ERP_CTPE;

RÚplica borrada.

SQL> CREATE MATERIALIZED VIEW FON.MVW_ERP_CTPE
  2  BUILD IMMEDIATE
  3  REFRESH FAST ON COMMIT
  4  AS
  5  SELECT coc_codcli,sum(coc_comision) FROM
  6  fon.comcli_rW
  7  group by coc_codcli
  8  /

RÚplica creada.

SQL>
SQL> SELECT * FROM FON.MVW_ERP_CTPE WHERE COC_CODCLI =9479880;

COC_CODCLI SUM(COC_COMISION)
---------- -----------------
   9479880            959.04

SQL> SELECT coc_codcli,sum(coc_comision) FROM FON.comcli_rw WHERE COC_CODCLI=947
9880 group by coc_codcli;

COC_CODCLI SUM(COC_COMISION)
---------- -----------------
   9479880            959.04

SQL>
SQL> INSERT INTO comcli_rw
  2  (COC_CODCLI,COC_CTACORR,COC_FECHA,COC_COMISION,COC_DSC,COC_COMISION_EXITO,C
OC_DSM)
  3  values (9479880,1,to_date('010101','ddmmyyyy'),1000000,'006020927195922',0,
NULL)
  4  /

1 fila creada.

SQL> commit;

Validaci¾n terminada.

SQL> SELECT * FROM FON.MVW_ERP_CTPE WHERE COC_CODCLI =9479880;

COC_CODCLI SUM(COC_COMISION)
---------- -----------------
   9479880           1000959

SQL> SELECT coc_codcli,sum(coc_comision) FROM FON.comcli_rw WHERE COC_CODCLI=947
9880 group by coc_codcli;

COC_CODCLI SUM(COC_COMISION)
---------- -----------------
   9479880           1000959

SQL> delete from comcli_rw where coc_codcli = 9479880 and coc_comision = 1000000

  2  /

1 fila borrada.

SQL> commit;

Validaci¾n terminada.

SQL> SELECT * FROM FON.MVW_ERP_CTPE WHERE COC_CODCLI =9479880;

COC_CODCLI SUM(COC_COMISION)
---------- -----------------
   9479880           1000959

SQL> SELECT coc_codcli,sum(coc_comision) FROM FON.comcli_rw WHERE COC_CODCLI=947
9880 group by coc_codcli;

COC_CODCLI SUM(COC_COMISION)
---------- -----------------
   9479880            959.04

SQL>
SQL>
SQL> select * from dba_mviews;

OWNER                          MVIEW_NAME                     CONTAINER_NAME
------------------------------ ------------------------------ ------------------
------------
QUERY
 QUERY_LEN U
--------------------------------------------------------------------------------
 --------- -
UPDATE_LOG                     MASTER_ROLLBACK_SEG
------------------------------ ------------------------------
MASTER_LINK
--------------------------------------------------------------------------------
--------------------
R REWRITE_C REFRES REFRESH_ BUILD_MOD FAST_REFRESHABLE   LAST_REF LAST_REFR STAL
ENESS
- --------- ------ -------- --------- ------------------ -------- --------- ----
---------------
AFTER_FAST_REFRESH  U U U U U COMPILE_STATE       U
------------------- - - - - - ------------------- -
FON                            MVW_ERP_CTPE                   MVW_ERP_CTPE
SELECT coc_codcli,sum(coc_comision) FROM
        75 N
fon.comcli_rW
group by coc_codcli


N GENERAL   COMMIT FAST     IMMEDIATE DIRLOAD_LIMITEDDML FAST     29ABR2004 UNUS
ABLE
NA                  N N N N N VALID               N


SQL>


 

Thanks Tom,
I think the bug is that it allow fast refresh once ;)

it is a very good useful for developers and learners how to use M.Vs

This view keeps on refreshing , and we dont know why

CREATE MATERIALIZED VIEW VW_OPENFIELD2_MOUSE_CALC
REFRESH FORCE
ON DEMAND
START WITH SYSDATE
AS
SELECT
MT.PROJ_ID,
O.DATASET_ID,
MT.GENOTYPE,
MT.ID AS MOUSE_NUMBER,
MT.LABEL AS MOUSE_LABEL,
O.MOUSE_ID,
TO_CHAR(ROUND(O.TOTDIST/V.SUMDIST*100, 2)) AS PERC_TOT_DIST,
O.SAMPLE,
TO_CHAR(ROUND(O2.SLOPE_VALUE,2)) AS NORM_SLOPE
FROM OPENFIELD2 O, MOUSE_TABLE MT, SMALLADI.OPENFIELD2_CALC O2,
(SELECT O.MOUSE_ID, SUM(O.TOTDIST) AS SUMDIST
FROM
OPENFIELD2 O
WHERE
O.TOTDIST IS NOT NULL
GROUP BY
O.MOUSE_ID
HAVING
SUM(O.TOTDIST) > 0) V
WHERE
O.MOUSE_ID = V.MOUSE_ID AND
O.MOUSE_ID = MT.MOUSE_ID AND
O.MOUSE_ID = O2.MOUSE_ID AND
O.TOTDIST IS NOT NULL
ORDER BY MT.GENOTYPE, MT.ID, O.SAMPLE;

Hi Tom,

I did that before only , the dba_jobs says

select * from dba_jobs where job = 462;

JOB LOG_USER PRIV_USER
---------- ------------------------------ ------------------------------
SCHEMA_USER LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE
------------------------------ --------- -------- --------- -------- ---------
NEXT_SEC TOTAL_TIME B
-------- ---------- -
INTERVAL
--------------------------------------------------------------------------------
FAILURES
----------
WHAT
--------------------------------------------------------------------------------
NLS_ENV
--------------------------------------------------------------------------------
MISC_ENV INSTANCE
---------------------------------------------------------------- ----------
462 LVISION LVISION
LVISION 07-JUN-04 18:16:55 01-JAN-00
00:00:00 7 Y
null
5
dbms_refresh.refresh('"LVISION"."VW_OPENFIELD2_MOUSE_CALC"');
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC
Y='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LAN
GUAGE='AMERICAN' NLS_SORT='BINARY'
0102000200000000

thus we see that the next_date is 1/1/4000 and the job is broken , so I thought maybe the syntax for the materialized view is wrong ??

No its not refreshing now , the question I had is on the creation of this view , it starts refreshing even though we do not have a NEXT in the syntax. Why is this so ??

If I am setting up basic read only MV's, do the Materialized views have to have primary keys? Its just that I put monitoring on all our indexes to see which ones were being used, and all the indexes that show up as NOT used are primary keys on the Materialized views. The materialized views themseleves are just exact copies of the base tables, no sub queries or joins etc.

Thanks

Currently complete refreshes as part of a refresh group, but
hopefully moving to fast refreshes as part of a refresh group.

Thanks

So I could remove them for any that are complete refreshes ?

Thanks

We have materialized view ENI_OLTP_ITEMS and a mview log on it. After every fresh we analyze the mv's using dbms_stats.gather_table_stats(ownname=>'SYS', tabname=>'ENI_OLTP_ITEMS', cascade=> true)

1. When we query dba_tables for last_analyzed column we see the update on MView but the column is empty for Mview log.

2. If we explicitly refresh the mview log it's getting updated.

The question is

(1) Will it be possible to analyze the mview log while the mview is refreshed

(2) Performance team guy is saying since the mview log is not analyzed it could cause performance issues. Do the mview logs analyze/not analyzing really improve/degrade the performance for refresh ?

Sorry Tom,
The actual schema under which we have the MView is different. It's not "SYS", it's "ENI"

dbms_stats.gather_table_stats(ownname=>'ENI',
tabname=>'ENI_OLTP_ITEMS', cascade=> true)

and my questions remain the same.

Thanks in advance!!

Thanks for your suggestions.

Tom,


I want to replicate data to 4 other databases. My db is Oracle 9iR1 and its for a dwh application. Using database link is ruled out.

In this case -- which one i should use.

1. Replication
2. AQ
3. Streams

..or anything else 9i has provided us?

My requirment goes like this -- I have a huge table, in a db that need to be in sync with all other db's located across the world.

Case:

1. I need only one table from a single db to be repliacted across other db's.

2. I need the whole schema objects from the master db to be repliacted across the others db's

3.I need only one table from a single db to be repliacted across other db's + two way distribution of data

4.I need the whole schema objects from the master db to be repliacted across the others db's + two way distribution of data

I the above cases .. which one should be choosen.

Please let me know your inputs on this. I am confused here .. Thanks for your consideration

Tom,

Can you please let us know why "3) streams didn't exist." from the above posting.

tell me why dblinks are "ruled out" -- just to make it harder?

-- because we thought writing a trigger and making an insert or update in other db's are going to be the same as replication in oracle - please correct me if am wrong.

our requirment is everytime there is an insert or update that needs to seen in all other db's

Also --
Snapshot logs impact the performance of the production system.
Snapshot logs impact the maintainence of the production system.

Thanks for your time and valuable service.

Hi, Tom.
We could do paralle select, parallel insert to improve performance. Could we do same thing on MV Refresh? Why or Why not?

Thanks,

I have some strange case and error I think..
I have materialized view -

CREATE MATERIALIZED VIEW mv_plannedhrs_rev
BUILD IMMEDIATE
REFRESH COMPLETE
WITH ROWID
DISABLE QUERY REWRITE
AS
Select work_id, period_ID, AcctCat, nvl(sum(totalplanhrs),0) as planhrs
From (
Select Work_ID, Period_ID, Auth_RevCat_ID as AcctCat,
sum((num_business_days(greatest(AuthAttr_Start_Date,period_Start),least(AuthAttr_Finish_Date, period_finish))
*AuthAttr_Amount)) as totalplanhrs
from xyz
UNION ALL
Select Work_ID, Period_ID, Reserve_RevCat_ID as AcctCat,
sum((num_business_days(greatest(Reserve_Start_Date, period_Start),least(Reserve_Finish_Date, period_finish))
*Reserve_Daily_Factor)) as totalplanhrs
from x,y,z
UNION ALL
select Work_ID, Period_ID, Work_RevCat_ID as AcctCat,
NVL(SUM(Request_Hours/num_business_days(Request_Start_Date, Request_Finish_Date)
*num_business_days(greatest(Request_Start_Date, period_start),least(Request_Finish_Date, period_finish))),0.0)
as totalplanhrs
from xyz
)
group by work_ID, period_ID, AcctCat;

I refresh this view from stored procedure which is going to read it..

I get following error -
EXECUTE pr_GetActualDLRev (552, 552, 1, 'Assignments', 553, '2004-08-19 00:00:00.000')
ORA-00942: table or view does not exist ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832 ORA-06512: at "V52DEVOR.PR_GETACTUALDLREV", line 17 ORA-06512: at line 2


The procedure looks like this -

CREATE OR REPLACE Procedure pr_GetActualDLREV(inHDRID int, inENTID int, inENTTYPE int, strDESCRIPTION varchar2, inURESID int, strCreateDate varchar2)
IS
a number;

BEGIN
dtCreateDate := to_date(strCreateDate,'YYYY-MM-DD "00:00:00.000"');
begin
dbms_mview.refresh('mv_plannedrevenue','C');
dbms_mview.refresh('mv_plannedhrs_rev','C');
end;

delete SBBGPR
where BGPR_GETDL = 10 AND BGPR_HDRID = inHDRID AND BGPR_Type = 10;

delete SBBGITEM
where BGIT_GETDL = 10 AND BGIT_HDRID = inHDRID and BGIT_Type = 10;

/* Find currency and currency table of work item */
select Work_Currency, GLCo_CurrTBL into inCurrency, inCurrTable
from ETGLCmpny, mwebWork
where GLCo_Company = Work_GLCompany AND Work_id = inENTID;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20002, SQLCODE, TRUE);
raise_application_error(-20003, SQLERRM, TRUE);
End pr_GetActualDLREV;
/


If I change these lines
CREATE MATERIALIZED VIEW mv_plannedhrs_rev
BUILD IMMEDIATE
REFRESH COMPLETE
WITH ROWID
DISABLE QUERY REWRITE

TO
CREATE MATERIALIZED VIEW mv_plannedhrs_rev
BUILD IMMEDIATE
REFRESH on demand
WITH ROWID
DISABLE QUERY REWRITE

I do not get error and it works.
Why doesn't refresh complete work and why does on demand work ? Whats the difference ??

I have 2 materialized views used in this procedure and both are refreshed from this procedure and they do not read each other..
dbms_mview.refresh('mv_plannedrevenue','C');
dbms_mview.refresh('mv_plannedhrs_rev','C');

Both of them had

BUILD IMMEDIATE
REFRESH COMPLETE
WITH ROWID
DISABLE QUERY REWRITE

It worked for 'mv_plannedrevenue' view but did not work for
'mv_plannedhrs_rev' view. I am puzzled..

Need entire example -- from start to finish -- terse, yet complete (eg: don't give me pages and pages -- whittle it down to something as small as possible...)

ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;
 
Table created.
 
ops$tkyte@ORA9IR2> CREATE MATERIALIZED VIEW mv
  2  BUILD IMMEDIATE
  3  REFRESH COMPLETE
  4      WITH ROWID
  5          DISABLE QUERY REWRITE
  6  as
  7  select * from emp;
 
Materialized view created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure p
  2  as
  3  begin
  4          dbms_mview.refresh( 'mv', 'c' );
  5  end;
  6  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec p
 
PL/SQL procedure successfully completed.
 

I am finally able to fifure out the difference between the 2 views.. where one works and other doesn't

CREATE MATERIALIZED VIEW mv_plannedhrs_rev
BUILD IMMEDIATE
REFRESH COMPLETE
WITH ROWID
DISABLE QUERY REWRITE
AS
Select work_id, period_ID, AcctCat, nvl(sum(totalplanhrs),0) as planhrs
From (
Select Work_ID, Period_ID, Auth_RevCat_ID as AcctCat,
sum((num_business_days(greatest(AuthAttr_Start_Date,period_Start),least(AuthAttr_Finish_Date, period_finish))
*AuthAttr_Amount)) as totalplanhrs
from x,y, mv_mwebWork
blah blah blah

mv_plannedhrs_rev which has REFRESH COMPLETE clause reads the view mv_mwebWork which has REFRESH ON DEMAND.

So when the data is updated so mv_mwebWork is changed. I get the error on mv_plannedhrs_rev view.
mv_mwebWork is refreshed on scheduled basis ( its a job).
But mv_plannedhrs_rev which reads mv_mwebWork is refreshed from the procedure.

Will this cause this error ( table or view does not exists ??)

Here is what the oracle documentation says
COMPLETE
Specify COMPLETE to indicate the complete refresh method, which is implemented by executing the materialized view's defining query. If you request a complete refresh, Oracle performs a complete refresh even if a fast refresh is possible.

ON DEMAND
Specify ON DEMAND to indicate that the materialized view will be refreshed on demand by calling one of the three DBMS_MVIEW refresh procedures. If you omit both ON COMMIT and ON DEMAND, ON DEMAND is the default.

When I change mv_plannedhrs_rev from refresh complete to refresh on demand it works.

Do I need both to be refresh complete or both refresh on demand ?
I mean both views have to match the way they are refreshed ?

how does it work ?

hi tom,
I have a snapshot with fast refresh type, from a oracle 8.0.3 db to 8.1.5. If the connection between the servers is ok, it works fine. But if there's something wrong with the db link, the refreshing job seems to be blocked, and it can not be stop. Do we have any settings like "timeout"? It will tell the job to stop trying and wait for the next refresh.
Thank you very much.

Hi Tom,
Currently we are having 2 Database applications running on 2 diffrent Servers. Now we will be combining the features of both these Applications into a single application. We will be Upgrading Server 1 to handle the necessary load. Now since Server 2 will be free, I was toying with the idea of making this SERVER 2 as a PURE Reporting Server for the new Application by using Replication and Read Only MV's. So Users will be doing their DML on the upgraded server(Server 1) and the reporting server(Server 2) will be refreshed periodically. Is this a good decision?, or am I better off by creating MV's on Server 1 itself.

Thanks for the quick reply. From Simplicity point of view , yes One Server is fine. What about from performance point?. I mean are there any performance gains to be obtained? I read in Data WareHousing Guide that when using Remote MV's Query Rewrite will not take place.Any workarounds to this problem? Apart from that any other dis-advantages?. Also when I refresh the MV over the link will there be a load on Master Server (ie CPU,Memory) or the Server where the MV will be created.

Thanks

Could you please explain the parameter “parallelism” of DBMS_MVIEW.REFRESH more?

Does it apply to the underlying query of the mview or the DML of the mview itself?

Thank you very much,
but how to make/check refresh faster, especially full refresh (the source query is a join of ~6 tables and some outer joining).

Hey Tom,

If a materialized view is created on a base table with the refresh complete on commit option, and that base table is reloaded via a direct path sql loader truncate, I confirm that the materialized view does indeed refresh as expected.

However, the staleness column of user_mviews does not change to FRESH as I would expect, causing the view to still be marked as stale and not be used for rewrites (because I am not tolerating stale rewrites).

Is this behavior normal in an Oracle 9iR2 EE HPUX environment? Please do not take the time to create an example for me, as I have not provided a test case to you (yet), and am only looking for a yes/no at this point before I proceed.

Thanks.

I created a materialized view on a table.

This materialized view should be refreshed whenever the business date of the application changes.

Hence, the refresh was put in a trigger ( as a pragma).
Very strangely, after the trigger fires, the materialized view status becomes invalid. ( This was found with user_objects table where status = 'INVALID').

By issuing the command in SQLPLUS: ALTER MATERIALIZED VIEW MV compile;
it becomes valid.

However, if this alter statement issued in the trigger did not make the status valid though the trigger did not report any error.

The oracle version is 8.1.7.
Is it a bug in oracle?

create materialized view mv_mvp_se_cust
tablespace master_d1
build deferred
refresh force on demand
disable query rewrite
as (select
.
.
from
.
.
join
.
.

);


There is a mview created like above 

disable query rewrite - If i say disable query rewrite, will my mview ever be used?.
Is it possible to force my query to use the MV

I took this info too

SQL> SELECT capability_name, possible,SUBSTR(related_text,1,8)ASrel_text,
SUBSTR(msgtxt,1,60) AS msgtxt
FROM MV_CAPABILITIES_TABLE
ORDER BY seq;  2    3    4
 
CAPABILITY_NAME                P ASREL_TE MSGTXT
------------------------------ - -------- ------------------------------------------------------------
PCT                            Y
REFRESH_COMPLETE               Y
REFRESH_FAST                   Y
REWRITE                        N
PCT_TABLE                      Y TB_SNB03
PCT_TABLE                      N TB_SNB03 relation is not a partitioned table
PCT_TABLE                      N TB_SNB03 no partition key or PMARKER in select list
REFRESH_FAST_AFTER_INSERT      N DW.TB_SN the detail table does not have a materialized view log
REFRESH_FAST_AFTER_INSERT      N DW.TB_SN the detail table does not have a materialized view log
REFRESH_FAST_AFTER_ONETAB_DML  N          see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML     N          see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
 
CAPABILITY_NAME                P ASREL_TE MSGTXT
------------------------------ - -------- ------------------------------------------------------------
REFRESH_FAST_PCT               Y
REWRITE_FULL_TEXT_MATCH        N          query rewrite is disabled on the materialized view
REWRITE_PARTIAL_TEXT_MATCH     N          query rewrite is disabled on the materialized view
REWRITE_GENERAL                N          query rewrite is disabled on the materialized view
REWRITE_PCT                    N          query rewrite is disabled on the materialized view
  

Hi

I have a question regarding Class I ODS. Since this type of ODS has a lag of 2 to 4 seconds I wonder if we could use Standby Database or Data Guard for this type of ODS?

Is it possible to execute a procedure that would refresh a MV over a db link. If so, can you please highlight the steps to do so?

Thanks

best bet here is to be on the instance where the mv resides in order to refresh it -- for the MV process wants to commit and the commit is supposed to come from the originating site of the transaction. 

to that end, I would suggest you look at using dbms_job to schedule a refresh to happen on the remote site soon after you commit, that is:


SQL> create synonym remote_job for dbms_job@remote_site;
SQL> exec remote_job.submit( :x, 'dbms_mview.refresh( ''xxxx'' );' );
SQL> commit;

soon after, the job queues on the remote site will pick up the job and run it as a local job. (make sure they have job_queue_processes set to >= 1)  

I created a table.

CREATE TABLE test1

(qw VARCHAR2(30) NOT NULL primary key ,
qwe VARCHAR2(10) NOT NULL)

CREATE MATERIALIZED VIEW LOG ON test1


create materialized view test1
build immediate
refresh fast
with primary key
enable query rewrite
as
select * from test1@meidasdb


But then again i was not getting Materialized View refreshed, will i have to refresh explicitly i thought it was implicit process . PLease help.

Tom,
I know that we can do paralle for materialized view refresh.
But while I read Oracle 9.2 release manual, I don't see any
sample of parallel for refresh COMPLETE although some sample of parallel is for refresh FORCE. So I try to some test to see if parallel will speed up COMPLETE refresh.I alter one of our large of materialized view to parallel (degree 8)--the setting for parallel_max_servers is 8 , and then COMPLETE refresh this materialized view . It seems to me no any speedup(it still takes more than 2 hours to COMPLETE refresh as before), and I don't see Oracle uses more than 1 process to refresh it. This materialized view was created based on single table over the database link, the base table was created with NOPARALLEL on master site.

How can we speed up the process of COMPLETE REFRESH -- we
have to use this option since we are practicing desaster recovery while we encounter ORA-12057.

Your help will be appreciated.

CREATE TABLE test1

(qw VARCHAR2(30) NOT NULL primary key ,
qwe VARCHAR2(10) NOT NULL)

CREATE MATERIALIZED VIEW LOG ON test1


create materialized view test1
build immediate
refresh fast
with primary key
enable query rewrite
as
select * from test1@meidb


But then again i was not getting Materialized View refreshed, will i have to
refresh explicitly i thought it was implicit process . PLease help.




Followup:
there is no interval specified on the MV create. See the sql reference for the
options.

you didn't schedule it.

as you clearly pointed out i did scheduling i want to enable my MV refreshed automatically also i am using DBlink ie accessing remote database, thats why the whole reason for going with MV


here is what i did
create materialized view test2
build immediate
refresh fast
start with sysdate
next sysdate+1/10000
with primary key
enable query rewrite
as
select * from test2@meidb

and i am happy to report it works

Your help is greatly appreciated also little doubt
Can i get my MV refreshed without going for scedule and procedure ..also my schedling is 20 seconds ahead of the original table ..is there i way to get it refreshed after every 1 sec ?

"you have to schedule it, you have to say how often.

one second would be a great burden. 5 seconds it the resolution of the job
queues (_job_queue_interval=5). I would consider 20 seconds even to be "too
often"


But if i dont schedule every 1 sec then whole purpose of the MV creation will be lost and MV will be giving stale result to the application ...in order to cut down time in accessing the remote DB we are creating a MV..

is this too strenous to have a refresh after 1 sec?

Also where there is job to be scheduled ? {queues (_job_queue_interval=5). }

Please let me know, As usual thanks in advance...

Really appreciate your fast response.

Hi Tom,

Thank you very much for your previous help. Your oracle expertise is unprecedented.

To simplify the question, we have many different kinds of regional databases and one master database with complex replications mechanism, using many replication groups, all triggers have
if (not dbms_snapshot.i_am_a_refresh) and (not dbms_reputil.from_remote) then
do something.
end if;
end of triggers.
Mostly, we use row-level triggers, basically no statement-level triggers.

Recently, we try to make one regional database independent (much less dependencies from other functional regions), so tables have been moved to master, mviews are on the regions, we have table table_a, table_b and table_c
table_a@master and table_b@master are master tables, materialized views are on the regions.

tia_table_a@master (after insert row-level trigger) get fired, then should insert into table_b@master, which should make remote procedure call to region to insert into table_c@region,
We found tia_table_b@master is not firing at all, we use
if (not dbms_snapshot.i_am_a_refresh) and (not dbms_reputil.from_remote) then
do something
else
do something else
end;
in the tia_table_b trigger.

The original triggers (based upon the original design) have been working for five to ten years, we just move around the tables and materialized views, then tia_table_b stop firing.

DBAs and on site long-term oracle consultants and our developers each have at least ten years of oracle experience, the whole application is huge and working for years.

We have multiple replication groups, table_a and table_b are under different replication groups,

We suspect some replication related parameters are not set up correctly, I am not talking about oracle parameters.

It is unlikely to be a simple problem, maybe some higher level parameters of replication groups are not set up correctly, based upon your instincts, what could go wrong? Please advise a list, we will check through all.

Thank you very much for help. I know you usually need some concrete stuff. We need some ideas to proceed.

Thanks, Tom.

Hi Tom,

I'm building a datawarehouse on a Oracle9iR2 Enterprise Edition and I recommend the use of MV's and OLAP features. I was building a test envirorment (fact and dimension tables, RI, dimensions, hierarchies, stats gathered with DBMS_STATS and cubes built with OEM).

But when I try to validate the dimension, do a DBMS_OLAP.recommend_mv or even create an id with DBMS_OLAP I got an ORA-30475: Feature not enabled.


ERROR en línea 1:
ORA-30475: función no activada: Summary Advisor
ORA-06512: en "SYS.DBMS_SYS_ERROR", línea 86
ORA-06512: en "SYS.DBMS_SUMMARY", línea 49
ORA-06512: en "SYS.DBMS_SUMMARY", línea 437
ORA-06512: en línea 1

Do I'm missing anything? Is there something we have to purchase or enable or something about licences? Or am I doing something wrong? (I could create dimensions, even cubes with that database!).

Thanks in advance, Tom,
Javier


Here is the code...

SQL> show user
USER es "DW_VIAJES"
SQL> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
DW_VIAJES                      CONNECT                        NO  YES NO
DW_VIAJES                      DBA                            NO  YES NO
DW_VIAJES                      OLAP_DBA                       NO  YES NO
DW_VIAJES                      OLAP_USER                      NO  YES NO
DW_VIAJES                      RESOURCE                       NO  YES NO

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for HPUX: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

SQL> select comp_name, version, status from dba_registry;

COMP_NAME                                VERSION                      STATUS
---------------------------------------- --------------------------- -----------
Oracle9i Catalog Views                   9.2.0.6.0                    VALID
Oracle9i Packages and Types              9.2.0.6.0                    VALID
JServer JAVA Virtual Machine             9.2.0.6.0                    VALID
Oracle XDK for Java                      9.2.0.8.0                    VALID
Oracle XML Database                      9.2.0.6.0                    VALID
OLAP Analytic Workspace                  9.2.0.6.0                    LOADED
Oracle OLAP API                          9.2.0.6.0                    UPGRADED
OLAP Catalog                             9.2.0.6.0                    VALID

8 filas seleccionadas.

SQL> variable ident number
SQL>  exec dbms_olap.create_id(:ident);
BEGIN dbms_olap.create_id(:ident); END;

*
ERROR en línea 1:
ORA-30475: función no activada: Summary Advisor
ORA-06512: en "SYS.DBMS_SYS_ERROR", línea 86
ORA-06512: en "SYS.DBMS_SUMMARY", línea 49
ORA-06512: en "SYS.DBMS_SUMMARY", línea 109
ORA-06512: en línea 1




SQL> exec dbms_summary.validate_dimension('GEOGRAFIA','DW_VIAJES',false,true);
BEGIN dbms_summary.validate_dimension('GEOGRAFIA','DW_VIAJES',false,true); END;

*
ERROR en línea 1:
ORA-30475: función no activada: Summary Advisor
ORA-06512: en "SYS.DBMS_SYS_ERROR", línea 86
ORA-06512: en "SYS.DBMS_SUMMARY", línea 49
ORA-06512: en "SYS.DBMS_SUMMARY", línea 437
ORA-06512: en línea 1

 

Thanks, Tom.

I think that software was upgraded to EE from SE after I did install jvm and xml. I found in metalink something about reinstall jvm and xml.

So, I'm on this. I will review this question to tell if it did solve this problem (I couldn't find much else documentation about this situation). Maybe this would be useful for other people.

Thanks for all, Tom.

Hi Tom,

You were right. It was an upgrade problem.

The software installed was Oracle9iR2SE and I did create the database manually, launching the scripts to create JVM, XKD, etc. So, when the script is going to create the java classes related to "Summary advisor", the script initqsma.sql checks Oracle edition and disables functionality of Summary Advisor in case of Standar Edition.

When I needed OLAP and DM option, the software was upgraded to Oracle9iR2EE, but java envirorment still had this "feature" disabled.

Launching this script initqsma.sql would be enough, but I prefered to follow this metalink note to reload JVM in Oracle9i.

</code> http://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=209870.1&blackframe=1 <code>

So, everything's working ok now.

Thanks for all,
Javier

Hi Tom,

Your previous replies have been extremely useful.

One thing I keep reading a lot about is partition change tracking (PCT) when looking at materialized views and the different ways of refreshing of materialized views but am not sure what it. My question is if you could give a simple example of how this is done and how it works and when would use it.

To give a bit of background is I am looking at is a table I want to make into a materialized view that contains data I think I want to partition based on a date column, that is for each day I have thousands of rows. I might want to partition based on a day basis or a week basis.

I am using Oracle 9iR1, unfortunately we are not looking to upgrade in the near future.

Why refreshing with a certain interval makes any sence at all? Any programming logic based upon (physical) time is error prone. Refresh fast on commit. Deprecate all the other options. Period.

Why refresh on a time interval?

Because sometimes a business wants stable, aggregated reports, refreshed at midnight, 6am, midday, and 6pm for example, on a transactional table.

Ask me how I know :)

Hi Tom
The above discussion is excellent.
But I have one basic question

We are planning to create materialized view using db link refresh on commit

We need to know whether the transaction will be in hold until the materialized view has refreshed.
Both are same transaction scope or not. Or else it will write in Materialized log and realeased the transaction.

Thanks

"umm, can you spell distributed"

Distributed transactions, what about them?

"can you spell not everything is refresh fast"

Why not?

"we want our data to remain constant from 9-5 while reporting on it"

select as of timestamp 5pm

"we cannot take the hit on the transactional system
that on commit
refreshes would impose"

at the expence of having to deal with inconsistent data

Hi Tom

I have materialized view replication setup with the 'Fast' refresh option
between 2 sites and 2 databases with db_links on Oracle 9i R2.

The situation is like this:

DB A (Site A) : Master site table t1 and mlog$_t1 materialized view log.
DB A (Site A) : Fast Refresh Materialized view MV_T1_1 using the log mlog$_t1
DB B (Site B) : Fast Refresh Materialized view MV_T1_2 using the same log mlog$_t1


DB A (Site A - Master Site)
T1
|
mlog$_t1
| |
DB A(Site A Materialized View site) DB B (Site B Materialized View site)
MV_T1_1 MV_T1_2

My question is:

If the Site B goes down with the intention to never come back, basically a 'disaster'.

My mlog$_t1 table still keeps recording all the changes which happen to T1 with
the intention to propagate them to MV_T1_2 on site B which doesn't exist.

Basically the number of the records in the mlog$_t1 log is constantly increasing although
the refresh jobs on Site A and MV_T1_1 happen periodically without problems.

I want to prevent this increasing of the mlog$_t1, I can't unregister the Materialized view on the the dead
Site B neither I can purge the log since I can't see it in the USER_REGISTERED_MVIEWS.

My other options is to TRUNCATE the logs but that won't 'unregister' the Materialized view
of the dead site B. ( I tested this.)

The last option is to drop the mlog$_T1 after refreshing it with the current working MV_T1_1
and re-creating it quickly. I tried this on a test situation it seems to work.

Table T1 and MV_T1_1 are production 'live' sites.
Table T1 is updated at unpredictable times.

My worry is if I drop the mlog$_t1 and at the same minute there is an update on T1,
will the new log miss it. Will it save it to put it in the new mlog$_T1?
Can this corrupt data?
I am aware that I will need to do a full refresh of the fast MV_T1_1 after I drop the mlog$_T1, the
table is not big.


What would be the best approach?

Thank you very much for all your help.


Best Regards

Kubilay

Hi Tom

Yes, I do query the master sites USER_REGISTERED_MVIEWS but I don't see the materialized view which I want to unregister.

Also, my mistake!
Just found out that although the materialized view MV_T1_1 on Site A refresh with the 'FAST' method, the materialized view on Site B MV_T1_2 (now the dead site) was not refreshing with the 'FAST' method but with the 'COMPLETE' method, but is still allowed to use the log mlog$_T1 table?!

Is there a way to unregister the one which uses the 'COMPLETE' refresh method still? It is not displayed in the USER_REGISTERED_MVIEWS though on master site A.

Many Thanks!

Kubilay

Hi Tom,

In the document "Oracle® Database New Features Guide
10g Release 2 (10.2)" it is written that a new feature called "Partition Change Tracking Refresh without Materialized View Logs" has been introduced in 10gR2.

I was wondering what was new about it because I remember having done a fast refresh of a MV over PCT without using any MV log already on Oracle 9.2.

Right now I don't have any 9iR2 DB available so I made a test on 10gR1. Based on the script below it seems to me that at least in 10gR1 it is already possible to make a fast refresh without any MV log.

Regrads
Maurice



SQL> CREATE MATERIALIZED VIEW MV_SALES
  2  PARTITION BY RANGE (TIME_ID)
  3  ( 
  4    PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE(' 200001', 'YYYYMM'))
  5      NOLOGGING, 
  6    PARTITION SALES_Q4_2001 VALUES LESS THAN (TO_DATE(' 200201', 'YYYYMM'))
  7      NOLOGGING, 
  8    PARTITION SALES_Q4_2002 VALUES LESS THAN (TO_DATE(' 200301', 'YYYYMM'))
  9      NOLOGGING, 
 10    PARTITION SALES_Q4_2003 VALUES LESS THAN (TO_DATE(' 200401', 'YYYYMM'))
 11      NOLOGGING
 12  )
 13  COMPRESS
 14  AS
 15  SELECT TIME_ID, SUM(AMOUNT_SOLD)
 16  FROM SH.SALES
 17  GROUP BY TIME_ID;

Materialized view created.

SQL> SELECT *
  2  FROM DBA_MVIEW_LOGS
  3  WHERE MASTER = 'SALES';

no rows selected

SQL> INSERT INTO SALES VALUES
  2  (13 ,2380 ,TO_DATE(' 20010204', 'YYYYMMDD') ,3 ,999 ,1 ,1232.16);

1 row created.

SQL>
SQL> COMMIT;

Commit complete.

SQL> select SUBOBJECT_NAME, value from v$segment_statistics
  2  where owner = 'SH'
  3  and OBJECT_NAME = 'SALES'
  4  and STATISTIC_NAME = 'logical reads'
  5  order by SUBOBJECT_NAME
  6  /

SUBOBJECT_NAME                      VALUE
------------------------------ ----------
SALES_1995                             16
SALES_1996                             32
SALES_H1_1997                          16
SALES_H2_1997                           0
SALES_Q1_1998                         256
SALES_Q1_1999                         336
SALES_Q1_2000                         336
SALES_Q1_2001                         496
SALES_Q1_2002                           0
SALES_Q1_2003                          32
SALES_Q2_1998                         224

SUBOBJECT_NAME                      VALUE
------------------------------ ----------
SALES_Q2_1999                         288
SALES_Q2_2000                         304
SALES_Q2_2001                         352
SALES_Q2_2002                           0
SALES_Q2_2003                           0
SALES_Q3_1998                         272
SALES_Q3_1999                         336
SALES_Q3_2000                         320
SALES_Q3_2001                         352
SALES_Q3_2002                          16
SALES_Q3_2003                           0

SUBOBJECT_NAME                      VALUE
------------------------------ ----------
SALES_Q4_1998                         320
SALES_Q4_1999                         336
SALES_Q4_2000                         304
SALES_Q4_2001                         384
SALES_Q4_2002                          16
SALES_Q4_2003                          32

28 rows selected.

SQL> begin
  2   dbms_mview.refresh('MV_SALES','F');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select SUBOBJECT_NAME, value from v$segment_statistics
  2  where owner = 'SH'
  3  and OBJECT_NAME = 'SALES'
  4  and STATISTIC_NAME = 'logical reads'
  5  order by SUBOBJECT_NAME
  6  /

SUBOBJECT_NAME                      VALUE
------------------------------ ----------
SALES_1995                             16
SALES_1996                             32
SALES_H1_1997                          16
SALES_H2_1997                           0
SALES_Q1_1998                         256
SALES_Q1_1999                         336
SALES_Q1_2000                         336
SALES_Q1_2001                         608
SALES_Q1_2002                           0
SALES_Q1_2003                          32
SALES_Q2_1998                         224

SUBOBJECT_NAME                      VALUE
------------------------------ ----------
SALES_Q2_1999                         288
SALES_Q2_2000                         304
SALES_Q2_2001                         352
SALES_Q2_2002                           0
SALES_Q2_2003                           0
SALES_Q3_1998                         272
SALES_Q3_1999                         336
SALES_Q3_2000                         320
SALES_Q3_2001                         352
SALES_Q3_2002                          16
SALES_Q3_2003                           0

SUBOBJECT_NAME                      VALUE
------------------------------ ----------
SALES_Q4_1998                         320
SALES_Q4_1999                         336
SALES_Q4_2000                         304
SALES_Q4_2001                         384
SALES_Q4_2002                          16
SALES_Q4_2003                          32

28 rows selected.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod
PL/SQL Release 10.1.0.4.0 - Production
CORE    10.1.0.4.0      Production
TNS for 32-bit Windows: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production 

     

Tom,
we created a MV by FAST REFRESH ON COMMIT while using oracle 8.1.7. Now we are using oracle 9.2.0.4. After we drop this MV, we can't use same code to create it and get 
the error of ORA-01031: insufficient privileges. If we remove 'ON COMMIT', the MV can be created. Please see below:

SQL> CREATE MATERIALIZED VIEW MIRMVU.MVU_EXCHANGE_RATES
  2  TABLESPACE MIR_MVU_A
build immediate
  3    4  REFRESH FAST ON COMMIT
WITH ROWID
  5    6  AS    
 SELECT
  7    8    exchange_rates.EXCHRATE_ID,
  CURRENCIES.CURRENCY_CODE,
  9   10    exchange_rates.EXCHANGE_RATE_DT,exchange_rates.RATE_PER_USD_NUM,
  exchange_rates.RATE_PER_GBP_NUM,exchange_rates.RATE_PER_EUR_NUM,
 11   12    exchange_rates.DELETED_FLG, exchange_rates.LAST_UPDATE_DT ,
  exchange_rates.rowid RATE_RID,CURRENCIES.rowid CURR_RID
 13   14    FROM core.exchange_rates,
       core.CURRENCIES
 15   16    WHERE CURRENCIES.CURRENCY_ID=exchange_rates.CURRENCY_ID;
  FROM core.exchange_rates,
            *
ERROR at line 14:
ORA-01031: insufficient privileges


SQL> CREATE MATERIALIZED VIEW MIRMVU.MVU_EXCHANGE_RATES
  2  TABLESPACE MIR_MVU_A
  3  build immediate
REFRESH FAST 
  4    5  WITH ROWID
AS      
  6    7   SELECT
  exchange_rates.EXCHRATE_ID,
  8    9    CURRENCIES.CURRENCY_CODE,
  exchange_rates.EXCHANGE_RATE_DT,exchange_rates.RATE_PER_USD_NUM,
 10   11    exchange_rates.RATE_PER_GBP_NUM,exchange_rates.RATE_PER_EUR_NUM,
  exchange_rates.DELETED_FLG, exchange_rates.LAST_UPDATE_DT ,
 12   13    exchange_rates.rowid RATE_RID,CURRENCIES.rowid CURR_RID
  FROM core.exchange_rates,
 14   15         core.CURRENCIES
 16    WHERE CURRENCIES.CURRENCY_ID=exchange_rates.CURRENCY_ID;

Materialized view created.

We really need to have it as refresh on commit. I guess
Oracle 9i has some restriction for refresh on commit. What is the additional privilege for creating such kind of MV?

Thanks.




 

This user has select any table privilege. We only drop this MV in development database. But in staging and production,
this MV is still there. We compared this user privileges on
all dev/staging/prod, no difference. Also I just try login
as this user, create different name MV by same code in staging environment(because the original on is existing), I get ORA-01031 also. When don't use ON COMMIT, it is OK.

It is very strange.

Tom,
I have created 2 test cases based on different version of Oracle softwear (the 8.1.7 database was down and zipped
for saving disk space -- we don't use it for our application now. I just uncompressed the files and start the database in order to create the test case. That is why I delay to post results here). The results indicated my guess is right -- 9.2.0.4 requires something more about privilege for ON COMMIT. But I just don't know what kind of additional privilege this user needs to have in 9.2.0.4 because it has select any table privilege,
and if no 'ON COMMIT' in create statement, the MV can be created.

Please see all from my 2 test cases below:

<dbdev1:oracle:dev1:/appl/oracle/admin/bin/log> sqlplus /

SQL*Plus: Release 9.2.0.4.0 - Production on Fri Sep 30 17:59:11 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> Create user  "TESTMVU" identified by TEST
  2    DEFAULT TABLESPACE MIR_MVU_A
  TEMPORARY TABLESPACE TEMP_DEFAULT
  PROFILE DEFAULT
  QUOTA UNLIMITED ON MIR_MVU_A
  ;
grant ALTER SESSION to testmvu;
  3    4    5    6  grant CREATE SESSION to testmvu;
grant CREATE SNAPSHOT to testmvu;
grant CREATE TABLE to testmvu;
grant CREATE VIEW to testmvu;
grant SELECT ANY TABLE to testmvu;
User created.

SQL> 
Grant succeeded.

SQL> 
Grant succeeded.

SQL> 
Grant succeeded.

SQL> 
Grant succeeded.

SQL> 
Grant succeeded.

SQL> 

Grant succeeded.

SQL> Create user  "TESTCORE" identified by TEST
  2    DEFAULT TABLESPACE MIR_DATA_A
  TEMPORARY TABLESPACE TEMP_DEFAULT
  QUOTA UNLIMITED ON MIR_DATA_A
  QUOTA UNLIMITED ON MIR_INDEX_A
  ;
grant ALTER SESSION to testcore;
grant CREATE SESSION to testcore;
grant CREATE SNAPSHOT to testcore;
grant CREATE TABLE to testcore;
  3    4    5    6  
User created.

SQL> 
Grant succeeded.

SQL> 
Grant succeeded.

SQL> 
Grant succeeded.

SQL> 
Grant succeeded.

SQL> grant select any table to testcore;

Grant succeeded.

SQL> conn testcore/test
Connected.
SQL> create table rates as select * from core.exchange_rates where rownum<101;

Table created.

SQL> create table curr as select * from core.currencies where rownum <101;

Table created.

SQL> desc rates 
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EXCHRATE_ID                               NOT NULL NUMBER
 CURRENCY_ID                               NOT NULL NUMBER
 EXCHANGE_RATE_DT                          NOT NULL DATE
 RATE_PER_USD_NUM                                   NUMBER
 RATE_PER_GBP_NUM                                   NUMBER
 RATE_PER_EUR_NUM                                   NUMBER
 DELETED_FLG                               NOT NULL VARCHAR2(1)
 LAST_UPDATE_DT                            NOT NULL DATE

SQL> alter table rates add constraint pk_rate_id primary key (exchrate_id) using index tablespace MIR_INDEX_A;

Table altered.

SQL> desc curr
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CURRENCY_ID                               NOT NULL NUMBER
 CURRENCY_CODE                             NOT NULL VARCHAR2(4)
 CURRENCY_NM                               NOT NULL VARCHAR2(40)
 DELETED_FLG                               NOT NULL VARCHAR2(1)
 LAST_UPDATE_DT                            NOT NULL DATE
 CURRENCY_SYMBOL_NUM                                NUMBER
 VALID_FROM_DT                             NOT NULL DATE
 VALID_TO_DT                                        DATE


SQL> alter table curr add constraint pk_curr_id primary key (CURRENCY_ID ) using index tablespace MIR_INDEX_A;

Table altered.

SQL> create materialized view log on testcore.rates
  2      tablespace MIR_DATA_A 
    with rowid, primary key 
    excluding new values;  3    4  

Materialized view log created.

SQL> create materialized view log on testcore.curr
  2      tablespace MIR_DATA_A 
    with rowid, primary key 
    excluding new values;  3    4  

Materialized view log created.

SQL> select count(*) from
  2  (SELECT
  rates.EXCHRATE_ID,
  CURR.CURRENCY_CODE,
  rates.EXCHANGE_RATE_DT,rates.RATE_PER_USD_NUM,
  rates.RATE_PER_GBP_NUM,rates.RATE_PER_EUR_NUM,
  3    4    5    6    7    rates.DELETED_FLG, rates.LAST_UPDATE_DT ,
  rates.rowid RATE_RID,CURR.rowid CURR_RID 
  FROM testcore.rates,
       testcore.CURR
  8    9   10   11    WHERE CURR.CURRENCY_ID=rates.CURRENCY_ID);

  COUNT(*)
----------
       100

SQL> analyze table rates compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL> analyze table curr compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL> conn testmvu/test
Connected.
SQL> sho user
USER is "TESTMVU"
SQL> desc testcore.rates;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EXCHRATE_ID                               NOT NULL NUMBER
 CURRENCY_ID                               NOT NULL NUMBER
 EXCHANGE_RATE_DT                          NOT NULL DATE
 RATE_PER_USD_NUM                                   NUMBER
 RATE_PER_GBP_NUM                                   NUMBER
 RATE_PER_EUR_NUM                                   NUMBER
 DELETED_FLG                               NOT NULL VARCHAR2(1)
 LAST_UPDATE_DT                            NOT NULL DATE

SQL> desc testcore.curr
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CURRENCY_ID                               NOT NULL NUMBER
 CURRENCY_CODE                             NOT NULL VARCHAR2(4)
 CURRENCY_NM                               NOT NULL VARCHAR2(40)
 DELETED_FLG                               NOT NULL VARCHAR2(1)
 LAST_UPDATE_DT                            NOT NULL DATE
 CURRENCY_SYMBOL_NUM                                NUMBER
 VALID_FROM_DT                             NOT NULL DATE
 VALID_TO_DT                                        DATE

SQL> create materialized view mvu_exchange_rates_test
  2  tablespace mir_mvu_a
BUILD IMMEDIATE
REFRESH FAST ON COMMIT 
AS
SELECT
  rates.EXCHRATE_ID,
  CURR.CURRENCY_CODE,
  rates.EXCHANGE_RATE_DT,rates.RATE_PER_USD_NUM,
  3    4    5    6    7    8    9   10    rates.RATE_PER_GBP_NUM,rates.RATE_PER_EUR_NUM,
  rates.DELETED_FLG, rates.LAST_UPDATE_DT ,
  rates.rowid RATE_RID,CURR.rowid CURR_RID 
  FROM testcore.rates,
 11   12   13   14         testcore.CURR
 15    WHERE CURR.CURRENCY_ID=rates.CURRENCY_ID;
  FROM testcore.rates,
                *
ERROR at line 13:
ORA-01031: insufficient privileges


SQL> create materialized view mvu_exchange_rates_test
  2  tablespace mir_mvu_a
BUILD IMMEDIATE
REFRESH FAST 
AS
SELECT
  rates.EXCHRATE_ID,
  3    4    5    6    7    8    CURR.CURRENCY_CODE,
  rates.EXCHANGE_RATE_DT,rates.RATE_PER_USD_NUM,
  9   10    rates.RATE_PER_GBP_NUM,rates.RATE_PER_EUR_NUM,
 11    rates.DELETED_FLG, rates.LAST_UPDATE_DT ,
 12    rates.rowid RATE_RID,CURR.rowid CURR_RID 
  FROM testcore.rates,
       testcore.CURR
  WHERE CURR.CURRENCY_ID=rates.CURRENCY_ID; 13   14   15  

Materialized view created.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production


<dbdev1:dev4:/appl/oracle> sqlplus '/as sysdba'

SQL*Plus: Release 8.1.7.0.0 - Production on Fri Sep 30 20:01:10 2005

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

SQL> Create user  "TESTMVU" identified by TEST
  2    DEFAULT TABLESPACE MIR_MVU_A
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  QUOTA UNLIMITED ON MIR_MVU_A
  ;  3    4    5    6  

User created.

SQL> grant ALTER SESSION to testmvu;
grant CREATE SESSION to testmvu;
grant CREATE SNAPSHOT to testmvu;
grant CREATE TABLE to testmvu;
grant CREATE VIEW to testmvu;
grant SELECT ANY TABLE to testmvu;
Grant succeeded.

SQL> 
Grant succeeded.

SQL> 
Grant succeeded.

SQL> 
Grant succeeded.

SQL> 
Grant succeeded.

SQL> 

Grant succeeded.

SQL> Create user  "TESTCORE" identified by TEST
  2    DEFAULT TABLESPACE MIR_DATA_A
  TEMPORARY TABLESPACE TEMP
  QUOTA UNLIMITED ON MIR_DATA_A
  QUOTA UNLIMITED ON MIR_INDEX_A
  ;
grant ALTER SESSION to testcore;
grant CREATE SESSION to testcore;
  3    4    5    6  grant CREATE SNAPSHOT to testcore;
grant CREATE TABLE to testcore;
User created.

SQL> 
Grant succeeded.

SQL> 
Grant succeeded.

SQL> 
Grant succeeded.

SQL> 

Grant succeeded.

SQL>  grant select any table to testcore;

Grant succeeded.

SQL> conn testcore/test
Connected.
SQL> sho user
USER is "TESTCORE"
SQL> select name from v$database;

NAME
---------
DEV4

SQL> create table rates as select * from core.exchange_rates where rownum<101;

Table created.

SQL> create table curr as select * from core.currencies where rownum <101;

Table created.

SQL> desc rates 
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EXCHRATE_ID                               NOT NULL NUMBER
 CURRENCY_ID                               NOT NULL NUMBER
 EXCHANGE_RATE_DT                          NOT NULL DATE
 RATE_PER_USD_NUM                                   NUMBER
 RATE_PER_GBP_NUM                                   NUMBER
 RATE_PER_EUR_NUM                                   NUMBER
 DELETED_FLG                               NOT NULL VARCHAR2(1)
 LAST_UPDATE_DT                            NOT NULL DATE

SQL> alter table rates add constraint pk_rate_id primary key (exchrate_id) using index tablespace MIR_INDEX_A;

Table altered.

SQL> desc curr
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CURRENCY_ID                               NOT NULL NUMBER
 CURRENCY_CODE                             NOT NULL VARCHAR2(4)
 CURRENCY_NM                               NOT NULL VARCHAR2(40)
 DELETED_FLG                               NOT NULL VARCHAR2(1)
 LAST_UPDATE_DT                            NOT NULL DATE
 CURRENCY_SYMBOL_NUM                                NUMBER
 VALID_FROM_DT                             NOT NULL DATE
 VALID_TO_DT                                        DATE

SQL> alter table curr add constraint pk_curr_id primary key (CURRENCY_ID ) using index tablespace MIR_INDEX_A;

Table altered.

SQL> create materialized view log on testcore.rates
  2          tablespace MIR_DATA_A 
    with rowid, primary key 
    excluding new values;    3    4  

Materialized view log created.

SQL> create materialized view log on testcore.curr
  2        tablespace MIR_DATA_A 
    with rowid, primary key 
    excluding new values;   3    4  

Materialized view log created.

SQL> analyze table rates compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL> analyze table curr compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL> select count(*) from
  2      (SELECT
  rates.EXCHRATE_ID,
  CURR.CURRENCY_CODE,
  rates.EXCHANGE_RATE_DT,rates.RATE_PER_USD_NUM,
  rates.RATE_PER_GBP_NUM,rates.RATE_PER_EUR_NUM,
  3    4    5    6    7       rates.DELETED_FLG, rates.LAST_UPDATE_DT ,
  rates.rowid RATE_RID,CURR.rowid CURR_RID 
  FROM testcore.rates,
       testcore.CURR
     WHERE CURR.CURRENCY_ID=rates.CURRENCY_ID);  8    9   10   11  

  COUNT(*)
----------
       100

SQL> conn testmvu/test
Connected.
SQL> sho user
USER is "TESTMVU"
SQL> desc testcore.rates;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EXCHRATE_ID                               NOT NULL NUMBER
 CURRENCY_ID                               NOT NULL NUMBER
 EXCHANGE_RATE_DT                          NOT NULL DATE
 RATE_PER_USD_NUM                                   NUMBER
 RATE_PER_GBP_NUM                                   NUMBER
 RATE_PER_EUR_NUM                                   NUMBER
 DELETED_FLG                               NOT NULL VARCHAR2(1)
 LAST_UPDATE_DT                            NOT NULL DATE

SQL> desc testcore.curr
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CURRENCY_ID                               NOT NULL NUMBER
 CURRENCY_CODE                             NOT NULL VARCHAR2(4)
 CURRENCY_NM                               NOT NULL VARCHAR2(40)
 DELETED_FLG                               NOT NULL VARCHAR2(1)
 LAST_UPDATE_DT                            NOT NULL DATE
 CURRENCY_SYMBOL_NUM                                NUMBER
 VALID_FROM_DT                             NOT NULL DATE
 VALID_TO_DT                                        DATE

SQL> create materialized view mvu_exchange_rates_test
  2  tablespace mir_mvu_a
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT
  rates.EXCHRATE_ID,
  3    4    5    6    7    8    CURR.CURRENCY_CODE,
  rates.EXCHANGE_RATE_DT,rates.RATE_PER_USD_NUM,
  rates.RATE_PER_GBP_NUM,rates.RATE_PER_EUR_NUM,
  rates.DELETED_FLG, rates.LAST_UPDATE_DT ,
  rates.rowid RATE_RID,CURR.rowid CURR_RID 
  FROM testcore.rates,
       testcore.CURR
  WHERE CURR.CURRENCY_ID=rates.CURRENCY_ID;  9   10   11   12   13   14   15  

Materialized view created.

SQL> select * from v$version;  

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
PL/SQL Release 8.1.7.4.0 - Production
CORE    8.1.7.0.0       Production
TNS for Solaris: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

Thanks for your help.

 

Hi Tom,

Could you please have a look at my question above:
10g R2 new feature: Partition Change Tracking Refresh Without Materialized View Logs September 20, 2005

Thanks
Maurice

Tom,
Thanks very much for your response. Today I create 2 self contain test cases by different Oracle software. As I noticed, 8.1.7 can create MV with ON COMMIT but 9.2.0.4 can't due to ORA-01031. But if remove 'ON COMMIT' in 9.2.0.4, then it is OK. I post output from my screen here.Please let me know your test result. 

<dbdev1:oracle:dev1:/db/dbbackup/expimp.dump> sqlplus /

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Oct 3 11:33:32 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> drop user TESTMVU cascade;

User dropped.

SQL> Create user  "TESTMVU" identified by TEST
  2    DEFAULT TABLESPACE MIR_MVU_A
  TEMPORARY TABLESPACE TEMP_DEFAULT
  PROFILE DEFAULT
  3    4    5    QUOTA UNLIMITED ON MIR_MVU_A
  ;  6  

User created.

SQL> grant ALTER SESSION to testmvu;

grant CREATE SESSION to testmvu;
grant CREATE SNAPSHOT to testmvu;
grant CREATE TABLE to testmvu;
Grant succeeded.

SQL> 
Grant succeeded.
grant CREATE VIEW to testmvu;

SQL> grant SELECT ANY TABLE to testmvu;
Grant succeeded.

SQL> 
Grant succeeded.

SQL> 
Grant succeeded.

SQL> 

Grant succeeded.

SQL> drop user TESTCORE cascade;

User dropped.

SQL> Create user  "TESTCORE" identified by TEST
  2    DEFAULT TABLESPACE MIR_DATA_A
  3    TEMPORARY TABLESPACE TEMP_DEFAULT
  QUOTA UNLIMITED ON MIR_DATA_A
  4    5    QUOTA UNLIMITED ON MIR_INDEX_A
  ;  6  

User created.

SQL> grant ALTER SESSION to testcore;

Grant succeeded.

SQL> grant CREATE SESSION to testcore;
grant CREATE SNAPSHOT to testcore;
grant CREATE TABLE to testcore;
Grant succeeded.

SQL> 
Grant succeeded.

SQL> 

Grant succeeded.

SQL> conn testcore/test
Connected.

SQL> CREATE TABLE testcore.RATES
  2  (
  3    EXCHRATE_ID       NUMBER                      NOT NULL,
  CURRENCY_ID       NUMBER                      NOT NULL,
  4    5    EXCHANGE_RATE_DT  DATE                        NOT NULL,
  6    RATE_PER_USD_NUM  NUMBER,
  RATE_PER_GBP_NUM  NUMBER,
  7    8    RATE_PER_EUR_NUM  NUMBER,
  DELETED_FLG       VARCHAR2(1)                 DEFAULT 'N'                   NO  9  T NULL,
 10    LAST_UPDATE_DT    DATE                        NOT NULL
)
 11   12  TABLESPACE MIR_DATA_A;

Table created.

SQL> desc rates;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EXCHRATE_ID                               NOT NULL NUMBER
 CURRENCY_ID                               NOT NULL NUMBER
 EXCHANGE_RATE_DT                          NOT NULL DATE
 RATE_PER_USD_NUM                                   NUMBER
 RATE_PER_GBP_NUM                                   NUMBER
 RATE_PER_EUR_NUM                                   NUMBER
 DELETED_FLG                               NOT NULL VARCHAR2(1)
 LAST_UPDATE_DT                            NOT NULL DATE

SQL> CREATE TABLE testcore.CURR
  2  (
  CURRENCY_ID          NUMBER                   NOT NULL,
  3    4    CURRENCY_CODE        VARCHAR2(4)              NOT NULL,
  CURRENCY_NM          VARCHAR2(40)             NOT NULL,
  5    6    DELETED_FLG          VARCHAR2(1)              DEFAULT 'N'                   NOT NULL,
  7    LAST_UPDATE_DT       DATE                     NOT NULL,
  8    CURRENCY_SYMBOL_NUM  NUMBER,
  VALID_FROM_DT        DATE                     NOT NULL,
  9   10    VALID_TO_DT          DATE
)
 11   12  TABLESPACE MIR_DATA_A;

Table created.

SQL> desc curr;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CURRENCY_ID                               NOT NULL NUMBER
 CURRENCY_CODE                             NOT NULL VARCHAR2(4)
 CURRENCY_NM                               NOT NULL VARCHAR2(40)
 DELETED_FLG                               NOT NULL VARCHAR2(1)
 LAST_UPDATE_DT                            NOT NULL DATE
 CURRENCY_SYMBOL_NUM                                NUMBER
 VALID_FROM_DT                             NOT NULL DATE
 VALID_TO_DT                                        DATE

SQL> alter table rates add constraint pk_rate_id primary key (exchrate_id) using index tablespace MIR_INDEX_A;

Table altered.

SQL> alter table curr add constraint pk_curr_id primary key (CURRENCY_ID ) using index tablespace MIR_INDEX_A;

Table altered.

SQL> INSERT INTO CURR ( CURRENCY_ID, CURRENCY_CODE, CURRENCY_NM, DELETED_FLG, LAST_UPDATE_DT,
CURRENCY_SYMBOL_NUM, VALID_FROM_DT, VALID_TO_DT ) VALUES ( 
  2    3  111, 'ATS', 'AUSTRIAN SCHILLING', 'Y',  TO_Date( '01/02/2001 04:33:12 PM', 'MM/DD/YYYY HH:MI:SS AM')
  4  , NULL,  TO_Date( '01/01/1900 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL); 

1 row created.


SQL> INSERT INTO RATES ( EXCHRATE_ID, CURRENCY_ID, EXCHANGE_RATE_DT, RATE_PER_USD_NUM,
RATE_PER_GBP_NUM, RATE_PER_EUR_NUM, DELETED_FLG, LAST_UPDATE_DT ) VALUES ( 
  2    3  61624466, 111,  TO_Date( '04/29/1995 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 0.1033057
  4  , 0.0639049, 0.1000619, 'N',  TO_Date( '12/27/2001 08:47:00 PM', 'MM/DD/YYYY HH:MI:SS AM')); 

1 row created.

SQL> commit;

Commit complete.

SQL> create materialized view log on testcore.rates
  2       tablespace MIR_DATA_A 
     with rowid, primary key 
  3    4       excluding new values;  

Materialized view log created.

SQL> create materialized view log on testcore.curr
  2       tablespace MIR_DATA_A 
    with rowid, primary key 
  3    4      excluding new values; 

Materialized view log created.

SQL> analyze table rates compute statistics;

Table analyzed.

SQL> analyze table curr compute statistics ;

Table analyzed.

SQL> conn testmvu/test
Connected.
SQL> desc testcore.rates;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EXCHRATE_ID                               NOT NULL NUMBER
 CURRENCY_ID                               NOT NULL NUMBER
 EXCHANGE_RATE_DT                          NOT NULL DATE
 RATE_PER_USD_NUM                                   NUMBER
 RATE_PER_GBP_NUM                                   NUMBER
 RATE_PER_EUR_NUM                                   NUMBER
 DELETED_FLG                               NOT NULL VARCHAR2(1)
 LAST_UPDATE_DT                            NOT NULL DATE

SQL>  desc testcore.curr
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CURRENCY_ID                               NOT NULL NUMBER
 CURRENCY_CODE                             NOT NULL VARCHAR2(4)
 CURRENCY_NM                               NOT NULL VARCHAR2(40)
 DELETED_FLG                               NOT NULL VARCHAR2(1)
 LAST_UPDATE_DT                            NOT NULL DATE
 CURRENCY_SYMBOL_NUM                                NUMBER
 VALID_FROM_DT                             NOT NULL DATE
 VALID_TO_DT                                        DATE

SQL> create materialized view mvu_exchange_rates_test
  2      tablespace mir_mvu_a
BUILD IMMEDIATE
  3    4  REFRESH FAST ON COMMIT 
AS
  5    6  SELECT
  rates.EXCHRATE_ID,
  7    8    CURR.CURRENCY_CODE,
  rates.EXCHANGE_RATE_DT,rates.RATE_PER_USD_NUM,
  9   10    rates.RATE_PER_GBP_NUM,rates.RATE_PER_EUR_NUM,
  rates.DELETED_FLG, rates.LAST_UPDATE_DT ,
 11   12    rates.rowid RATE_RID,CURR.rowid CURR_RID 
  FROM testcore.rates,
 13   14         testcore.CURR
    WHERE CURR.CURRENCY_ID=rates.CURRENCY_ID;
 15    FROM testcore.rates,  FROM testcore.rates,
                *
ERROR at line 13:
ORA-01031: insufficient privileges


SQL> create materialized view mvu_exchange_rates_test
  2       tablespace mir_mvu_a
  3  BUILD IMMEDIATE
REFRESH FAST 
  4    5  AS
  6  SELECT
  7    rates.EXCHRATE_ID,
  8    CURR.CURRENCY_CODE,
  rates.EXCHANGE_RATE_DT,rates.RATE_PER_USD_NUM,
  9   10       rates.RATE_PER_GBP_NUM,rates.RATE_PER_EUR_NUM,
     rates.DELETED_FLG, rates.LAST_UPDATE_DT ,
 11   12       rates.rowid RATE_RID,CURR.rowid CURR_RID 
  FROM testcore.rates,
 13   14         testcore.CURR
 15    WHERE CURR.CURRENCY_ID=rates.CURRENCY_ID;

Materialized view created.

SQL> select * from mvu_exchange_rates_test;

EXCHRATE_ID CURR EXCHANGE_ RATE_PER_USD_NUM RATE_PER_GBP_NUM RATE_PER_EUR_NUM D
----------- ---- --------- ---------------- ---------------- ---------------- -
LAST_UPDA RATE_RID           CURR_RID
--------- ------------------ ------------------
   61624466 ATS  29-APR-95         .1033057         .0639049         .1000619 N
27-DEC-01 ABBHJVAAPAAEEHGAAA ABBHJWAAPAAEEIGAAA

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Solaris: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

*********************************************************************************

<dbdev1:oracle:dev1:/appl/oracle> . ./.profile.8i
<dbdev1:dev4:/appl/oracle> sqlplus '/as sysdba'

SQL*Plus: Release 8.1.7.0.0 - Production on Mon Oct 3 12:36:34 2005

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  189595324 bytes
Fixed Size                   102076 bytes
Variable Size              57880576 bytes
Database Buffers          131072000 bytes
Redo Buffers                 540672 bytes
Database mounted.
Database opened.
SQL> drop user TESTMVU cascade;

User dropped.

SQL> Create user  "TESTMVU" identified by TEST
  2    DEFAULT TABLESPACE MIR_MVU_A
  3    TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  4    5    QUOTA UNLIMITED ON MIR_MVU_A
  ;  6  

User created.

SQL> grant ALTER SESSION to testmvu;
grant CREATE SESSION to testmvu;
grant CREATE SNAPSHOT to testmvu;
grant CREATE TABLE to testmvu;
grant CREATE VIEW to testmvu;
grant SELECT ANY TABLE to testmvu;

Grant succeeded.

SQL> 
Grant succeeded.

SQL> 
Grant succeeded.

SQL> 
Grant succeeded.

SQL> 
Grant succeeded.

SQL> 
Grant succeeded.

SQL> 
SQL> drop user TESTCORE cascade;

User dropped.

SQL> Create user  "TESTCORE" identified by TEST
  2    DEFAULT TABLESPACE MIR_DATA_A
  TEMPORARY TABLESPACE TEMP
  3    4    QUOTA UNLIMITED ON MIR_DATA_A
  5    QUOTA UNLIMITED ON MIR_INDEX_A
  6    ;

User created.

SQL> grant ALTER SESSION to testcore;

Grant succeeded.

SQL> grant CREATE SESSION to testcore;
grant CREATE SNAPSHOT to testcore;
grant CREATE TABLE to testcore;

Grant succeeded.

SQL> 
Grant succeeded.

SQL> 
Grant succeeded.

SQL>   
SQL> conn testcore/test
Connected.
SQL> CREATE TABLE testcore.RATES
  2  (
  3    EXCHRATE_ID       NUMBER                      NOT NULL,
  CURRENCY_ID       NUMBER                      NOT NULL,
  EXCHANGE_RATE_DT  DATE                        NOT NULL,
  4    5    6    RATE_PER_USD_NUM  NUMBER,
  RATE_PER_GBP_NUM  NUMBER,
  7    8    RATE_PER_EUR_NUM  NUMBER,
  DELETED_FLG       VARCHAR2(1)                 DEFAULT 'N'                   NO  9  T NULL,
  LAST_UPDATE_DT    DATE                        NOT NULL
 10   11  )
TABLESPACE MIR_DATA_A; 12  

Table created.

SQL> desc rates 
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EXCHRATE_ID                               NOT NULL NUMBER
 CURRENCY_ID                               NOT NULL NUMBER
 EXCHANGE_RATE_DT                          NOT NULL DATE
 RATE_PER_USD_NUM                                   NUMBER
 RATE_PER_GBP_NUM                                   NUMBER
 RATE_PER_EUR_NUM                                   NUMBER
 DELETED_FLG                               NOT NULL VARCHAR2(1)
 LAST_UPDATE_DT                            NOT NULL DATE

SQL> CREATE TABLE testcore.CURR
  2  (
  CURRENCY_ID          NUMBER                   NOT NULL,
  3    4    CURRENCY_CODE        VARCHAR2(4)              NOT NULL,
  CURRENCY_NM          VARCHAR2(40)             NOT NULL,
  5    6    DELETED_FLG          VARCHAR2(1)              DEFAULT 'N'                   NOT NULL,
  7    LAST_UPDATE_DT       DATE                     NOT NULL,
  8    CURRENCY_SYMBOL_NUM  NUMBER,
  VALID_FROM_DT        DATE                     NOT NULL,
  9   10    VALID_TO_DT          DATE
) 11   
 12  TABLESPACE MIR_DATA_A;

Table created.

SQL> desc curr;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CURRENCY_ID                               NOT NULL NUMBER
 CURRENCY_CODE                             NOT NULL VARCHAR2(4)
 CURRENCY_NM                               NOT NULL VARCHAR2(40)
 DELETED_FLG                               NOT NULL VARCHAR2(1)
 LAST_UPDATE_DT                            NOT NULL DATE
 CURRENCY_SYMBOL_NUM                                NUMBER
 VALID_FROM_DT                             NOT NULL DATE
 VALID_TO_DT                                        DATE


SQL> alter table rates add constraint pk_rate_id primary key (exchrate_id) using index 
  2  tablespace MIR_INDEX_A;

Table altered.

SQL> alter table curr add constraint pk_curr_id primary key (CURRENCY_ID ) using index 
  2  tablespace MIR_INDEX_A;

Table altered.

SQL> INSERT INTO CURR ( CURRENCY_ID, CURRENCY_CODE, CURRENCY_NM, 
  2  DELETED_FLG, LAST_UPDATE_DT,CURRENCY_SYMBOL_NUM, VALID_FROM_DT, VALID_TO_DT ) 
  3  VALUES 
( 111, 'ATS', 'AUSTRIAN SCHILLING', 'Y',  TO_Date( '01/02/2001 04:33:12 PM', 
  4    5  'MM/DD/YYYY HH:MI:SS AM'), NULL,  TO_Date( '01/01/1900 12:00:00 AM', 
'MM/DD/YYYY HH:MI:SS AM'), NULL);   6  

1 row created.

SQL> INSERT INTO RATES ( EXCHRATE_ID, CURRENCY_ID, EXCHANGE_RATE_DT, 
  2  RATE_PER_USD_NUM,RATE_PER_GBP_NUM, RATE_PER_EUR_NUM, DELETED_FLG, LAST_UPDATE_DT ) 
  3  VALUES 
( 61624466, 111,  TO_Date( '04/29/1995 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),   4  0.1033057
, 0.0639049, 0.1000619, 'N',  TO_Date( '12/27/2001 08:47:00 PM', 'MM/DD/YYYY HH:  5  MI:SS AM')); 

1 row created.

SQL> commit;

Commit complete.

SQL> create materialized view log on testcore.rates
  2       tablespace MIR_DATA_A 
  3       with rowid, primary key 
     excluding new values;    4  

Materialized view log created.

SQL>  create materialized view log on testcore.curr
  2       tablespace MIR_DATA_A 
    with rowid, primary key 
  3    4      excluding new values; 

Materialized view log created.

SQL> analyze table rates compute statistics;

Table analyzed.

SQL> analyze table curr compute statistics;

Table analyzed.

SQL> conn testmvu/test
Connected.
SQL> desc testcore.rates;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EXCHRATE_ID                               NOT NULL NUMBER
 CURRENCY_ID                               NOT NULL NUMBER
 EXCHANGE_RATE_DT                          NOT NULL DATE
 RATE_PER_USD_NUM                                   NUMBER
 RATE_PER_GBP_NUM                                   NUMBER
 RATE_PER_EUR_NUM                                   NUMBER
 DELETED_FLG                               NOT NULL VARCHAR2(1)
 LAST_UPDATE_DT                            NOT NULL DATE

SQL>  desc testcore.curr
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CURRENCY_ID                               NOT NULL NUMBER
 CURRENCY_CODE                             NOT NULL VARCHAR2(4)
 CURRENCY_NM                               NOT NULL VARCHAR2(40)
 DELETED_FLG                               NOT NULL VARCHAR2(1)
 LAST_UPDATE_DT                            NOT NULL DATE
 CURRENCY_SYMBOL_NUM                                NUMBER
 VALID_FROM_DT                             NOT NULL DATE
 VALID_TO_DT                                        DATE

SQL> create materialized view mvu_exchange_rates_test
  2      tablespace mir_mvu_a
BUILD IMMEDIATE
  3    4  REFRESH FAST ON COMMIT 
AS
  5    6  SELECT
  rates.EXCHRATE_ID,
  7    8    CURR.CURRENCY_CODE,
  rates.EXCHANGE_RATE_DT,rates.RATE_PER_USD_NUM,
  9   10    rates.RATE_PER_GBP_NUM,rates.RATE_PER_EUR_NUM,
  rates.DELETED_FLG, rates.LAST_UPDATE_DT ,
 11   12    rates.rowid RATE_RID,CURR.rowid CURR_RID 
  FROM testcore.rates,
 13   14         testcore.CURR
    WHERE CURR.CURRENCY_ID=rates.CURRENCY_ID; 15  

Materialized view created.

SQL> select * from mvu_exchange_rates_test;

EXCHRATE_ID CURR EXCHANGE_ RATE_PER_USD_NUM RATE_PER_GBP_NUM RATE_PER_EUR_NUM D
----------- ---- --------- ---------------- ---------------- ---------------- -
LAST_UPDA RATE_RID           CURR_RID
--------- ------------------ ------------------
   61624466 ATS  29-APR-95         .1033057         .0639049         .1000619 N
27-DEC-01 AACATRAAPAABzyGAAA AACATSAAPAABzzGAAA

SQL> select * from v$version;  

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
PL/SQL Release 8.1.7.4.0 - Production
CORE    8.1.7.0.0       Production
TNS for Solaris: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production 

i was going to run this, but it was taking forever to edit. 

tablespace names (that I do not have)
code that is formatted really hard to cut and past (the flow is a tad "strange", things like:

SQL> CREATE TABLE testcore.RATES
  2  (
  3    EXCHRATE_ID       NUMBER                      NOT NULL,
  CURRENCY_ID       NUMBER                      NOT NULL,
  4    5    EXCHANGE_RATE_DT  DATE                        NOT NULL,
  6    RATE_PER_USD_NUM  NUMBER,
  RATE_PER_GBP_NUM  NUMBER,
  7    8    RATE_PER_EUR_NUM  NUMBER,
  DELETED_FLG       VARCHAR2(1)                 DEFAULT 'N'                   NO 
 9  T NULL,
 10    LAST_UPDATE_DT    DATE                        NOT NULL
)
 11   12  TABLESPACE MIR_DATA_A;

look at that no...... t null and such...)

make it "easier"? please. no tablespaces, just script, or use tablespaces anyone might have (like users) - a test case, something we can cut and paste and run. 

Tom,
Please see the script which makes you easier to run. Thanks
very much for your time and help.

**************************************************

conn /
spool test.log
set echo on

drop user TESTMVU cascade;

Create user "TESTMVU" identified by TEST
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE TEMP_DEFAULT
PROFILE DEFAULT
QUOTA UNLIMITED ON users
;


grant ALTER SESSION to testmvu;
grant CREATE SESSION to testmvu;
grant CREATE SNAPSHOT to testmvu;
grant CREATE TABLE to testmvu;
grant SELECT ANY TABLE to testmvu;

drop user TESTCORE cascade;

Create user "TESTCORE" identified by TEST
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE TEMP_DEFAULT
QUOTA UNLIMITED ON users
;


grant ALTER SESSION to testcore;
grant CREATE SESSION to testcore;
grant CREATE SNAPSHOT to testcore;
grant CREATE TABLE to testcore;


conn testcore/test

CREATE TABLE testcore.RATES
(
EXCHRATE_ID NUMBER NOT NULL,
CURRENCY_ID NUMBER NOT NULL,
EXCHANGE_RATE_DT DATE NOT NULL,
RATE_PER_USD_NUM NUMBER,
RATE_PER_GBP_NUM NUMBER,
RATE_PER_EUR_NUM NUMBER,
DELETED_FLG VARCHAR2(1) DEFAULT 'N' NOT NULL,
LAST_UPDATE_DT DATE NOT NULL
)
TABLESPACE users;


desc rates;

CREATE TABLE testcore.CURR
(
CURRENCY_ID NUMBER NOT NULL,
CURRENCY_CODE VARCHAR2(4) NOT NULL,
CURRENCY_NM VARCHAR2(40) NOT NULL,
DELETED_FLG VARCHAR2(1) DEFAULT 'N' NOT NULL,
LAST_UPDATE_DT DATE NOT NULL,
CURRENCY_SYMBOL_NUM NUMBER,
VALID_FROM_DT DATE NOT NULL,
VALID_TO_DT DATE
)
TABLESPACE users;


desc curr;


alter table rates add constraint pk_rate_id primary key (exchrate_id) ;

alter table curr add constraint pk_curr_id primary key (CURRENCY_ID ) ;


INSERT INTO CURR ( CURRENCY_ID, CURRENCY_CODE, CURRENCY_NM, DELETED_FLG, LAST_UPDATE_DT, CURRENCY_SYMBOL_NUM,
VALID_FROM_DT, VALID_TO_DT )
VALUES
(111, 'ATS', 'AUSTRIAN SCHILLING', 'Y', TO_Date( '01/02/2001 04:33:12 PM', 'MM/DD/YYYY HH:MI:SS AM') , NULL, TO_Date( '01/01/1900
12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL);

INSERT INTO RATES ( EXCHRATE_ID, CURRENCY_ID, EXCHANGE_RATE_DT, RATE_PER_USD_NUM, RATE_PER_GBP_NUM,
RATE_PER_EUR_NUM, DELETED_FLG, LAST_UPDATE_DT )
VALUES
(61624466, 111, TO_Date( '04/29/1995 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 0.1033057 , 0.0639049, 0.1000619, 'N',
TO_Date( '12/27/2001 08:47:00 PM', 'MM/DD/YYYY HH:MI:SS AM'));


create materialized view log on testcore.rates
tablespace users
with rowid, primary key
excluding new values;

create materialized view log on testcore.curr
tablespace users
with rowid, primary key
excluding new values;

analyze table rates compute statistics;

analyze table curr compute statistics ;

conn testmvu/test
desc testcore.rates;
desc testcore.curr
create materialized view mvu_exchange_rates_test
tablespace users
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT
rates.EXCHRATE_ID,
CURR.CURRENCY_CODE,
rates.EXCHANGE_RATE_DT,rates.RATE_PER_USD_NUM,
rates.RATE_PER_GBP_NUM,rates.RATE_PER_EUR_NUM,
rates.DELETED_FLG, rates.LAST_UPDATE_DT ,
rates.rowid RATE_RID,CURR.rowid CURR_RID
FROM testcore.rates,
testcore.CURR
WHERE CURR.CURRENCY_ID=rates.CURRENCY_ID;


create materialized view mvu_exchange_rates_test
tablespace users
BUILD IMMEDIATE
REFRESH FAST
AS
SELECT
rates.EXCHRATE_ID,
CURR.CURRENCY_CODE,
rates.EXCHANGE_RATE_DT,rates.RATE_PER_USD_NUM,
rates.RATE_PER_GBP_NUM,rates.RATE_PER_EUR_NUM,
rates.DELETED_FLG, rates.LAST_UPDATE_DT ,
rates.rowid RATE_RID,CURR.rowid CURR_RID
FROM testcore.rates,
testcore.CURR
WHERE CURR.CURRENCY_ID=rates.CURRENCY_ID;


select * from mvu_exchange_rates_test;

select * from v$version;

exit

spool off

Tom,
By reading the link documentation for 9.2, I grant additional privilege to that user, then the MV with on commint can be created.

Thanks very much.

Tom,
Did a user level export of schema1 and imported this schema into schema2. The materialized views present in schema1 were not exported to schema2.

My question
Q1) Can materialized views be exported and imported between schemas having different schema names?
Q2) If no, what is the best procedure to re-create the materialized views in the destination schem.

As always appreciate all the help you are providing to the Oracle community.

Thank you

do you have an example for us to work with (as FEW objects as humanly possible)

ops$tkyte@ORA10G> drop user a cascade;
 
User dropped.
 
ops$tkyte@ORA10G> drop user b cascade;
 
User dropped.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> grant dba to a identified by a;
 
Grant succeeded.
 
ops$tkyte@ORA10G> grant dba to b identified by b;
 
Grant succeeded.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> @connect a/a
ops$tkyte@ORA10G> set termout off
a@ORA10G> @login
a@ORA10G> set termout off
a@ORA10G>
a@ORA10G> set termout on
a@ORA10G>
a@ORA10G> create table t ( x int primary key, y int );
 
Table created.
 
a@ORA10G>
a@ORA10G> create materialized view mv
  2  as
  3  select * from t where y > 50;
 
Materialized view created.
 
a@ORA10G>
a@ORA10G> !exp userid=a/a owner=a
 
Export: Release 10.1.0.4.0 - Production on Thu Oct 13 09:20:53 2005
 
Copyright (c) 1982, 2004, Oracle.  All rights reserved.
 
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
 
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user A
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user A
About to export A's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export A's tables via Conventional Path ...
. . exporting table                             MV          0 rows exported
. . exporting table                              T          0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
 
a@ORA10G> !imp userid=b/b fromuser=a touser=b
 
Import: Release 10.1.0.4.0 - Production on Thu Oct 13 09:20:56 2005
 
Copyright (c) 1982, 2004, Oracle.  All rights reserved.
 
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
 
Export file created by EXPORT:V10.01.00 via conventional path
 
Warning: the objects were exported by A, not by you
 
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. . importing table                           "MV"          0 rows imported
. . importing table                            "T"          0 rows imported
Import terminated successfully without warnings.
 
a@ORA10G>
a@ORA10G> @connect b/b
a@ORA10G> set termout off
b@ORA10G> @login
b@ORA10G> set termout off
b@ORA10G>
b@ORA10G> set termout on
b@ORA10G> select object_name, object_type from user_objects;
 
OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
MV                             TABLE
SYS_C0020437                   INDEX
T                              TABLE
SYS_C0020438                   INDEX
MV                             MATERIALIZED VIEW
 


<b>the MV's "came with" in this case...</b>
 

Thank you Tom, for the step by step example.

After looking at the example above, particularly the statement 'grant dba to b identified by b'
noticed that the schema 'b' I have did not have the create materialized view privilege and so my import was missing the materialized views.

Thank you again.

Please provide me how to build this query --logic below

SELECT MAX(a1.effdt)
FROM ps_job a1
WHERE a1.emplid=job.emplid
and a1.empl_status='A'
and
(
if (case1 )
then
a1.effdt<=sysdate
else
a1.effdt>=sysdate
)


Please help me appreciate your help...

SELECT MAX(a1.effdt)
FROM ps_job a1
WHERE a1.emplid=job.emplid
and a1.empl_status='A'
and *******************
(
if (case1 )
then
a1.effdt<=sysdate
else
a1.effdt>=sysdate
)

***** here i want to add a dynamic and condition based on any condition(case 1 ) say if &var=5 then i want to use a1.effdt<=sysdate in main query else i want to use a1.effdt>=sysdate

Hi

We recently migrated from 8i to 9i after migration we noticed Oracle created tons of new jobs of those MV which normally are refreshed on demand.

How so? Why Oracle creates new jobs?!

Hi

I make a pardon from previous post. Migration didnt cause this

It seems that if I call dbms_mview.refresh from a pl/sql wrapper internally Oracle creates a new job, I have verified this by looking dba_jobs_running and the job sequence increasing. However if I call dbms_mview.refresh manually this doesnt happen.

Is this expected behaviour in 9.2.0.6?

Hi

If I run dbms_mviews.refresh from sqlplus it runs in the prompt

if i create a procedure (a wrapper)

create or replace procedure
as
begin
dbms_mview.refresh('EMP_MV');
end;
/

This internally submits a job!

How so?

show me what you mean - from start to finish....


ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;

Table created.

ops$tkyte@ORA9IR2> alter table emp add constraint emp_pk primary key(empno);

Table altered.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create materialized view emp_mv
  2  refresh on demand
  3  as
  4  select * from emp;
select * from emp
              *
ERROR at line 4:
ORA-12006: a materialized view with the same user.name already exists


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure p
  2  as
  3  begin
  4          dbms_mview.refresh('EMP_MV');
  5  end;
  6  /

Procedure created.

ops$tkyte@ORA9IR2> exec p

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> select * from user_jobs;

no rows selected

 

Please double check that you do not have atomic_refresh => false (unless you really need it) when you refresh mviews using your wrappers.

=========================================================
atomic_refresh

If this parameter is set to true, then the list of materialized views is refreshed in a single transaction. All of the refreshed materialized views are updated to a single point in time. If the refresh fails for any of the materialized views, none of the materialized views are updated.

If this parameter is set to false, then each of the materialized views is refreshed in a separate transaction. The number of job queue processes must be set to 1 or greater if this parameter is false.
=========================================================

Be aware that on 10g the mview index(es) rebuild(s) can be done via jobs.

atomic_refresh is really a bug of bug and of bugs!

In 8i the parameter simply doesnt work, no matter how many MV you refresh it no job would be submitted

In 9i things gets better but not correct. If you refresh more than one MV the jobs are submitted but if you only have MV it submits jobs too! The workaround is use atomic_refresh => TRUE.

In 10g things work properly

Tom,

I tried creating a materialied view on external table and it was succesful, but when I tried to use refresh on commit, it failed with ORA-12054 error.

Question: Is it possible to create a materialized view with refersh on commit on external tables? If yes, an example will be very helpful to understand and if not then why not?

Here is the sameple code
/* Formatted on 2005/11/11 15:45 (Formatter Plus v4.8.6) */
CREATE MATERIALIZED VIEW klondike_dss.billing_mv
TABLESPACE billing_data
NOCACHE
NOLOGGING
NOCOMPRESS
NOPARALLEL
BUILD DEFERRED
REFRESH COMPLETE ON COMMIT
WITH ROWID
ENABLE QUERY REWRITE
AS
SELECT TRUNC (hist.rel_date) AS summary_date, 0 AS is_ldc,
TRUNC (hist.iam_date) AS call_date, hist.call_type_code,
hist.org_id_opc, hist.org_id_dpc, hist.ssp_id_opc, hist.ssp_id_dpc,
hist.trunk_group_id,
SUM (DECODE (hist.call_type_code, '6', hist.mou, 0)) AS tot_unknown,
COUNT (*) AS tot_calls, SUM (hist.mou) AS tot_minutes,
SUM ( hist.mou
* (DECODE (hist.orig_call_rate,
NULL, DECODE (hist.term_call_rate,
NULL, 0,
hist.term_call_rate
),
hist.orig_call_rate
)
)
) AS tot_amount,

FROM billing hist -- external table
WHERE hist.report_ind = 0
GROUP BY TRUNC (hist.rel_date),
TRUNC (hist.iam_date),
hist.org_id_opc,
hist.org_id_dpc,
hist.ssp_id_opc,
hist.ssp_id_dpc,
hist.trunk_group_id,
hist.call_type_code
/

Tom,

I tried creating a materialied view on external table and it was succesful, but when I tried to use refresh on commit, it failed with ORA-12054 error.

Question: Is it possible to create a materialized view with refersh on commit on external tables? If yes, an example will be very helpful to understand and if not then why not?

Here is the sameple code
/* Formatted on 2005/11/11 15:45 (Formatter Plus v4.8.6) */
CREATE MATERIALIZED VIEW klondike_dss.billing_mv
TABLESPACE billing_data
NOCACHE
NOLOGGING
NOCOMPRESS
NOPARALLEL
BUILD DEFERRED
REFRESH COMPLETE ON COMMIT
WITH ROWID
ENABLE QUERY REWRITE
AS
SELECT TRUNC (hist.rel_date) AS summary_date, 0 AS is_ldc,
TRUNC (hist.iam_date) AS call_date, hist.call_type_code,
hist.org_id_opc, hist.org_id_dpc, hist.ssp_id_opc, hist.ssp_id_dpc,
hist.trunk_group_id,
SUM (DECODE (hist.call_type_code, '6', hist.mou, 0)) AS tot_unknown,
COUNT (*) AS tot_calls, SUM (hist.mou) AS tot_minutes,
SUM ( hist.mou
* (DECODE (hist.orig_call_rate,
NULL, DECODE (hist.term_call_rate,
NULL, 0,
hist.term_call_rate
),
hist.orig_call_rate
)
)
) AS tot_amount,

FROM billing hist -- external table
WHERE hist.report_ind = 0
GROUP BY TRUNC (hist.rel_date),
TRUNC (hist.iam_date),
hist.org_id_opc,
hist.org_id_dpc,
hist.ssp_id_opc,
hist.ssp_id_dpc,
hist.trunk_group_id,
hist.call_type_code
/

Hi Tom,
I have been trying to find the answer to a query, but could not really find it anywhere..Hence need to ask it here..

We are trying to separate an application consisting of a Oracle 8i Database and C++ application into two logiocally separate parts. Hence the design consists of having another database and C++ application based on the current one on another server catering to different kind of requests.

Our problem is that a particular table data needs to be shared by both the databases, and not only that, both the C++ applications need to be alerted on any change in this data.
This is currently done using DBMS_ALERT.SIGNAL on the original database.
We are thinking of various options like
1)Having a view on this table from the new remote database and registering the new C++ application as well to wait for the relevant alert
2) Having a snapshot/materialized view and adding a trigger on this data to raise the same alert in the new database

Another consideration here is that we need to make the two databases as independent of each other as possible.

Could you please recommend one of the approaches above, or any other approach we can follow for implementing this?

Thanks for all your help, it has been very useful when we were stuck in the past.

Thanks and Regards,
Kanchan,
Pune.

Why is this materialized view not able to do a REFRESH FAST ON COMMIT?

CREATE MATERIALIZED VIEW LOG ON CD_RMIS_LIMITS
WITH ROWID,SEQUENCE (COVERAGE_TYPE,LIMITAMOUNT,LIMITDESCRIPTION,RMIS_ID)
INCLUDING NEW VALUES
/

CREATE MATERIALIZED VIEW ORACLE.CD_RMIS_CARGO_LIMIT_MAX_VIEW
BUILD IMMEDIATE
REFRESH FAST
ON COMMIT
AS select rmis_id, max(limitamount) as cargo_limit
from cd_rmis_limits
where coverage_type='CARGO'
group by rmis_id

/

I get a -
Following on-commit snapshots not refreshed :
ORACLE.CD_RMIS_CARGO_LIMIT_MAX_VIEW
in my alert log.

I forgot to include that this is Oracle 9.2.0.6 and my developers are trying to keep this view in sync with updates on the base table for use on a web page.

you know what will be faster, if you need the max limitamount for a given rmis_id?

create index t_idx on t(rmis_id, limitamount);



....
ops$tkyte@ORA9IR2> create table t
  2  as
  3  select mod(rownum,5000) rmis_id, object_id limitamount, a.*
  4    from all_objects a;

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t_idx on t(rmis_id,limitamount);

Index created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select max(limitamount)
  2    from t
  3   where rmis_id = 42;

MAX(LIMITAMOUNT)
----------------
           32441

ops$tkyte@ORA9IR2> set autotrace on
ops$tkyte@ORA9IR2> select max(limitamount)
  2    from t
  3   where rmis_id = 42;

MAX(LIMITAMOUNT)
----------------
           32441


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=9)
   1    0   SORT (AGGREGATE)
   2    1     FIRST ROW (Cost=2 Card=6 Bytes=54)
   3    2       INDEX (RANGE SCAN (MIN/MAX)) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=5119)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        389  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte@ORA9IR2> set autotrace off;


That MV would only be useful if you frequently needed ALL max(limitamounts) for EVERY rmis_id.


If you need the max(limitamount) given a rmis_id, just index. 

Thank you for your response. I believe that my developers are using the view to display all of the data from the base table that is related to the transportation type of Cargo. This is a web screen that tells truckers what various weight limits are.

I'm sorry I didn't include the DDL for the table. It is very simple.

CREATE TABLE CD_RMIS_LIMITS
(RMIS_ID NUMBER NOT NULL,
COVERAGE_TYPE VARCHAR2(90 byte) NOT NULL,
LIMITDESCRIPTION VARCHAR2(90 byte) NOT NULL,
LIMITAMOUNT NUMBER NOT NULL,
INSERTED DATE NOT NULL,
CONSTRAINT PK_CD_RMIS_LIMITS PRIMARY KEY(RMIS_ID,
COVERAGE_TYPE, LIMITDESCRIPTION) )

I believe your observation about the count(*) being missing is the answer I needed. I've tried to find a straight answer about what to include in a materialized view to do a refresh fast on commit with an aggregate and I've found various and confusing answers. I'll see if the count(*) works. Thank you.

Ok, I set up a test scenario and my new materialized view with counts still doesn't work with an update.  Here is my new materialized view -
CREATE MATERIALIZED VIEW ORACLE.CD_RMIS_CARGO_LIMIT_MAX_VIEW2
BUILD IMMEDIATE
REFRESH FAST
ON COMMIT
AS select r.rmis_id,
          count(*) as total_cnt,
          max(r.limitamount) as cargo_limit,
          count(r.limitamount) as cargo_cnt
   from cd_rmis_limits r
   where r.coverage_type='CARGO'
   group by r.rmis_id
/
SQL> select * from CD_RMIS_CARGO_LIMIT_MAX_VIEW2 where rmis_id in (4903, 4882, 3367);

   RMIS_ID  TOTAL_CNT CARGO_LIMIT  CARGO_CNT
---------- ---------- ----------- ----------
      3367          4      500000          4
      4882          3      500000          3
      4903          2     1500000          2

SQL> update cd_rmis_limits set limitamount = 700000 where rmis_id = 4882 and limitdescription = 'PER
 VEHICLE' and coverage_type = 'CARGO'; 

1 row updated.

SQL> select * from CD_RMIS_CARGO_LIMIT_MAX_VIEW2 where rmis_id in (4903, 4882, 3367);

   RMIS_ID  TOTAL_CNT CARGO_LIMIT  CARGO_CNT
---------- ---------- ----------- ----------
      3367          4      500000          4
      4882          3      500000          3
      4903          2     1500000          2

SQL> select rmis_id, limitamount from cd_rmis_limits where rmis_id in (4903, 4882, 3367);

   RMIS_ID LIMITAMOUNT
---------- -----------
      3367      300000
      3367      500000
      3367      500000
      3367      200000
      4882      700000
      4882      500000
      4882      500000
      4903     1000000
      4903     1500000

And I did do a commit.  What have I missed. 

Hi Tom,

I amd using Oracle 9i (9.2.0.6.0) on HP_UX 11.00.
Context
=======
I am having one master table and one detail table which gets populated once in a month by using some batch jobs. The detail table contains different types of data corrosponding to each master records and need to displayed by application in different format.
On these two tables, I have created four materialized views with the following options
CREATE MATERIALIZED VIEW XYZ
BUILD IMMEDIATE
REFRESH FORCE
ON COMMIT
AS select ......
from master, detail
group by ...
Whenever I am creating the four MV, the fourth one gives me the following error:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
Please let me know, Is there any limitations on creating MV's or I am missing some important point.

Thanks a lot for your help as always..

Thanks and Regards

Rahul

Hi Tom,

We are having a Master Table (FUND) and a detail table (FUND_PERF). The structure of the FUND_PERF table is as follows:
Name Null? Type
-------------------- -------- ------------
FUND_ID NOT NULL NUMBER(10)
PERIOD_TYPE_ID NOT NULL NUMBER(7)
AS_OF_DATE NOT NULL DATE
PERF_VALUE NUMBER(23,16)
CURRENCY_TYPE_ID NOT NULL NUMBER(7)

Due to some business requirement, I have created the following MV:
CREATE MATERIALIZED VIEW MV_FUND_PERF_Stand
TABLESPACE VJ_PRICE_PERF
BUILD IMMEDIATE
REFRESH FORCE
ON COMMIT
AS
select
a.fund_id, b.name,
SUM(DECODE(period_type_id,34,perf_value,NULL)) YEAR_TO_DATE,
MAX(DECODE(period_type_id,34,as_of_date,NULL)) YTD_AS_OF_DATE,
SUM(DECODE(period_type_id,35,perf_value,NULL)) CURRENT_MONTH,
MAX(DECODE(period_type_id,35,as_of_date,NULL)) MONTH_AS_OF_DATE,
SUM(DECODE(period_type_id,36,perf_value,NULL)) TRAILING_THREE_MONTHS,
MAX(DECODE(period_type_id,36,as_of_date,NULL)) THREE_MONTH_AS_OF_DATE,
SUM(DECODE(period_type_id,37,perf_value,NULL)) CALENDER_QUARTER,
MAX(DECODE(period_type_id,37,as_of_date,NULL)) QUARTER_AS_OF_DATE,
SUM(DECODE(period_type_id,39,perf_value,NULL)) QTR_ONE_YEAR,
MAX(DECODE(period_type_id,39,as_of_date,NULL)) QTR_AS_OF_DATE,
SUM(DECODE(period_type_id,40,perf_value,NULL)) QTR_THREE_YEAR,
SUM(DECODE(period_type_id,41,perf_value,NULL)) QTR_FIVE_YEAR,
SUM(DECODE(period_type_id,42,perf_value,NULL)) QTR_TEN_YEAR,
SUM(DECODE(period_type_id,43,perf_value,NULL)) QTR_SINCE_INCEPTION,
MAX(DECODE(period_type_id,520,as_of_date,NULL)) ME_AS_OF_DATE,
SUM(DECODE(period_type_id,520,perf_value,NULL)) ME_ONE_YEAR,
SUM(DECODE(period_type_id,521,perf_value,NULL)) ME_THREE_YEAR,
SUM(DECODE(period_type_id,522,perf_value,NULL)) ME_FIVE_YEAR,
SUM(DECODE(period_type_id,523,perf_value,NULL)) ME_TEN_YEAR,
SUM(DECODE(period_type_id,524,perf_value,NULL)) ME_SINCE_INCEPTION
from fund_perf a, fund b
where a.fund_id=b.fund_id
group by a.fund_id,b.name;

Initially I have created it with Refresh Option as "FAST" and created the MV logs for both the tables. But whenever any inserts was done on detail table the MV was not refreshing. So I have changed the refresh option to FORCE and remove the MV logs and it was refreshing immediately.

Please help me in understanding what was the reason behind that and Is my approach without MV logs and FORCE Refresh option ok?

Thanks a lot

Rahul

... "Note that COUNT(*) must always be present."....  as well.

It could look something like this:


ops$tkyte@ORA10GR2> create table t ( x int, y int, z int, val int );

Table created.

ops$tkyte@ORA10GR2> create materialized view log on t
  2  with sequence, rowid
  3  ( x, y, z, val )
  4  including new values;

Materialized view log created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create materialized view mv
  2  build immediate
  3  refresh fast
  4  on commit
  5  as
  6  select x, y,
  7         sum( decode( z, 1, val ) ) z_1_sum,
  8         max( decode( z, 1, val ) ) z_1_max,
  9         sum( decode( z, 2, val ) ) z_2_sum,
 10         max( decode( z, 2, val ) ) z_2_max,
 11             count(*),
 12             count(  decode( z, 1, val ) ) count_z1,
 13             count(  decode( z, 2, val ) ) count_z2
 14    from t
 15   group by x, y
 16  /

Materialized view created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> set feedback off
ops$tkyte@ORA10GR2> insert into t values ( 1, 1, 1, 1 );
ops$tkyte@ORA10GR2> insert into t values ( 1, 1, 1, 2 );
ops$tkyte@ORA10GR2> insert into t values ( 1, 1, 1, 3 );
ops$tkyte@ORA10GR2> insert into t values ( 1, 1, 2, 3 );
ops$tkyte@ORA10GR2> insert into t values ( 1, 1, 2, 3 );
ops$tkyte@ORA10GR2> insert into t values ( 2, 1, 1, 10 );
ops$tkyte@ORA10GR2> insert into t values ( 2, 1, 1, 20 );
ops$tkyte@ORA10GR2> insert into t values ( 2, 1, 1, 30 );
ops$tkyte@ORA10GR2> insert into t values ( 2, 1, 2, 30 );
ops$tkyte@ORA10GR2> set feedback on
ops$tkyte@ORA10GR2> commit;

Commit complete.

ops$tkyte@ORA10GR2> select * from mv;

   X    Y Z_1_SUM Z_1_MAX Z_2_SUM Z_2_MAX COUNT(*) COUNT_Z1 COUNT_Z2
---- ---- ------- ------- ------- ------- -------- -------- --------
   2    1      60      30      30      30        4        3        1
   1    1       6       3       6       3        5        3        2

2 rows selected.

ops$tkyte@ORA10GR2> insert into t values ( 1, 1, 2, 1 );

1 row created.

ops$tkyte@ORA10GR2> commit;

Commit complete.

ops$tkyte@ORA10GR2> select * from mv;

   X    Y Z_1_SUM Z_1_MAX Z_2_SUM Z_2_MAX COUNT(*) COUNT_Z1 COUNT_Z2
---- ---- ------- ------- ------- ------- -------- -------- --------
   2    1      60      30      30      30        4        3        1
   1    1       6       3       7       3        6        3        3

2 rows selected.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> update t set val = 100;

10 rows updated.

ops$tkyte@ORA10GR2> commit;

Commit complete.

ops$tkyte@ORA10GR2> select * from mv;

   X    Y Z_1_SUM Z_1_MAX Z_2_SUM Z_2_MAX COUNT(*) COUNT_Z1 COUNT_Z2
---- ---- ------- ------- ------- ------- -------- -------- --------
   2    1     300     100     100     100        4        3        1
   1    1     300     100     300     100        6        3        3

2 rows selected.


 

Hi Tom we have materialized view with agreegate function and union in it would you please recommend the type of refresh for it
as base table is having 128 millions of rows and we are using group by and union while creating the materialized view so please help us on this

Thanks in advance

Hi Tom,

I have created a Materialized view with financial year date ranges.

CREATE MATERIALIZED VIEW "FINANCE"."DPR_MV"
PCTFREE 10
PCTUSED 0
MAXTRANS 255
STORAGE (
INITIAL 72K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
) NOLOGGING
TABLESPACE "FINANCE_ERP"
BUILD IMMEDIATE
USING INDEX TABLESPACE "INDX" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 72K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645 )
REFRESH FORCE
ON COMMIT
AS
select fa_code,
Sum(Depr_Amnt) depr_amnt,
add_months(Round(dated,'YEAR'),-6) FY_FROM,
add_months(Round(dated,'YEAR'),6)-1 FY_TO
from fad
WHERE flag = 'DPR'
GROUP BY fa_code ,
add_months(Round(dated,'YEAR'),-6) ,
add_months(Round(dated,'YEAR'),6)-1;


SELECT *
FROM dpr_mv;

FA_CODE DEPR_AMNT FY_FROM FY_TO
--------- --------- --------- ---------
1 7985 01-JUL-05 30-JUN-06
2 31649 01-JUL-05 30-JUN-06
3 73997 01-JUL-05 30-JUN-06
4 3195 01-JUL-05 30-JUN-06
5 13585 01-JUL-05 30-JUN-06
...
..
.

214 rows selected.


1) Will it be Incremental Refresh or Complete Refresh?
2) How can I find out that the Materialized View has been Completely Refreshed or Incrementally Refreshed?

Thanks in Advance
Muhammad Waseem Haroon
waseem.haroon@hotmail.com

Hi Tom,

I had observed that dbms_mview is having two types of procedures dbms_mview.refresh_all and dbms_mview_refresh_all_mviews. What is a difference between them ?
PROCEDURE REFRESH_ALL
PROCEDURE REFRESH_ALL_MVIEWS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NUMBER_OF_FAILURES             BINARY_INTEGER          OUT
 METHOD                         VARCHAR2                IN     DEFAULT
 ROLLBACK_SEG                   VARCHAR2                IN     DEFAULT
 REFRESH_AFTER_ERRORS           BOOLEAN                 IN     DEFAULT
 ATOMIC_REFRESH                 BOOLEAN                 IN     DEFAULT
PROCEDURE REFRESH_DEPENDENT
 Argument Name                  Type                    In/Out Default?

I have created mv as follows

create materialized view tp258_mv
build immediate
refresh complete
with rowid
as
select * from tp258@prodin01std
/

when I do 

18:06:45 SQL> exec dbms_mview.refresh_all;

PL/SQL procedure successfully completed.

But materialized view does not get refreshed. But this view getting refreshed by refresh_all_mviews

Kindly guide me

regards & thanks
pjp 

Hi Tom,

I have an MV that is set :
CREATE MATERIALIZED VIEW MV_DIM_GEOG
REFRESH COMPLETE ON COMMIT
AS
SELECT D.GEOG_ID, D.GEOG_ID_DESC, D.SUB_SITE_ID FROM DW.DW_DIM_GEOG D

The MV is set on a different schema then the master table(GRANT SELECT)

For some reason new data was inserted into that table, but that data is not updated into the MV.

Also when I try to execute :
exec Dbms_Mview.refresh_mv('MV_DIM_GEOG');
I get the following error:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'REFRESH_MV'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

What am I doing wrong ??? :(

Thanks,
that helped

Hi Tom,
We have materialized view replication on Oracle 8i Rel 2 at two sites.
Site A and B on two different users User_A and User_B.

As the users were supposed to run in parallel we implemented MV. Therefore, Site A also the Master Site for Site B and also the Client. The same applies for Site B.

Site A has a DB link a_to_b which connects to B as User_b on database B
and
Site B has a DB link b_to_a which connects to A as User_a on database A .

I have created Refresh Groups on both the sites by the same name called Ref_Grp.

Now the requirement is such:
When a user, say User_a at Site A connects to the Database at he can refresh the local mview group by executing
exec DBMS_refresh.refresh('Ref_Grp').
But we also want to refresh the mviews at site B.
To acheive the same I executed the following:
exec DBMS_refresh.refresh@b_to_a('Ref_Grp')
It gives the error Ora-2041

Kindly state a way with which it can be achieved.
Do I have to write a procedure to achieve the same?

Thanks as always

Sorry Tom it is Oracle 8i on Rel 3 and not Rel 2 as stated above.

Hi Tom,
Got a little strnage problem. I need to physicalize more than 50 views to tables. These views are getting data from MVs. SPace being a secondary problem, I am bit concerned about :
1. Refreshing those tables everynight with million of rows will bring the performance down a lot.

COuld there be any better way of doing this like setting MVs on MVs?

Rgds.

Hi Tom,

I have 2 dbs both on Windows and Oracle 8i Rel 3.

Materialized View Replication has been implemented at both the sites on a 14 tables at each master site.

I have incorporated the mviews into one refresh group.

I have created a procedure at both the sites called Ref_Grp as follows:

Create or replace procedure Ref_Grp as
begin
execute immediate 'execute dbms_refresh.refresh('||'''||Refresh_Group_Name||'''||')';
ed ref_grp;

I want to write the following code:

SQL>exec dbms_refresh.refresh('Refresh_Group_Name'); --Refreshes the local Refresh Group

SQL> exec ref_grp@remote_site; --refreshes the remote Refresh Group

My question is:
Is this a feasible approach. Or should I modify the code to make it execution even better?

Thanks as always
 

Hi Tom,
Thank you for the reply.
As a matter of fact I had changed the above procedure in the database as you mentioned.
The one in my question was written by a another person.
It was saved on this computer as a result I just pasted it without any changes ;)
To be frank, I do not know how to implement your logic about submitting your job idea.
Will be really grateful if you could just guide me on that.
Thanks as always

Dear Tom,

I am trying to create the materilized view using
<exist clause> in the following query. I am getting the
error.

ORA-12015: cannot create a fast refresh materialized view from a complex query

1)
CREATE MATERIALIZED VIEW check_status
PCTFREE 0
STORAGE (INITIAL 8k NEXT 8k PCTINCREASE 0)
BUILD IMMEDIATE
REFRESH FAST
start with sysdate
next sysdate + 1/1440
ENABLE QUERY REWRITE
as select count(*) from
tl a
where
A.STATUS_CODE = 77
and exists (
select 1
from
T2 B
where
a.city_code = b.city_code
and a.country_code = b.country_code
AND a.sbatch_no = b.sbatch_no
AND a.batch_ref = b.batch_ref
AND a.cust_id = b.cust_id
AND (B.EXCEPTION_CLOSED !='Y' OR B.EXCEPTION_CLOSED IS NULL)
AND B.STATUS_CODE = 53
AND B.COUNTRY_CODE='HK'
AND B.CITY_CODE='HKG' )



Same query i have rewrite following way materilized
view is created.but cost of the query is higher than 1st query.

2)
CREATE MATERIALIZED VIEW check_status
PCTFREE 0
STORAGE (INITIAL 8k NEXT 8k PCTINCREASE 0)
BUILD IMMEDIATE
REFRESH FAST
start with sysdate
next sysdate + 1/1440
ENABLE QUERY REWRITE as
select count(1) from tl a,T2 B
where a.status_code=77 and b.status_code=53
and a.country_code = b.country_code
-- and a.city_code=b.city_code
AND a.sbatch_no = b.sbatch_no
AND a.batch_ref = b.batch_ref
AND a.cust_id = b.cust_id
AND (A.EXCEPTION_CLOSED !='Y' OR B.EXCEPTION_CLOSED IS NULL)


please advice me, what wrong in my materialized view ?

Tom,

Actually my need creating fast refresh MV, refreshing every minute.I have tuned the 1st query and query cost is very low compare to 2nd query.I want the 1st query convert to
materialized view. I couldn't create the MV using this
following query. Please advice



ORA-12015: cannot create a fast refresh materialized view from a complex query

1)
CREATE MATERIALIZED VIEW check_status
PCTFREE 0
STORAGE (INITIAL 8k NEXT 8k PCTINCREASE 0)
BUILD IMMEDIATE
REFRESH FAST
start with sysdate
next sysdate + 1/1440
ENABLE QUERY REWRITE
as select count(*) from
tl a
where
A.STATUS_CODE = 77
and exists (
select /*+ NO_UNNEST INDEX(B IND_PYMT_STATUS_SBATCH) */ 1
from
T2 B
where
a.city_code = b.city_code
and a.country_code = b.country_code
AND a.sbatch_no = b.sbatch_no
AND a.batch_ref = b.batch_ref
AND a.cust_id = b.cust_id
AND (B.EXCEPTION_CLOSED !='Y' OR B.EXCEPTION_CLOSED IS NULL)
AND B.STATUS_CODE = 53
AND B.COUNTRY_CODE='HK'
AND B.CITY_CODE='HKG' )

Hi Tom,

Greetings !

I have a 8174 database in which I have around 40 tables. These 40 tables are required on our CRM application
databases which are 9207. The architecture is as under :

8174
----
Table : X
snapshot log on X

9205
----
Table x_mum with one additional column
Materialized view x_mum on prebuilt table as select * from X@8174;

We have data pertaining to last 5 years in table X and hence would like to purge the data so that only one year data is retained. For this, the procedure we would be using is

1. Shutdown and restart the db.
2. Fast Refresh of the materialized view X_MUM. Now the table count of X and MVIEW X_MUM is same.
3. CTAS to create a new table x_req with required data
4. drop Snapshot log and the mview x_mum.
5. Rename x to x_old and x_req to X
6. Create all the required indexes and constraints on new X
7. create the snapshot log

Now the question here is how do I ensure that my Mview is refreshed fast enough to reduce the amount of downtime.
Should we

OPTION 1
--------
1. create the prebuilt table x_mum (9205) using CTAS (select * from x@8174) and
2. create mview x_mum on prebuilt table as select * from X@8174;
or

OPTION 2
--------
1. create the structure of prebuilt table x_mum (9205)
2. create mview x_mum on prebuilt table as select * from X@8174;
3. Do a Complete refresh.

A faster approach, I thought would be to use 3rd option which would be

OPTION 3 (Entire steps)
------------------------
1. Shutdown and restart the db.
2. Fast Refresh of the materialized view X_MUM. Now the table count of X and MVIEW X_MUM is same.
3. drop the snapshot log and the materialized view
4. CTAS to create a new table x_req with required data 8174 and CTAS x_mum_req with required data from x_mum (prebuilt table)
5. Rename x to x_old and x_req to X, x_mum to x_mum_old and x_mum_req to x_mum
6. Create all the required indexes and constraints
7. create the snapshot log
8. create mview x_mum on prebuilt table as select * from X@8174;

Is there any other approach to accomplish this ? Need your assistance on the above so that the entire task can be completed smoothly with less downtime.

Thanks and Regards
VLS

Hi Tom,

Thanks for your reply.

My mview are on prebuilt table and after the purge on the base table, my base table will contain only 40% of the rows whereas the prebuilt table or the mview will have all the records.

The Scenario will be :
TABLE X 100% rows
CTAS X_REQ will have 40% i.e.60% rows will be eliminated.

After the purge on the base table, I need only those 40% rows in the materialized view that are required, hence I have to drop the mview and the prebuilt table as well. so, the question here was :

Option 1
--------
1. drop and recreate the prebuilt table using CTAS
2. create the mview on this prebuilt table

or

Option 2
--------
1. drop and recreate the structure of the prebuilt table.
2. create the mview on this prebuilt table
3. do a complete refresh of the mview

or option 3
-----------
1. Purge the records from the prebuilt table using CTAS..i.e creating a new table with only 40% rows required
2. rename the tables
3. create a materialized view on this new prebuilt table.

Thanks and Regards
VLS

Tom,

We have implemented a data warehouse with specific set of requirements. Since it was performing good all the similar type of applications wants to merge their data in the DW which we have built. Now my problem start, One of the business users requires Pre-Calculated aggregations on measures starting from previous years start date e.g. PYTD(Previous Year To Date), PY (Previous Year), Yesterday etc. Some of the back dated records get updated and hence calculations like PY changes. But these results he wants is across 52 dimensions (none of dimensions has NULL value). So if I built the cube on these the data generated is more than the actual data. Now since none of the dimensions is null I did sum on all the measures(facts) and grouped by all the dimensions. But still the number has hardly come down to 35 millions from 50 millions. Can you please suggest somethig that would help me to do the fast Pre-Calcuted tables. I've got the liberty to make data model changes or any type of change as you may suggest to try out.

Cheers,
AVS.

Tom,

Well one of the important person I've not mentioned is that Precalculated Previous Year, Yesterday are altogether different tables. For PY will have its own table, Yesterday will have its own table. So if you sum only one days data (yesterday) it has less number of records as compared to PYTD. Hence query executed on them are faster.

Regards,
AVS

Sorry in the above statement please ignore my ignorance
PERSOn should be replaced with THING

Tom,

My requirement is to replicate a remote db table_b with my local db table_a. Here is what I gathered should happen from the discussions on your site. Please correct me if I am not understanding it right.

On master site b:

drop materialized view log on table_b

create materialized view log on table_b
tablespace smc_data
with primary key;


On local site a:

drop materialized view mv_table_b

create materialized view mv_table_b
refresh fast with primary key
start with sysdate
next sysdate + 1/(24*60)
as (select * from table_b@orcl_b);

--orcl_b is the db_link to master site b

select count(*) from mv_table_b;
51433 rows


On master site b:

Insert into table_b
(col1, col2, col3, col4, col5, col6, col7, col8, col9, LAST_UPDATE_USER_ID, LAST_UPDATE_DATE_TIME, ROW_CREATE_DATE, MODEL_YEAR_PARTITIONING_KEY)
Values
(51435, 'YKD12352', 8660, 100, 3, 9, 2, 3, 'Y', 'DKSV', TO_DATE('06/14/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/21/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

COMMIT;


On local site a:
select count(*) from mv_table_b;
51434 rows

Notice that it returned the complete set of 51433 + 1 newly inserted row. I thought I was supposed to get only the newly inserted or updated rows...in my case, I expected to see only 1 row.

1) Is my understanding right about seeing only the changed rows
2) If so how would I see only the changes (at master site b) on the local site?
3) To replicate local site a with master site b, I was planning on using a merge statament...any help on how I would write it?

Thanks Tom. I read through some oracle documentation from links on your site. Still a bit fuzzy...:-)

So now I have all the rows in local server side...including the new rows and changed rows from master db table. Now how do I proceed to implement replication between the master db table and the local db table. Can you throw some light with a typical roadmap to this kind of simple replication.

Thanks for clarifying that only the changed rows come from the log. very clever way of implementing....just bring the changes

Now, I have my materialized view on local db that contains all the rows - old unchanged rows, newly inserted rows, updated rows and possibly deleted rows.

How do I identify these changes and How do I push these into my local db table_a

Sorry Tom, I am still lost.

In all my scripts above I have used table_b (master table on remote db). All I have on the local db is MV with all changes and the content of table_a is still old.

Dont I need to push changes from MV to my local table_a?

Are you suggesting - use the MV in place of table_a??

Thanks Tom.

Finally i got it through my thick skull.

You are the best. Have awonderful weekend

Tom,

    I ran a query on a FACT_ORDER table (query below). It used the query_rewrite and redirected to repective Materalized View to get the data. The result returned was 5055341474. When I ran the same query against the base table by changing the where clause (Added: || ''    4th line) The result returned is 5055803575. During time difference of executing the query there was no DDL/DML (not even any select) or anything running on database. Why do I got such different results?

SQL> SELECT sum(FACT_ORDER.AM_NET_CVA_DELTA)
  2  FROM FACT_ORDER
  3  WHERE (FACT_ORDER.IND_IS_REPORTABLE  = 'Y'
  4  AND FACT_ORDER.IND_TO_BE_DELETED = 'N')
  5  AND REPORTING_DATE BETWEEN to_date('01-JAN-2006', 'DD-MON-YYYY') AND to_date('03-JUL-2006', 'DD-MON-YYYY');

SUM(FACT_ORDER.AM_NET_CVA_DELTA)
-----------------------------------------
                               5055341474


Execution Plan
----------------------------------------------------------
Plan hash value: 3626593659

-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                      |     1 |    12 |  1509   (2)| 00:00:22 |       |       |        |      |            |
|   1 |  SORT AGGREGATE                   |                      |     1 |    12 |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR                  |                      |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)            | :TQ10000             |     1 |    12 |            |          |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE                |                      |     1 |    12 |            |          |       |       |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR            |                      |  2389K|    27M|  1509   (2)| 00:00:22 |    50 |    74 |  Q1,00 | PCWC |            |
|*  6 |       MAT_VIEW REWRITE ACCESS FULL| MV_PRECALC_ORDER_SUM |  2389K|    27M|  1509   (2)| 00:00:22 |    50 |    74 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter("MV_PRECALC_ORDER_SUM"."REPORTING_DATE"<=TO_DATE('2006-07-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
      48454  recursive calls
          3  db block gets
      51976  consistent gets
      19291  physical reads
        680  redo size
        244  bytes sent via SQL*Net to client
        240  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
        225  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SELECT sum(FACT_ORDER.AM_NET_CVA_DELTA)
  2  FROM FACT_ORDER
  3  WHERE (FACT_ORDER.IND_IS_REPORTABLE  = 'Y'
  4  AND FACT_ORDER.IND_TO_BE_DELETED || '' = 'N')
  5  AND REPORTING_DATE BETWEEN to_date('01-JAN-2006', 'DD-MON-YYYY') AND to_date('03-JUL-2006', 'DD-MON-YYYY');

SUM(FACT_ORDER.AM_NET_CVA_DELTA)
-----------------------------------------
                               5055803575


Execution Plan
----------------------------------------------------------
Plan hash value: 3220554619

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |     1 |    16 | 66552   (1)| 00:15:32 |       |       |        |      |            |
|   1 |  SORT AGGREGATE        |            |     1 |    16 |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR       |            |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000   |     1 |    16 |            |          |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |            |     1 |    16 |            |          |       |       |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |            |  6031K|    92M| 66552   (1)| 00:15:32 |    12 |    14 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| FACT_ORDER |  6031K|    92M| 66552   (1)| 00:15:32 |    12 |    14 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter("REPORTING_DATE">=TO_DATE('2006-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "FACT_ORDER"."IND_IS_REPORTABLE"='Y' AND "FACT_ORDER"."IND_TO_BE_DELETED"||''='N' AND
              "REPORTING_DATE"<=TO_DATE('2006-07-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
       2538  recursive calls
          3  db block gets
     818105  consistent gets
     770956  physical reads
        588  redo size
        259  bytes sent via SQL*Net to client
        240  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         40  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>


Regards,
AVS 

how to write a trigger that fires when a select statement is executed.

how to write a trigger that fires when a select statement is executed.

The MV's were completely refreshed before I fired any query. Also there was no DDl/DML fired in between the two sql statements.

SQL> show parameter query

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      enforced
 

I am try to create the below materialized view with fast refresh. It contains outer joins. The inner table columns are primary key columns.

SQL> alter table on_ord_qty add constraint on_ord_pk primary key ( store_id, item_id );

Table altered.

SQL> alter table alloctab add constraint alloctab_pk primary key ( id_str_rt, id_itm );

Table altered.

-- The materialized view creation
SQL> create materialized view dis_mv
  2     PARTITION BY HASH(store_id)
  3     PARTITIONS 8
  4     STORE IN (DISCOVERER_DATA,
  5               DISCOVERER_DATA,
  6               DISCOVERER_DATA,
  7               DISCOVERER_DATA,
  8               DISCOVERER_DATA,
  9               DISCOVERER_DATA,
 10               DISCOVERER_DATA,
 11               DISCOVERER_DATA)
 12    NOCACHE
 13    NOPARALLEL
 14    build immediate
 15    refresh fast on demand
 16    as
 17   SELECT    /*+ index(itmloc pk_as_itm_rtl_str) index(soh pk_rk_store_item_soh) index(ris pk_rk_item_supplier) index(itm pk_as_itm)*/
 18                psr.id_str_rt    store_id,
 19                psr.rowid        psr_rid,
 20                po.nm_orgn       store_name,
 21                po.rowid         ps_rid,
 22                soh.id_itm       item_id,
 23                soh.rowid        soh_rid,
 24                SUBSTR(itm.id_strc_mr,1,INSTR(itm.id_strc_mr,':')-1) Dept,
 25                soh.id_itm ||'-'|| itm.de_itm id_desc,
 26                itm.de_itm       item_description,
 27                itm.lu_uom_dft   default_uom,
 28                itm.rowid        itm_rowid,
 29                itmloc.id_spr    primary_supplier,
 30                itmloc.rowid     itmloc_rid,
 31                ps.nm_spr        vendor_name,
 32                ps.rowid         ps_rowid,
 33                itmloc.id_spr ||'-'|| ps.nm_spr VDesc,
 34                ris.vpn          vendor_style,
 35                ris.rowid        ris_rid,
 36                itmloc.repn_type repn_type,
 37                soh.total_quantity soh,
 38                soh.in_transit_quantity in_transit_quantity,
 39                soh.adjust_unavail_qty unavailable_quantity,
 40                aq.allocated_quantity,
 41                aq.rowid       aq_rid,
 42                ooq.on_order_quantity,
 43                ooq.rowid      ooq_rid,
 44                CASE WHEN soh.total_quantity = 0 THEN 'ZERO'
 45                     WHEN soh.total_quantity < 0 THEN 'NEGATIVE'
 46                END status
 47          FROM sim.pa_str_rtl        psr,
 48               sim.as_itm_rtl_str    itmloc,
 49               sim.as_itm            itm,
 50               sim.rk_item_supplier  ris,
 51               sim.pa_spr            ps,
 52               sim.rk_store_item_soh soh,
 53               sim.pa_orgn           po,
 54               sim.alloctab      aq,
 55               sim.on_ord_qty    ooq
 56         WHERE itmloc.id_str_rt = psr.id_str_rt
 57           AND itmloc.id_itm    = itm.id_itm
 58           AND ooq.store_id(+)     = soh.id_str_rt
 59           AND ooq.item_id(+)      = soh.id_itm
 60           AND itmloc.id_itm    = ris.id_itm
 61           AND itmloc.id_spr    = ris.id_spr
 62           AND ris.id_spr       = ps.id_spr
 63           AND aq.id_str_rt(+)     = soh.id_str_rt
 64           AND aq.id_itm(+)        = soh.id_itm
 65           AND soh.id_str_rt    = itmloc.id_str_rt
 66           AND soh.id_str_rt IN ('10','4','79','83','5','7','8','9','11','13','15','16','17','18','20','21','24','25','26','34','78','84','85','86'
,'2')
 67           AND soh.id_itm       = itmloc.id_itm
 68           AND psr.id_prty     = po.id_prty_orgn;
        FROM sim.pa_str_rtl        psr,
                 *
ERROR at line 47:
ORA-12015: cannot create a fast refresh materialized view from a complex query

Why is that. I have logs on all my base tables to.
I previously created this materialized view with all inner joins but had to change to some outer joins.
 

Well I really just wanted confirmation that those primary keys ARE UNIQUE CONSTRAINTS?

If so I should be able to fast refresh on those columns.

The below is what the package came back with.
Saying there is a 'non-AND conjunction' is confusing because the IN was there when I could fast refresh.
Same with the filter conditions. Its adding exceptions that didnt  matter before.

SQL> @readcap

CAPABILITY_NAME                P REL_TEXT
------------------------------ - ------------------------
MSGTXT
--------------------------------------------------------------------------
PCT                            N


REFRESH_COMPLETE               Y


REFRESH_FAST                   N


REWRITE                        N


PCT_TABLE                      N SIM.PA_S
relation is not a partitioned table

PCT_TABLE                      N SIM.AS_I
PCT not supported with this type of partitioning

PCT_TABLE                      N SIM.AS_I

CAPABILITY_NAME                P REL_TEXT
------------------------------ - ------------------------
MSGTXT
--------------------------------------------------------------------------
relation is not a partitioned table

PCT_TABLE                      N SIM.RK_I
relation is not a partitioned table

PCT_TABLE                      N SIM.PA_S
relation is not a partitioned table

PCT_TABLE                      N SIM.RK_S
PCT not supported with this type of partitioning

PCT_TABLE                      N SIM.PA_O
relation is not a partitioned table

PCT_TABLE                      N SIM.ALLO
relation is not a partitioned table

PCT_TABLE                      N SIM.ON_O
relation is not a partitioned table

CAPABILITY_NAME                P REL_TEXT
------------------------------ - ------------------------
MSGTXT
--------------------------------------------------------------------------

REFRESH_FAST_AFTER_INSERT      N
non-AND conjunction in WHERE clause

REFRESH_FAST_AFTER_INSERT      N
mv uses a filter condition in the WHERE or HAVING clause

REFRESH_FAST_AFTER_INSERT      N
outer join in mv

REFRESH_FAST_AFTER_INSERT      N
one or more joins present in mv

REFRESH_FAST_AFTER_ONETAB_DML  N
see the reason why REFRESH_FAST_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ANY_DML     N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled


CAPABILITY_NAME                P REL_TEXT
------------------------------ - ------------------------
MSGTXT
--------------------------------------------------------------------------
REFRESH_FAST_PCT               N
PCT is not possible on any of the detail tables in the mater

REWRITE_FULL_TEXT_MATCH        N
query rewrite is disabled on the materialized view

REWRITE_PARTIAL_TEXT_MATCH     N
query rewrite is disabled on the materialized view

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 a
 

My first attempt at small with respect to the issue. If this is too large let me know I will try to see what I can do.

CREATE TABLE RK_ALLOCATIONS1
(
ALLOCATION_ID NUMBER(10) NOT NULL,
ITEM_ID VARCHAR2(128 BYTE) NOT NULL,
DELIVERY_DATE DATE,
QUANTITY NUMBER(20,4),
STORE_ID VARCHAR2(128 BYTE) NOT NULL
);
ALTER TABLE RK_ALLOCATIONS1 ADD
CONSTRAINT PK_RK_ALLOCATIONS1 PRIMARY KEY (ALLOCATION_ID, ITEM_ID, STORE_ID);
CREATE TABLE RK_STORE_ITEM_SOH1
(
ID_ITM VARCHAR2(128 BYTE) NOT NULL,
ID_STR_RT VARCHAR2(128 BYTE) NOT NULL,
TOTAL_QUANTITY NUMBER(20,4) DEFAULT 0 NOT NULL,
IN_TRANSIT_QUANTITY NUMBER(20,4),
ADJUST_UNAVAIL_QTY NUMBER(20,4)
);
ALTER TABLE RK_STORE_ITEM_SOH1 ADD
CONSTRAINT PK_RK_STORE_ITEM_SOH1 PRIMARY KEY (ID_ITM, ID_STR_RT);
CREATE TABLE PA_ORGN1
(
NM_ORGN VARCHAR2(40 BYTE),
ID_PRTY_ORGN VARCHAR2(128 BYTE) NOT NULL
);
ALTER TABLE PA_ORGN1 ADD
CONSTRAINT PK_PA_ORGN11 PRIMARY KEY (ID_PRTY_ORGN);
CREATE TABLE PA_SPR1
(
ID_SPR VARCHAR2(128 BYTE) NOT NULL,
NM_SPR VARCHAR2(40 BYTE)
);
ALTER TABLE PA_SPR1 ADD
CONSTRAINT PK_PA_SPR1 PRIMARY KEY (ID_SPR);
CREATE TABLE AS_ITM_RTL_STR1
(
ID_STR_RT VARCHAR2(128 BYTE) NOT NULL,
ID_ITM VARCHAR2(128 BYTE) NOT NULL,
ID_SPR VARCHAR2(128 BYTE),
REPN_TYPE VARCHAR2(4 BYTE)
);
ALTER TABLE AS_ITM_RTL_STR1 ADD
CONSTRAINT PK_AS_ITM_RTL_STR1 PRIMARY KEY (ID_ITM, ID_STR_RT );
CREATE TABLE PA_STR_RTL1
(
ID_STR_RT VARCHAR2(128 BYTE) NOT NULL,
ID_PRTY VARCHAR2(128 BYTE) NOT NULL
);
ALTER TABLE PA_STR_RTL1 ADD
CONSTRAINT PK_PA_STR_RTL1 PRIMARY KEY (ID_STR_RT);
CREATE UNIQUE INDEX PA_STR_RTL_I11 ON PA_STR_RTL1
(ID_PRTY);
CREATE TABLE RK_ITEM_SUPPLIER1
(
ID_ITM VARCHAR2(128 BYTE) NOT NULL,
ID_SPR VARCHAR2(128 BYTE) NOT NULL
);
ALTER TABLE RK_ITEM_SUPPLIER1 ADD
CONSTRAINT PK_RK_ITEM_SUPPLIER1 PRIMARY KEY (ID_ITM, ID_SPR);
CREATE TABLE AS_ITM1
(
ID_ITM VARCHAR2(128 BYTE) NOT NULL,
DE_ITM VARCHAR2(255 BYTE),
LU_UOM_DFT VARCHAR2(40 BYTE)
);
ALTER TABLE AS_ITM1 ADD
CONSTRAINT PK_AS_ITM1 PRIMARY KEY (ID_ITM);

create table alloctab1 as
SELECT soh.id_str_rt,
soh.id_itm,
SUM (rka.quantity) allocated_quantity
FROM rk_allocations rka,
rk_store_item_soh soh
WHERE rka.delivery_date > SYSDATE
AND rka.store_id = soh.id_str_rt
AND rka.store_id = '10'
AND rka.item_id = soh.id_itm
GROUP BY soh.id_str_rt,
soh.id_itm;

alter table alloctab1 add constraint alloctab1_pk primary key ( id_str_rt, id_itm );

insert into as_itm1 values ('22','33','each');

insert into rk_allocations1 values ( 1,'22','10-JUN-05',500,'10');

insert into rk_store_item_soh1 values (22,'10',1000,5000,500);

insert into pa_orgn1 values('nm_orgn','20');

insert into pa_spr1 values('30','nm_spr');

insert into as_itm_rtl_str1 values('10','22','30','patl');

insert into pa_str_rtl1 values ('10','20');

insert into rk_item_supplier1 values('22','30');

DROP SNAPSHOT LOG ON AS_ITM1;

CREATE MATERIALIZED VIEW LOG ON AS_ITM1
WITH ROWID, PRIMARY KEY, SEQUENCE
(DE_ITM,LU_UOM_DFT) INCLUDING NEW VALUES;

DROP SNAPSHOT LOG ON AS_ITM_RTL_STR1;

CREATE MATERIALIZED VIEW LOG ON AS_ITM_RTL_STR1
WITH ROWID, PRIMARY KEY, SEQUENCE
(ID_SPR,REPN_TYPE) INCLUDING NEW VALUES;

DROP SNAPSHOT LOG ON PA_ORGN1;

CREATE MATERIALIZED VIEW LOG ON PA_ORGN1
WITH ROWID, PRIMARY KEY, SEQUENCE
(NM_ORGN) INCLUDING NEW VALUES;

DROP SNAPSHOT LOG ON PA_SPR1;

CREATE MATERIALIZED VIEW LOG ON PA_SPR1
WITH ROWID, PRIMARY KEY, SEQUENCE
(NM_SPR) INCLUDING NEW VALUES;

DROP SNAPSHOT LOG ON PA_STR_RTL1;

CREATE MATERIALIZED VIEW LOG ON PA_STR_RTL1
WITH ROWID, PRIMARY KEY, SEQUENCE INCLUDING NEW VALUES;


DROP SNAPSHOT LOG ON RK_ITEM_SUPPLIER1;

CREATE MATERIALIZED VIEW LOG ON RK_ITEM_SUPPLIER1
WITH ROWID, PRIMARY KEY, SEQUENCE
INCLUDING NEW VALUES;

DROP SNAPSHOT LOG ON RK_STORE_ITEM_SOH1;

CREATE MATERIALIZED VIEW LOG ON RK_STORE_ITEM_SOH1
WITH ROWID, PRIMARY KEY, SEQUENCE
(TOTAL_QUANTITY) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON alloctab1
NOCACHE
NOPARALLEL
WITH ROWID, PRIMARY KEY, SEQUENCE
(allocated_quantity) INCLUDING NEW VALUES;

create materialized view tst_mv
PARALLEL
PARTITION BY HASH(store_id)
PARTITIONS 8
NOCACHE
NOLOGGING
build immediate
refresh fast on demand
as
SELECT psr.id_str_rt store_id,
psr.rowid psr_rid,
po.nm_orgn store_name,
po.rowid ps_rid,
soh.id_itm item_id,
soh.rowid soh_rid,
itm.de_itm item_description,
itm.lu_uom_dft default_uom,
itm.rowid itm_rowid,
itmloc.id_spr primary_supplier,
itmloc.rowid itmloc_rid,
ps.nm_spr vendor_name,
ps.rowid ps_rowid,
ris.rowid ris_rid,
itmloc.repn_type repn_type,
soh.total_quantity soh,
soh.in_transit_quantity in_transit_quantity,
soh.adjust_unavail_qty unavailable_quantity,
aq.allocated_quantity,
aq.rowid aq_rid,
CASE WHEN soh.total_quantity = 0 THEN 'ZERO'
WHEN soh.total_quantity < 0 THEN 'NEGATIVE'
END status
FROM pa_str_rtl1 psr,
as_itm_rtl_str1 itmloc,
as_itm1 itm,
rk_item_supplier1 ris,
pa_spr1 ps,
rk_store_item_soh1 soh,
pa_orgn1 po,
alloctab1 aq
WHERE itmloc.id_str_rt = psr.id_str_rt
AND itmloc.id_itm = itm.id_itm
AND itmloc.id_itm = ris.id_itm
AND itmloc.id_spr = ris.id_spr
AND ris.id_spr = ps.id_spr
AND aq.id_str_rt(+) = soh.id_str_rt
AND aq.id_itm(+) = soh.id_itm
AND soh.id_str_rt = itmloc.id_str_rt
AND soh.id_str_rt ='10'
AND soh.id_itm = itmloc.id_itm
AND psr.id_prty = po.id_prty_orgn;