We use Peoplesoft Finacial 8.4, on-line transaction version and Oracle enterprise edition release 9.2.0.2 version for our applications.

We are evaluating the Oracle updateable Materialized view product to see whether we can use it in our environment, I appreciate your response on the following questions:

1. Do anyone use updateable Materialized view on the PeopleSoft Financial 8.4 on-line transaction environment? Is there any issue for updateable Materialized view operating on this kind of environment? Or does anyone encounter the problem using updateable materialized view on PeopleSoft application environment?

2. How does updateable Materialized view monitor the DML activities on the materialized view table? Is there a way I can log all the DML activities on the materialized view table?

3. Materialized view log table data will be purged by Oracle once the data is successfully refreshed to the other side of database. This means if your Materialized view refresh interval is 5 minutes, your data in the log table will be gone after 5 minutes (assume the data is successfully refreshed to the other side of database). There is no API to make Materialized view turn off purging mechanism. Am I right on this isse?

4. Materialized view log uses MLOG$_materialized_table_name to store the DML change to the materialized view table.

5. The MLOG$_materialized_table_name is defined as the Oracle internal structure; the data in that table is not understandable by users. For example, I have a materialized view table called ps_zone_inv, the table definition for ps_zone_inv is as follows:

SQL> desc ps_zone_inv
Name Null? Type
----------------------------------- -------- ----------------------------
SETID NOT NULL VARCHAR2(5)
ZONE_CD NOT NULL VARCHAR2(6)
DESCR NOT NULL VARCHAR2(30)
DESCRSHORT NOT NULL VARCHAR2(10)

The MLOG$_ps_zone_inv table definition defined by Oracle is as follows:

SQL> desc mlog$_ps_zone_inv;
Name Null? Type
-------------------------------- -------- ----------------------------
SETID VARCHAR2(5)
ZONE_CD VARCHAR2(6)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)

When I insert the following row of data to ps_zone_inv and commit the inserting:

SQL> insert into ps_zone_inv values ('df6', '6', '6', '6');

1 row created.

The MLOG$_ps_zone_inv log table stores my inserting data as follows:

SQL> select * from mlog$_ps_zone_inv;

SETID ZONE_C SNAPTIME$ DML OLD CHANGE_VECTOR$$
------ -------- ------------ ----- ---- -----------------------
df6 6 01-JAN-00 I N FE

From MLOG$_ps_zone_inv log table, I can only get the setid and zone_cd data, how about other columns’s data, why the change_vector$$ does not contain all the DML data? Why the "CHANGE_VECTOR$$" store the data "FE" instead of " 'df', '6', '6', '6' " the whole inserted data? I appreciate you response.
Thank you.