Implementing Materialized View Logs in the Oracle Database

To implement Materialized View Logs in the Oracle database, first determine if you have created customized (non PeopleSoft delivered) Materialized View Logs for a base table by entering the following SQL statement against the Oracle database:

SELECT * FROM USER_SNAPSHOT_LOGS WHERE MASTER='<BASE TABLE>’;

If a customized view log exists, you must configure it to support PeopleSoft requirements. You must also create a DUMMY materialized view to prevent the Materialized View Log data from being purged.

Note: If the existing view log is one of the PeopleSoft delivered view logs, you do not need to configure it. See the following section for a list of delivered Materialized View Logs for Campus Solutions Warehouse source tables.

Delivered Materialized View Logs for Campus Solutions Warehouse Source Tables

The following table provides a list of delivered Materialized View Logs and their corresponding source tables for the Campus Solutions Warehouse. The table also provides the EPM fact tables related to a Materialized View Log; these fact tables track deleted source records with the help of the Materialized View Log.

Campus Solutions Source Table	Delivered Materialized View Log	Corresponding EPM Fact Table
PS_ACAD_DEGR	MLOG$_PS_ACAD_DEGR	PS_F_DEGREES
PS_ACAD_DEGR_HONS	MLOG$_PS_ACAD_DEGR_HONS
PS_ACAD_DEGR_PLAN	MLOG$_PS_ACAD_DEGR_PLAN
PS_ACAD_DEGR_SPLN	MLOG$_PS_ACAD_DEGR_SPLN
PS_ACAD_PLAN	MLOG$_PS_ACAD_PLAN	PS_F_ACADPLAN_SUMM PS_F_INST_SUMM
PS_ACAD_PROG	MLOG$_PS_ACAD_PROG	PS_F_ACAD_PROG_DTL PS_F_ACADPLAN_SUMM
PS_ADM_APP_CAR_SEQ	MLOG$_PS_ADM_APP_CAR_SEQ	PS_F_ADM_APPL_EVAL PS_F_STDNT_RESP
PS_ADM_APPL_CMP	MLOG$_PS_ADM_APPL_CMP	PS_F_ADM_APPL_EVAL
PS_ADM_APPL_DATA	MLOG$_PS_ADM_APPL_DATA	PS_F_ADM_APPL PS_F_ADM_APPL_EVAL PS_F_ADM_APPL_STAT PS_F_ADM_FUNNEL PS_F_STDNT_RESP
PS_ADM_APPL_EVAL	MLOG$_PS_ADM_APPL_EVAL	PS_F_ADM_APPL_EVAL
PS_ADM_APPL_MATLS	MLOG$_PS_ADM_APPL_MATLS	PS_TF_EXT_ACAD_SUM PS_TF_STU_TSTSCORE
PS_ADM_APPL_PLAN	MLOG$_PS_ADM_APPL_PLAN	PS_F_ADM_APPL PS_F_ADM_APPL_STAT
PS_ADM_APPL_PROG	MLOG$_PS_ADM_APPL_PROG	PS_F_ADM_APPL_EVAL PS_F_ADM_APPL_STAT PS_F_STDNT_RESP
PS_ADM_PRSPCT_CAR	MLOG$_PS_ADM_PRSPCT_CAR	PS_F_ADM_FUNNEL PS_F_STU_RECRT
PS_ADM_PRSPCT_PLAN	MLOG$_PS_ADM_PRSPCT_PLAN	PS_F_ADM_FUNNEL PS_F_STU_RECRT
PS_ADM_PRSPCT_PROG	MLOG$_PS_ADM_PRSPCT_PROG	PS_F_ADM_FUNNEL PS_F_STU_RECRT
PS_ADM_PRSPCT_SBPL	MLOG$_PS_ADM_PRSPCT_SBPL	PS_F_STU_RECRT
PS_CAMPUS_EVENT	MLOG$_PS_CAMPUS_EVENT	PS_F_CAMPUS_EVENT PS_F_CAMP_EVNT_MTG
PS_CAMPUS_MTG_SEL	MLOG$_PS_CAMPUS_MTG_SEL	PS_F_CAMP_EVNT_MTG
PS_COMMUNICATION	MLOG$_PS_COMMUNICATION	PS_F_COMM_EXT_ORG PS_F_COMM_PERSON
PS_CLASS_INSTR	MLOG$_PS_CLASS_INSTR	PS_F_CLASS_MTG_PAT
PS_CLASS_MTG_PAT	MLOG$_PS_CLASS_MTG_PAT	PS_F_CLASS_MTG_PAT
PS_CLASS_TBL	MLOG$_PS_CLASS_TBL	PS_F_CLASS_MTG_PAT
PS_EVENT_MTG	MLOG$_PS_EVENT_MTG	PS_F_CAMP_EVNT_MTG
PS_EXT_ACAD_SUM	MLOG$_PS_EXT_ACAD_SUM	PS_F_EXT_ACAD_SUMM
PS_FACILITY_TBL	MLOG$_PS_FACILITY_TBL	PS_F_CLASS_MTG_PAT
PS_INSTR_TERM_DTL	MLOG$_PS_INSTR_TERM_DTL	PS_F_CLASS_INSTRCT
PS_INSTRUCTOR_TERM	MLOG$_PS_INSTRUCTOR_TERM	PS_F_CLASS_INSTRCT
PS_PERSON_CHECKLST	MLOG$_PS_PERSON_CHECKLST	PS_F_CHKLST_ORG PS_F_CHKLST_PERSON
PS_STDNT_AWARDS	MLOG$_PS_STDNT_AWARDS	PS_F_AWD_DISB
PS_STDNT_AWRD_DISB	MLOG$_PS_STDNT_AWRD_DISB	PS_F_AWD_DISB
PS_STDNT_CAR_TERM	MLOG$_PS_STDNT_CAR_TERM	PS_F_TERM_ENRLMT
PS_STDNT_RESPONSE	MLOG$_PS_STDNT_RESPONSE	PS_F_STDNT_RESP
PS_STDNT_TEST_COMP	MLOG$_PS_STDNT_TEST_COMP	F_EXT_TESTSCORE
PS_TERM_TBL	MLOG$_PS_TERM_TBL	PS_F_CLASS_ENRLMT PS_F_TERM_ENRLMT
PS_TRNS_CRSE_SCH	MLOG$_PS_TRNS_CRSE_SCH	PS_TF_TRNS_CREDIT
PS_TRNS_OTHR_MODEL	MLOG$_PS_TRNS_OTHR_MODEL	PS_TF_TRNS_CREDIT
PS_TRNS_TEST_MODEL	MLOG$_PS_TRNS_TEST_MODEL	PS_TF_TRNS_CREDIT

Configuring Non-PeopleSoft Delivered Materialized View Logs

If a non-PeopleSoft delivered Materialized View Log exists for the master table of a materialized view, you must configure the view log to support the following requirements:

All base table key columns
ROWID option
SEQUENCE option
INCLUDING NEW VALUES option

For example:

CREATE MATERIALIZED VIEW LOG ON <BASE TABLE> WITH ROWID, SEQUENCE (<BASE TABLE KEY COLUMNS>) INCLUDING NEW VALUES;

Additionally, you must create a DUMMY materialized view table for each materialized view you created. The fast refresh process automatically purges Materialized View Log data when the materialized view is refreshed, so creating a related dummy materialized view will prevent the Materialized View Log data from being purged.

To create a DUMMY materialized view, you can enter a SQL statement against the Oracle database similar to the following:

CREATE MATERIALIZED VIEW <BASE TABLE>_DMV
REFRESH FAST WITH ROWID AS
SELECT <BASE TABLE COLUMN NAME> FROM <BASE TABLE> WHERE 1=2;

OLTP Table Security

Materialized View Log ETL jobs delete Materialized View Log data once the job completes. This normally requires a user to have DELETE permissions, which may jeopardize source data. As such, PeopleSoft provides an additional user (SCHEMA), which has READ and DELETE rights to Materialized View Log data only. This user cannot select or delete records from master tables.

PeopleSoft also delivers two new environment variables, MLOG_USERNAME, MLOG_PASSWORD, and MLOG_SCHEMA for the new user.

Note: Since Materialized View Log ETL jobs delete Materialized View Log data once the job completes, you should refresh non-PeopleSoft materialized views before running the ETL jobs.