PeopleSoft Financials Ledger Partitioning Recommendations

I have written previously about partitioning the PS_LEDGER table in Financials for the benefit of General Ledger (GL) reporting.  I have always recommended top-level range partitioning on the combination of FISCAL_YEAR and ACCOUNTING_PERIOD with separate partitions for each accounting period.  It also leaves sub-partitioning available to use another column, usually LEDGER.

• PeopleSoft DBA blog: nVision Performance Tuning: 4. Partitioning of Ledger, Ledger Budget, and Summary Ledger Tables
• Go-Faster blog: Retrofitting Partitioning into Existing Applications: Example 1. General Ledger

However, recent research into partition change tracking (PCT) and materialized views has made me question that advice.  The decision is not as clear cut.

• Go-Faster blog: Partition Change Tracking During Materialized View Refresh and Query Rewrite

Summary Ledgers or Materialized Views

You can create summary ledger tables in PeopleSoft Financials that are incrementally maintained by batch processes, and then nVision reports can reference the summary ledgers instead.  If the summary ledgers are not up to date, then the reports will produce out of date results.

Similarly, materialized views can be used in an Oracle database to create pre-generated reporting tables.  An application can reference a materialized view directly, or the database can dynamically rewrite submitted SQL queries to use the materialized view if they are sufficiently similar to the query that created the materialized view and if the optimizer judges that it is cheaper to query the materialized view.  By default, the database will check that the materialized view is up to date, that is no change has been made to the underlying tables since the last refresh commenced, before it can rewrite the SQL query.  So the query will always return the same data, but if the materialized view is out of date you don't get the performance improvement.

You can optionally choose to configure the database to write SQL queries to use stale materialized views by setting QUERY_REWRITE_INTEGRITY=stale_tolerated at either database or session-level.  

Materialized views can be created for the nVision queries that you wish to optimise, and no further code change is required because the database will rewrite the SQL.  You can see a typical example of this in my blog about PCT.

Partition Change Tracking is a mechanism the Oracle database uses to 'track freshness to a finer grain than the entire materialized view'.  It can identify which partitions and subpartitions are fresh and can be used for query rewrite, and to refresh just the partitions that are stale or that contain stale sub-partitions.  

Alternatives for Partitioning PS_LEDGER

If you wish to create materialized views on the main ledger table, and rely upon query rewrite, and keep the materialized views up to date with respect to the ledger table, and only use them when they are up to date, then you probably want PCT to help with both rewrite and refresh.

1. Multi-column composite partitioning 

I usually like to range partition PS_LEDGER on the combination of FISCAL_YEAR and ACCOUNTING_PERIOD to produce a separate range partition for each accounting period.   Partition pruning works very effectively with the SQL generated by nVision.  It also allows sub-partitioning on another column, usually LEDGER.  This is desirable when a system has multiple actuals ledgers, and especially since 9.2 where the budget data is also stored in PS_LEDGER rather than PS_LEDGER_BUDG.  

However, amongst the documented restrictions of PCT is that partitioned tables must use either range, or list partitioning, or composite partitioning with range or list as the top-level partitioning strategy.  Also, the top-level partition key must consist of only a single column (see Database Data Warehousing Guide -> Advanced Materialized Views -> About Partition Change Tracking)

If I want to use query rewrite to materialized views for queries on LEDGER table then I have a few choices.

• If I stick with multi-column range partitioning, then I cannot use PCT.  I must either keep the materialized views fresh, or the queries remain on the ledger table.  Any update to any partition in the ledger table will render the entire materialized view stale and prevent query rewrite.  Many customers run a suite of nVision reportbooks overnight.  I could set QUERY_REWRITE_INTEGRITY=stale_tolerated at session-level for the report books processes using a trigger on the process scheduler request table (PSPRCSRQST) - see Setting Oracle Session Parameters for Specific Process Scheduler Processes.  In this case, I would have to take responsibility for refreshing the materialized views prior to running, say, a suite of report processes.  This is effectively the same situation as using summary ledgers, but without code change to the reports.
• I have created materialized views on summary ledger tables in order to provide compressed copies of the summary ledger.  Again, in this case, the materialized views had to be refreshed after the summary ledger maintenance process.
• Or, I have to produce a simpler partitioning strategy for the ledger table that is still compatible with PCT.

2. Composite Range-Range Partitioning on FISCAL_YEAR and ACCOUNTING_PERIOD

I could composite partition both the LEDGER table and the materialized views by FISCAL_YEAR and ACCOUNTING_PERIOD, but then I cannot further subpartition by other columns.  This would degrade queries on smaller ledgers that could not be rewritten to dedicated materialized views.

CREATE TABLE ps_ledger
(business_unit VARCHAR2(5) NOT NULL
…
) PCTFREE 10 PCTUSED 80
PARTITION BY RANGE (FISCAL_YEAR) 
SUBPARTITION BY RANGE (ACCOUNTING_PERIOD)
SUBPARTITION TEMPLATE
(SUBPARTITION ap_bf VALUES LESS THAN (1) 
,SUBPARTITION ap_01 VALUES LESS THAN (2) 
,SUBPARTITION ap_02 VALUES LESS THAN (3) 
,SUBPARTITION ap_03 VALUES LESS THAN (4) 
,SUBPARTITION ap_04 VALUES LESS THAN (5) 
,SUBPARTITION ap_05 VALUES LESS THAN (6) 
,SUBPARTITION ap_06 VALUES LESS THAN (7) 
,SUBPARTITION ap_07 VALUES LESS THAN (8) 
,SUBPARTITION ap_08 VALUES LESS THAN (9) 
,SUBPARTITION ap_09 VALUES LESS THAN (10) 
,SUBPARTITION ap_10 VALUES LESS THAN (11) 
,SUBPARTITION ap_11 VALUES LESS THAN (12) 
,SUBPARTITION ap_12 VALUES LESS THAN (13) 
,SUBPARTITION ap_cf VALUES LESS THAN (MAXVALUE))
 (PARTITION ledger_2018 VALUES LESS THAN (2019) PCTFREE 0 COMPRESS
,PARTITION ledger_2019 VALUES LESS THAN (2020) PCTFREE 0 COMPRESS
,PARTITION ledger_2020 VALUES LESS THAN (2021) 
,PARTITION ledger_2021 VALUES LESS THAN (2022) 
)
ENABLE ROW MOVEMENT NOLOGGING
/

The materialized view will be similarly composite partitioned.  Note that I have created the materialized view for specific fiscal years and for a specific ledger.  I would create materialized views for each combination of ledger and each distinct set of analysis columns that are regularly reported upon.

CREATE MATERIALIZED VIEW mv_ledger_2020
PARTITION BY RANGE (FISCAL_YEAR)
SUBPARTITION BY RANGE (ACCOUNTING_PERIOD)
SUBPARTITION TEMPLATE
(SUBPARTITION ap_bf VALUES LESS THAN (1) 
,SUBPARTITION ap_01 VALUES LESS THAN (2) 
,SUBPARTITION ap_02 VALUES LESS THAN (3) 
,SUBPARTITION ap_03 VALUES LESS THAN (4) 
,SUBPARTITION ap_04 VALUES LESS THAN (5) 
,SUBPARTITION ap_05 VALUES LESS THAN (6) 
,SUBPARTITION ap_06 VALUES LESS THAN (7) 
,SUBPARTITION ap_07 VALUES LESS THAN (8) 
,SUBPARTITION ap_08 VALUES LESS THAN (9) 
,SUBPARTITION ap_09 VALUES LESS THAN (10) 
,SUBPARTITION ap_10 VALUES LESS THAN (11) 
,SUBPARTITION ap_11 VALUES LESS THAN (12) 
,SUBPARTITION ap_12 VALUES LESS THAN (13) 
,SUBPARTITION ap_cf VALUES LESS THAN (MAXVALUE))
(PARTITION ledger_2019 VALUES LESS THAN (2020)
,PARTITION ledger_2020 VALUES LESS THAN (2021)
) PCTFREE 0 COMPRESS PARALLEL
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT business_unit, account, chartfield1, fiscal_year, accounting_period,
sum(posted_total_amt) posted_total_amt
FROM ps_ledger
WHERE fiscal_year >= 2019
AND   ledger = 'ACTUALS'
AND   currency_cd = 'GBP'
GROUP BY business_unit, account, chartfield1, fiscal_year, accounting_period
/

Note that I have equality criteria on LEDGER and CURRENCY_CD in the materialized view, but I have not included those columns in the select clause, and so they are not in the view.  Oracle can still rewrite queries to use this materialized view and that specify the same criteria on PS_LEDGER

PCT will determine whether any unpruned partitions or subpartitions are stale and if so prevent query rewrite.  It is documented behaviour that the materialized view refresh will truncate and rebuild the whole top-level partitions, in this case each fiscal year, where the partition or any constituent sub-partitions are stale.  So even if just one subpartition, for one accounting period is stale, the whole fiscal year is refreshed.

3. Composite Range-Range Partitioning on ACCOUNTING_PERIOD and FISCAL_YEAR

I investigated making ACCOUNTING_PERIOD the top-level partitioning key, and sub-partitioning on FISCAL_YEAR.  

• Partitioning pruning works correctly, so the query performance will be unaffected, 
• PCT refresh processed all years for the single accounting period, rather than all accounting periods for the single year.  That is less work if you have fewer fiscal years than accounting periods.  Generally, this is the case.  I usually see systems that contain 3 to 6 fiscal years of data.

However, it has a number of problems.

• I can't specify storage options or compression attributes on sub-partitions in the create table DDL command, so I have to come along afterwards with PL/SQL to alter the sub-partitions.
• It is not possible to interval range sub-partition an object, so I can't automatically add partitions for future fiscal years on demand.  Instead, I am going to have to add new fiscal year subpartitions to each of the 14 range partitions.

On balance, I don't think I would choose to implement this.

Conclusion

There is no single clear recommendation for partitioning and PCT.  It will depend on the circumstances.

• If I don't need to introduce materialized views on PS_LEDGER then I would usually stick with the multi-column composite partitioning with the top-level range partition on FISCAL_YEAR and ACCOUNTING_PERIOD.
• PeopleSoft provides summary ledgers to improve the performance of the ledger queries and compressed materialized views can be built on these.
• If you only have a single actuals ledger then composite range-range partition on FISCAL_YEAR and ACCOUNTING_PERIOD is attractive.  
• I do not recommend interval partitioning on FISCAL_YEAR because this affects the scope of the refresh process.  It processes a second top-level range partition.
• If the budget data is stored in the PS_LEDGER table rather than LEDGER_BUDG then consider building separate materialized views for each value of LEDGER.  
• If you can manage to build materialized views to support rewrite of most the queries on the smaller ledgers, then the lack of sub-partitioning by LEDGER is not going to be a problem unless the query doesn't get rewritten because the materialized views are stale.  Keeping the materialized views up to date is going to be a bigger challenge.

PeopleTools 8.54: Materialized Views

This is part of a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.

Materialized Views in the Database

Snapshots were introduced in Oracle 7.  They were a way of building and maintaining a physical table to hold the results of a SQL query.  We are well used to a view being the results of a query, but here the results are materialised into a physical table.  Hence the feature was renamed materialized views in Oracle 8i.

Today materialized views are one among many replication technologies.  They are available in standard edition Oracle, and there is no licensing implication in their use.

Materialized views can generally be put into two categories

• A simple, single-table materialized view. 
• Often used to replicate data across a database link from another database.  Or to produce a subset of the data.
• Can be refreshed incrementally using a PL/SQL package supplied by Oracle
•  A materialized view log is created on the source table to record all the changes made to the source table.  It holds the primary key of the changed row, or the rowid (physical address of the row).  It can optionally hold additional columns.  It is populated by a database trigger on the source table (since Oracle 8i that trigger is hidden).
• Multi-table materialized view
• Usually done within a single database rather than across a database link.
• Can only be refreshed by being completely rebuilt. 
• If the same query as is used in a materialized view is submitted, Oracle can rewrite the query to use the materialized view instead.  Query rewrite only occurs subject to configuration and certain pre-requisites being met.

Materialized Views can be populated immediately when they are built, or later on demand.  They can be refreshed on demand, on a regular schedule by a database job, or immediately when an update to a source table is committed.  Materialized views can be put into Refresh Groups.  All the materialized views in a refresh group are refreshed in the same database transaction so the data in the materialized views is consistent.

Materialized views can be updatable and used for bidirectional replication.  I am not going to talk that here.

When you introduce materialized views into an application you need to consider what you are trying to achieve, and make design decisions accordingly.

Materialized Views in PeopleTools 8.54

Using this new feature in PeopleSoft is deceptively easy, but quite a lot is going on behind the scenes.
PeopleSoft Documentation (the term PeopleBooks seems to have been replaced by PeopleSoft Online Help): Data Management, Using Materialized Views provides an overview.

There are two new PeopleTools tables:

• PSPTMATVWDEFN - addition definition fields for the materialized view, build, refresh, staleness, stats.  Doesn't contain the query, that is in PSSQLTEXTDEFN as it is for all other views.
• PSPTMATVWDEP - lists tables upon which materialized view depends.  PeopleSoft seems to work this out for itself by parsing the SQL query.

I am going to demonstrate some aspects of the feature by running through some examples.

Example 1: Replicate part of PS_JOB across a database link

In this example I am going to use a materialized view to replicate a table from another Oracle database across a database link.

If I select SQL View the Materialized View check box appears, if I check the checkbox the Materialized View Options appear.

This is build script generated by Application Designer

DROP VIEW PS_DMK
/
CREATE VIEW PS_DMK (EMPLID, EMPL_RCD, EFFDT, EFFSEQ, SETID_DEPT,
 DEPTID) AS SELECT EMPLID , EMPL_RCD , EFFDT , EFFSEQ , SETID_DEPT ,
 DEPTID FROM PS_JOB@HROTHER
/
UPDATE PSPTMATVWDEFN SET PTMAT_MATSTAT = 0, PTMAT_REFRESHSTAT = 0,
 PTMAT_LASTREFRESH = TO_TIMESTAMP('1900-01-01-00.00.00.000000'
,'YYYY-MM-DD-HH24.MI.SS.FF'), PTMAT_STALENESS = 'STALE' WHERE RECNAME
 = 'DMK'
/
DELETE FROM MV_CAPABILITIES_TABLE WHERE MVNAME = 'PS_DMK'
/
DROP VIEW PS_DMK
/
CREATE MATERIALIZED VIEW PS_DMK (EMPLID, EMPL_RCD, EFFDT, EFFSEQ,
 SETID_DEPT, DEPTID) TABLESPACE PSMATVW BUILD DEFERRED REFRESH FAST 
ON DEMAND DISABLE QUERY REWRITE AS SELECT EMPLID , EMPL_RCD , EFFDT ,
 EFFSEQ , SETID_DEPT , DEPTID FROM PS_JOB@HROTHER
/
UPDATE PSPTMATVWDEFN SET PTMAT_MATSTAT = 1, PTMAT_REFRESHSTAT = 0,
 PTMAT_LASTREFRESH = TO_TIMESTAMP('1900-01-01-00.00.00.000000'
,'YYYY-MM-DD-HH24.MI.SS.FF'), PTMAT_STALENESS = 'STALE' WHERE RECNAME
 = 'DMK'
/

However, if the materialized view already exists, the script will drop it, recreate and drop the view, and then recreate the materialized view.

DROP MATERIALIZED VIEW PS_DMK
/
UPDATE PSPTMATVWDEFN SET PTMAT_MATSTAT = 0, PTMAT_REFRESHSTAT = 0,
 PTMAT_LASTREFRESH = TO_TIMESTAMP('1900-01-01-00.00.00.000000'
,'YYYY-MM-DD-HH24.MI.SS.FF'), PTMAT_STALENESS = 'STALE' WHERE RECNAME
 = 'DMK'
/
CREATE VIEW PS_DMK (EMPLID, EMPL_RCD, EFFDT, EFFSEQ, SETID_DEPT,
 DEPTID) AS SELECT EMPLID , EMPL_RCD , EFFDT , EFFSEQ , SETID_DEPT ,
 DEPTID FROM PS_JOB@HROTHER
/
UPDATE PSPTMATVWDEFN SET PTMAT_MATSTAT = 0, PTMAT_REFRESHSTAT = 0,
 PTMAT_LASTREFRESH = TO_TIMESTAMP('1900-01-01-00.00.00.000000'
,'YYYY-MM-DD-HH24.MI.SS.FF'), PTMAT_STALENESS = 'STALE' WHERE RECNAME
 = 'DMK'
/
DELETE FROM MV_CAPABILITIES_TABLE WHERE MVNAME = 'PS_DMK'
/
DROP VIEW PS_DMK
/
CREATE MATERIALIZED VIEW PS_DMK (EMPLID, EMPL_RCD, EFFDT, EFFSEQ,
 SETID_DEPT, DEPTID) TABLESPACE PSMATVW BUILD IMMEDIATE REFRESH FAST
 ON DEMAND DISABLE QUERY REWRITE AS SELECT EMPLID , EMPL_RCD , EFFDT ,
 EFFSEQ , SETID_DEPT , DEPTID FROM PS_JOB@HROTHER
/
UPDATE PSPTMATVWDEFN SET PTMAT_MATSTAT = 1, PTMAT_REFRESHSTAT = 1,
 (PTMAT_LASTREFRESH, PTMAT_STALENESS) = (SELECT LAST_REFRESH_DATE, 
 STALENESS FROM USER_MVIEWS WHERE MVIEW_NAME = 'PS_DMK') WHERE RECNAME
  = 'DMK'
/

• The Application Designer build script creates the materialized view using a primary key based replication.  If there is no WITH PRIMARY KEY clause specified, because it is the default.  There appears to be no way to get Application Designer to generate a WITH ROWID clause, so it is not possible to replicate a single table without a unique key.  You might question whether that is useful, but it is possible in Oracle.

• If there is no primary key on the source table, you will to add one.  If this is on another system, or a pre-8.58 PeopleSoft system you will need to do this manually.  Otherwise you will get this error message:

ERROR at line 4:
ORA-12014: table 'PS_JOB' does not contain a primary key constraint 

• If you specify any key columns on the materialized view it does not result in the usual indexes that you get on tables.  Also, it is not possible to add additional user specified indexes to the materialized view - the option is greyed out.  This is rather disappointing, because you might want to do exactly that so you can query the materialized view it in different ways to the underlying table.
• You will get a unique index on a materialized view that is replicated by primary key, because the primary key will be inherited from the underlying table.
• Nor is it possible to specify partitioning in Application Designer on a materialized view.
• You can specify storage options on the materialized view via Record DDL, but the storage options do not appear in the CREATE MATERIALIZED VIEW statement in the build script.  This is rather disappointing because you don't need to provide free space for updates in a materialized view which is completely refreshed each time, but you might if you do incremental update.

Example 2: Replicate part of PS_JOB locally

In this example, I am again only replicating 6 named columns into my materialized view.

DROP VIEW PS_DMK
/
CREATE VIEW PS_DMK (EMPLID, EMPL_RCD, EFFDT, EFFSEQ, SETID_DEPT,
 DEPTID) AS SELECT EMPLID , EMPL_RCD , EFFDT , EFFSEQ , SETID_DEPT ,
 DEPTID FROM PS_JOB
/
UPDATE PSPTMATVWDEFN SET PTMAT_MATSTAT = 0, PTMAT_REFRESHSTAT = 0,
 PTMAT_LASTREFRESH = TO_TIMESTAMP('1900-01-01-00.00.00.000000'
,'YYYY-MM-DD-HH24.MI.SS.FF'), PTMAT_STALENESS = 'STALE' WHERE RECNAME
 = 'DMK'
/
ALTER TABLE PS_JOB DROP CONSTRAINT PS_JOB_PK
/
DROP MATERIALIZED VIEW LOG ON PS_JOB
/
DELETE FROM PSPTMATVWDEP WHERE RECNAME = 'DMK' AND PTMAT_BASETBL =
 'PS_JOB'
/
ALTER TABLE PS_JOB ADD CONSTRAINT PS_JOB_PK PRIMARY KEY (EFFDT, EFFSEQ
, EMPLID, EMPL_RCD)
/
CREATE MATERIALIZED VIEW LOG ON PS_JOB TABLESPACE PSMATVW WITH PRIMARY
 KEY, ROWID, SEQUENCE(DEPTID, SETID_DEPT) INCLUDING NEW VALUES PURGE
 IMMEDIATE
/
INSERT INTO PSPTMATVWDEP(RECNAME, PTMAT_BASETBL) VALUES('DMK',
 'PS_JOB')
/
DELETE FROM MV_CAPABILITIES_TABLE WHERE MVNAME = 'PS_DMK'
/
DROP VIEW PS_DMK
/
UPDATE PSPTMATVWDEFN SET PTMAT_MATSTAT = 0, PTMAT_REFRESHSTAT = 0,
 PTMAT_LASTREFRESH = TO_TIMESTAMP('1900-01-01-00.00.00.000000'
,'YYYY-MM-DD-HH24.MI.SS.FF'), PTMAT_STALENESS = 'STALE' WHERE RECNAME
 = 'DMK'
/

I don't know why it rebuilds the non-materialized view as a normal view and drops the primary key constraint each time every time but it does.  You might not want to do this every time for a large materialized view that takes time to build.
If the materialized view log has been built, next time you generate the view build script it creates and drop the view and then builds the materialized view.

CREATE VIEW PS_DMK (EMPLID, EMPL_RCD, EFFDT, EFFSEQ, SETID_DEPT,
 DEPTID) AS SELECT EMPLID , EMPL_RCD , EFFDT , EFFSEQ , SETID_DEPT ,
 DEPTID FROM PS_JOB
/
DELETE FROM PSPTMATVWDEP WHERE RECNAME = 'DMK' AND PTMAT_BASETBL =
 'PS_JOB'
/
UPDATE PSPTMATVWDEFN SET PTMAT_MATSTAT = 0, PTMAT_REFRESHSTAT = 0,
 PTMAT_LASTREFRESH = TO_TIMESTAMP('1900-01-01-00.00.00.000000'
,'YYYY-MM-DD-HH24.MI.SS.FF'), PTMAT_STALENESS = 'STALE' WHERE RECNAME
 = 'DMK'
/
ALTER TABLE PS_JOB DROP CONSTRAINT PS_JOB_PK
/
DROP MATERIALIZED VIEW LOG ON PS_JOB
/
DELETE FROM PSPTMATVWDEP WHERE RECNAME = 'DMK' AND PTMAT_BASETBL =
 'PS_JOB'
/
ALTER TABLE PS_JOB ADD CONSTRAINT PS_JOB_PK PRIMARY KEY (EFFDT, EFFSEQ
, EMPLID, EMPL_RCD)
/
CREATE MATERIALIZED VIEW LOG ON PS_JOB TABLESPACE PSMATVW WITH PRIMARY
 KEY, ROWID, SEQUENCE(DEPTID, SETID_DEPT) INCLUDING NEW VALUES PURGE
 IMMEDIATE
/
INSERT INTO PSPTMATVWDEP(RECNAME, PTMAT_BASETBL) VALUES('DMK',
 'PS_JOB')
/
DELETE FROM MV_CAPABILITIES_TABLE WHERE MVNAME = 'PS_DMK'
/
DROP VIEW PS_DMK
/
CREATE MATERIALIZED VIEW PS_DMK (EMPLID, EMPL_RCD, EFFDT, EFFSEQ,
 SETID_DEPT, DEPTID) TABLESPACE PSMATVW BUILD DEFERRED REFRESH FAST
 ON DEMAND DISABLE QUERY REWRITE AS SELECT EMPLID , EMPL_RCD , EFFDT ,
 EFFSEQ , SETID_DEPT , DEPTID FROM PS_JOB
/
UPDATE PSPTMATVWDEFN SET PTMAT_MATSTAT = 1, PTMAT_REFRESHSTAT = 1,
 (PTMAT_LASTREFRESH, PTMAT_STALENESS) = (SELECT LAST_REFRESH_DATE, 
 STALENESS FROM USER_MVIEWS WHERE MVIEW_NAME = 'PS_DMK') WHERE RECNAME
 = 'DMK'
/

• It is rather odd to see a build script update a PeopleTools table. Application Designer also updates the PSPTMATVWDEFN table itself every time it generates the build script.  Note that the script doesn't issue an explicit commit, so if you execute the build script in SQL*Plus remember to commit to release the row level lock on PSPTMATVWDEFN.  
• Application Designer flip-flops between these two build scripts that will repeatedly drop and create the materialized view and materialized view log.  Unless you are very careful you might not know whether you have the objects in the desired state.
• The materialized view and materialized view log are always created in tablespace PSMATVW.  This is a new tablespace delivered in the standard tablespace script.  It is not possible to set the tablespace to something else as for a normal table because it is a view.  This is unfortunately because, I might not want all my materialized views in the same tablespace.
• Even though the materialized view is replicated by primary key, the materialized view log also contains the rowid and the supplementary columns.  This is overkill.  The materialized view log as built be application designer contains every length-bounded column in the source table.  This can significantly increase the overhead of the materialized view log which is maintained as other process update the source table.

SQL> desc mlog$_ps_job
 Name                          Null?    Type
 ----------------------------- -------- -------------------
 EFFDT                                  DATE
 EFFSEQ                                 NUMBER
 EMPLID                                 VARCHAR2(11 CHAR)
 EMPL_RCD                               NUMBER
 DEPTID                                 VARCHAR2(10 CHAR)
 SETID_DEPT                             VARCHAR2(5 CHAR)
 M_ROW$$                                VARCHAR2(255 CHAR)
 SEQUENCE$$                             NUMBER
 SNAPTIME$$                             DATE
 DMLTYPE$$                              VARCHAR2(1 CHAR)
 OLD_NEW$$                              VARCHAR2(1 CHAR)
 CHANGE_VECTOR$$                        RAW(255)
 XID$$                                  NUMBER

• If I just created the materialized view log as follows

CREATE MATERIALIZED VIEW LOG ON PS_JOB TABLESPACE PSMATVW 
WITH PRIMARY KEY
--, ROWID, SEQUENCE(DEPTID, SETID_DEPT) 
INCLUDING NEW VALUES PURGE IMMEDIATE
/

• then the materialized view log contains fewer columns

Name                          Null?    Type
 ----------------------------- -------- --------------------
 EFFDT                                  DATE
 EFFSEQ                                 NUMBER
 EMPLID                                 VARCHAR2(11 CHAR)
 EMPL_RCD                               NUMBER
 SNAPTIME$$                             DATE
 DMLTYPE$$                              VARCHAR2(1 CHAR)
 OLD_NEW$$                              VARCHAR2(1 CHAR)
 CHANGE_VECTOR$$                        RAW(255)
 XID$$                                  NUMBER

• The materialized view inherits the primary key from the source table because it is a single table materialized view replicated using the primary key.  Therefore there is also a unique index on this materialised view.

SELECT constraint_name, constraint_type, table_name, index_name
FROM   user_constraints
WHERE table_name = 'PS_DMK'
AND   constraint_type != 'C'
/

CONSTRAINT_NAME      C TABLE_NAME INDEX_NAME
-------------------- - ---------- ----------
PS_JOB_PK1           P PS_DMK     PS_JOB_PK1

SELECT index_name, index_type, uniqueness
FROM   user_indexes
WHERE  table_name = 'PS_DMK'
/

INDEX_NAME INDEX_TYPE UNIQUENES
---------- ---------- ---------
PS_JOB_PK1 NORMAL     UNIQUE

SELECT index_name, column_position, column_name, descend
FROM   user_ind_columns
WHERE  table_name = 'PS_DMK'
/

INDEX_NAME COLUMN_POSITION COLUMN_NAME          DESC
---------- --------------- -------------------- ----
PS_JOB_PK1               1 EFFDT                ASC
PS_JOB_PK1               2 EFFSEQ               ASC
PS_JOB_PK1               3 EMPLID               ASC
PS_JOB_PK1               4 EMPL_RCD             ASC

The build script clears out the MV_CAPABILITIES_TABLE when it drops the materialized view.  This table is used to hold the output from DBMS_MVIEW.EXPLAIN_MVIEW (see Oracle Database Data Warehousing Guide - ), which Application Designer executes when the materialized view record is saved.

Example 3:DMK_DPT_SEC_MVW is a materialised view that is cloned from security view DEPT_SEC_SRCH.

This view references various tables (I have edited out column lists and predicates for readability)

SELECT … 
  FROM PS_DEPT_TBL DEPT 
  , PSOPRDEFN OPR 
 WHERE EXISTS ( 
 SELECT 'X' 
  FROM PS_SJT_DEPT SEC 
  , PS_SJT_CLASS_ALL CLS 
  , PS_SJT_OPR_CLS SOC 
…) 
    OR EXISTS ( 
 SELECT 'X' 
  FROM PS_SJT_DEPT SEC 
  , PS_SJT_CLASS_ALL CLS 
  , PS_SJT_OPR_CLS SOC 
 …) 
    OR EXISTS ( 
 SELECT 'X' 
  FROM PS_SJT_DEPT SEC 
  , PS_SJT_CLASS_ALL CLS 
  , PS_SJT_OPR_CLS SOC 
 …)

But only 4 views appear in PSPTMATVWDEP.  PS_SJT_DEPT was not added.

SELECT * FROM psptmatvwdep WHERE recname = 'DMK_DPT_SEC_MVW'
/

RECNAME         PTMAT_BASETBL
--------------- ------------------
DMK_DPT_SEC_MVW PSOPRDEFN
DMK_DPT_SEC_MVW PS_DEPT_TBL
DMK_DPT_SEC_MVW PS_SJT_CLASS_ALL
DMK_DPT_SEC_MVW PS_SJT_OPR_CLS

I think this is because it tried and failed to add primary key constraint and materialized view log to PS_SJT_DEPT because it has a 'duplicate key' defined in Application Designer.  The following errors are found in the build log even if the build script is not executed.

ALTER TABLE PS_SJT_DEPT ADD CONSTRAINT PS_SJT_DEPT_PK PRIMARY KEY 
(SCRTY_KEY1, SCRTY_KEY2, SCRTY_KEY3, SCRTY_TYPE_CD, SETID) 
Error: DMK_DPT_SEC_MVW - SQL Error. Error Position: 39  Return: 2437 
- ORA-02437: cannot validate (SYSADM.PS_SJT_DEPT_PK) - primary key violated 
 
CREATE MATERIALIZED VIEW LOG ON PS_SJT_DEPT TABLESPACE PSMATVW 
WITH PRIMARY KEY, ROWID, SEQUENCE (DEPTID, EFFDT_NOKEY) 
INCLUDING NEW VALUES PURGE IMMEDIATE 
Error: DMK_DPT_SEC_MVW - SQL Error. Error Position: 39  Return: 2437 
- ORA-02437: cannot validate (SYSADM.PS_SJT_DEPT_PK) - primary key violated 

Application Designer worked out that PS_SJT_DEPT was referenced in the materialized view query, but it didn't check that the table does not has a unique key defined in PeopleTools.

We didn't get as far as creating the materialized view.  However, Application Designer passed the create Materialized View command to the EXPLAIN_MVIEW function in order to populate

EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW (q'[CREATE MATERIALIZED VIEW PS_DMK_DPT_SEC_MVW (SETID, OPRID, DEPTID, DESCR
, DESCRSHORT, SETID_LOCATION, LOCATION, MANAGER_ID, COMPANY, USE_BUDGETS, USE_ENCUMBRANCES) 
TABLESPACE PSMATVW BUILD DEFERRED REFRESH FAST ON DEMAND  AS 
SELECT   DEPT.SETID, OPR.OPRID,  DEPT.DEPTID , DEPT.DESCR , DEPT.DESCRSHORT , DEPT.SETID_LOCATION 
, DEPT.LOCATION , DEPT.MANAGER_ID , DEPT.COMPANY , DEPT.USE_BUDGETS , DEPT.USE_ENCUMBRANCES 
FROM PS_DEPT_TBL DEPT , PSOPRDEFN OPR 
WHERE EXISTS ( 
 SELECT 'X' FROM PS_SJT_DEPT SEC , PS_SJT_CLASS_ALL CLS , PS_SJT_OPR_CLS SOC 
 WHERE SEC.SETID = DEPT.SETID AND SEC.DEPTID = DEPT.DEPTID AND SEC.EFFDT_NOKEY = DEPT.EFFDT 
 AND CLS.SCRTY_SET_CD = 'PPLJOB' AND CLS.SCRTY_TYPE_CD = '001' AND CLS.TREE = 'Y' 
 AND CLS.SCRTY_KEY1 = SEC.SCRTY_KEY1 AND CLS.SCRTY_KEY2 = SEC.SCRTY_KEY2 
 AND CLS.SCRTY_KEY3 = SEC.SCRTY_KEY3 AND SOC.OPRID

Example 4: DMK_JOB_CUR_MVW is a materialized view cloned from JOB_CURR_ALL_VW

In this case I will try to create a materialized view on a complex query, but this time the underlying table has a unique key.  When I try to build the materialized view I get the following entries in the error log.  These warnings were obtained from the entries in MV_CAPABILITIES_TABLE which was populated by an attempt to describe the query.

SQL Build process began on 16/02/2015 at 21:05:30 for database HR92U011. 
Error: Cannot create Materialized View on record DMK_JOB_CUR_MVW. 
Warning: | PS_DMK_JOB_CUR_MVW | REFRESH_COMPLETE| Y |  | | 
Warning: | PS_DMK_JOB_CUR_MVW | REFRESH_FAST| N |  | | 
Warning: | PS_DMK_JOB_CUR_MVW | REWRITE| N |  | | 
Warning: | PS_DMK_JOB_CUR_MVW | REFRESH_FAST_AFTER_INSERT| N | aggregate function in mv | | 
Warning: | PS_DMK_JOB_CUR_MVW | REFRESH_FAST_AFTER_INSERT| N | multiple instances of the same table or view | | 
Warning: | PS_DMK_JOB_CUR_MVW | REFRESH_FAST_AFTER_ONETAB_DML| N | see the reason why REFRESH_FAST_AFTER_INSERT is disabled | | 
Warning: | PS_DMK_JOB_CUR_MVW | REFRESH_FAST_AFTER_ANY_DML| N | see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled | | 
Warning: | PS_DMK_JOB_CUR_MVW | REWRITE_FULL_TEXT_MATCH| N | Oracle error: see RELATED_NUM and RELATED_TEXT for details |expression not supported for query rewrite | 
Warning: | PS_DMK_JOB_CUR_MVW | REWRITE_FULL_TEXT_MATCH| N | Oracle error: see RELATED_NUM and RELATED_TEXT for details |expression not supported for query rewrite | 
Warning: | PS_DMK_JOB_CUR_MVW | REWRITE_FULL_TEXT_MATCH| N | query rewrite is disabled on the materialized view | | 
Warning: | PS_DMK_JOB_CUR_MVW | REWRITE_PARTIAL_TEXT_MATCH| N | materialized view cannot support any type of query rewrite | | 
Warning: | PS_DMK_JOB_CUR_MVW | REWRITE_PARTIAL_TEXT_MATCH| N | query rewrite is disabled on the materialized view | | 
Warning: | PS_DMK_JOB_CUR_MVW | REWRITE_GENERAL| N | subquery present in the WHERE clause | | 
Warning: | PS_DMK_JOB_CUR_MVW | REWRITE_GENERAL| N | materialized view cannot support any type of query rewrite | | 
Warning: | PS_DMK_JOB_CUR_MVW | REWRITE_GENERAL| N | query rewrite is disabled on the materialized view | | 

SQL Build process ended on 16/02/2015 at 21:05:30. 
1 records processed, 1 errors, 15 warnings. 
SQL Build script for all processes written to file C:\Temp\PSBUILD.SQL. 
SQL executed online. 
SQL Build log file written to C:\Temp\PSBUILD.LOG.

• So, Application Designer does try to prevent you from creating materialized views that Oracle won't manage, but the messages back are a little obscure.

• If I change the refresh mode to Complete, Application Designer does not create materialized view logs. 

CREATE MATERIALIZED VIEW PS_DMK_JOB_CUR_MVW (EMPLID, EMPL_RCD,
 ACTION_DT, BUSINESS_UNIT, EMPL_STATUS, HR_STATUS, DEPTID, JOBCODE,
 LOCATION, POSITION_NBR, ACTION, ACTION_REASON, COMP_FREQUENCY,
 COMPRATE, CURRENCY_CD, SAL_ADMIN_PLAN, GRADE, COMPANY, PAY_SYSTEM_FLG
, PAYGROUP, REG_TEMP, FULL_PART_TIME, SETID_DEPT, SETID_JOBCODE,
 SETID_LOCATION, PER_ORG) TABLESPACE PSMATVW BUILD IMMEDIATE REFRESH
 COMPLETE ON DEMAND AS SELECT A.EMPLID ,A.EMPL_RCD ,A.EFFDT 
,A.BUSINESS_UNIT ,A.EMPL_STATUS ,A.HR_STATUS ,A.DEPTID ,A.JOBCODE 
,A.LOCATION ,A.POSITION_NBR ,A.ACTION ,A.ACTION_REASON 
,A.COMP_FREQUENCY ,A.COMPRATE ,A.CURRENCY_CD ,A.SAL_ADMIN_PLAN 
,A.GRADE ,A.COMPANY ,A.PAY_SYSTEM_FLG ,A.PAYGROUP ,A.REG_TEMP 
,A.FULL_PART_TIME ,A.SETID_DEPT ,A.SETID_JOBCODE ,A.SETID_LOCATION 
,A.PER_ORG FROM PS_JOB A WHERE A.EFFDT = ( SELECT MAX (C.EFFDT) FROM
 PS_JOB C WHERE C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND
 ((C.EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')) OR
 (A.EFFDT > TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') AND
 TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') < ( SELECT
 MIN(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = A.EMPLID AND
 J2.EMPL_RCD = A.EMPL_RCD) ) )) AND A.EFFSEQ = ( SELECT MAX(D.EFFSEQ)
 FROM PS_JOB D WHERE D.EMPLID = A.EMPLID AND D.EMPL_RCD = A.EMPL_RCD
 AND D.EFFDT = A.EFFDT)
/
UPDATE PSPTMATVWDEFN SET PTMAT_MATSTAT = 1, PTMAT_REFRESHSTAT = 1,
 (PTMAT_LASTREFRESH, PTMAT_STALENESS) = (SELECT LAST_REFRESH_DATE, 
 STALENESS FROM USER_MVIEWS WHERE MVIEW_NAME = 'PS_DMK_JOB_CUR_MVW')
 WHERE RECNAME = 'DMK_JOB_CUR_MVW'
/

• Also, It doesn't create a primary key constraint on either the underlying table or the materialized view.  So this materialized view doesn't have any indexes.

Query ReWrite

One common use of complex materialized views is to allow the optimizer to rewrite the query to use the materialized view when it sees the same query as was used to create the materialized view.  Optionally the optimizer will also check that the view is up to date.  I have added the enable query rewrite clause.

DROP MATERIALIZED VIEW PS_DMK_PER_DEP_MVW
/
CREATE MATERIALIZED VIEW PS_DMK_PER_DEP_MVW (SETID_DEPT, DEPTID, EFFDT
, DESCR) TABLESPACE PSMATVW 
BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND
enable query rewrite
 AS SELECT A.SETID ,A.DEPTID ,A.EFFDT ,A.DESCR FROM PS_DEPT_TBL A
 WHERE A.EFFDT= ( SELECT MAX(B.EFFDT) FROM PS_DEPT_TBL B WHERE A.SETID
 =B.SETID AND A.DEPTID= B.DEPTID AND B.EFFDT<=TO_DATE(TO_CHAR(SYSDATE
,'YYYY-MM-DD'),'YYYY-MM-DD'))
/

However, expressions - in this case one generated to determine the current effective-dated department - are not supported for query write.

=B.SETID AND A.DEPTID= B.DEPTID AND B.EFFDT<=TO_DATE(TO_CHAR(SYSDATE
                                                              *
ERROR at line 7:
ORA-30353: expression not supported for query rewrite

This could make it very difficult to use the feature in PeopleSoft. If you want to use the materialized view you are likely to have to reference it explicitly in the code.

Refreshing materialized Views

There is a new component to manage the refresh frequency of materialized views.

PeopleTools-> Utilities-> Administration -> Oracle Materialized Views -> Materialized View Maintenance

This component will schedule an Application Engine process called PTMATREFVW.

What this Application Engine actually does is to specify the refresh frequency for the materialized view

&AlterSQL = "alter materialized view " | &mview_name 
| " REFRESH  NEXT SYSDATE + (" | &MatRecords.PTMAT_REFINT.Value | "/86400)";

So the command issued is just

alter materialized view PS_DMK REFRESH  NEXT SYSDATE + (4242/86400)";

Effectively, for each materialized view, this creates a refresh group and a database job that refreshes it.

SELECT rname, next_date, interval FROM user_refresh
/

RNAME      NEXT_DATE INTERVAL
---------- --------- -------------------------
PS_DMK     24-JAN-15 SYSDATE + (4242/86400)

SELECT name, type, rname, job, next_date, interval FROM user_refresh_children
/

NAME       TYPE       RNAME             JOB NEXT_DATE INTERVAL
---------- ---------- ---------- ---------- --------- -------------------------
PS_DMK     SNAPSHOT   PS_DMK             21 24-JAN-15 SYSDATE + (4242/86400)

SELECT job, next_date, next_Sec, interval, what FROM dba_jobs
/

       JOB NEXT_DATE NEXT_SEC INTERVAL
---------- --------- -------- -------------------------
WHAT
--------------------------------------------------
        21 24-JAN-15 11:48:52 SYSDATE + (4242/86400)
dbms_refresh.refresh('"SYSADM"."PS_DMK"');

• But I might want to group related materialized views together into a single refresh group.
• I might want to refresh the job at a particular time, which can be done with a more sophisticated function in the interval. 
• I might prefer to refresh a materialized view at a particular point in a batch schedule.  So I might prefer to code that into an application engine, or have the application engine submit a job that only fires once and does resubmit (depending on whether I want to wait for the refresh).

My Recommendations

• Good Things
• The removal of the descending indexes and the creation of the primary key is a good thing
• It is useful to be able to define the materialized view query in PeopleTools along with the rest of the applicaiton.
• The use of EXPLAIN_MVIEW to test the validity of the materialized view and to populate MV_CAPABILITIES_TABLE is clever, but the messages are obscure and should be better documented.
• Bad Things
• No checking that a source table in the local database doesn't have a unique key that will support a primary key.
• I can't build indexes on materialized view.  Although, the primary key will be inherited automatically on single table materialized views.  So you will have to handle that manually outside PeopleTools.
• There is no support for rowid based materialized views.
• The materialized view logs created by Application Designer are totally overblown - there is far too much data being logged.  They should be either primark key or rowid (if primary key is not possible), but not both.  I cannot see the point of the additional columns.  I think they are a waste of resource.
• The flip-flopping of the build script is confusing; you will never be completely sure what you have in the database. 
• The script dropping the materialized view unnecessarily, which will drop any indexes that you have created manually!

• I think some of the problems stem from trying to graft materialized views onto the existing view record type, instead of creating a new record type and building it into Application Designer properly and giving it the attributes of both a table and a view.
• There is not enough control over when a materialized view is refreshed.  Just a time interval is not good enough.  In most systems, you need better control.
• It is clearly going to be difficult getting database query rewrite to work with complex materialized views in PeopleSoft, especially if effective-date logic is required.  However, that is a reflection on the application code rather than the way support for materialized views has been implemented.

 When you design materialized views into your application you need to make careful choices about

• Refresh method
• Complete - pretty much mandatory for multi-table views, but there are some exceptions to this rule described in the Oracle database documentation.
• Fast - only for single table - rarely used within a single database - more commonly used for moving data between databases.  In which case, you need database links and the materialized view log goes onto the source database and the materialized view is created on the target.
• Refresh mode
• On commit - this is potentially dangerous because it could happen too often and there won't be a saving.  Possible exception being when the underlying table is only ever updated by a batch process
• On demand - manually issue refresh at a specific point in a batch
• On schedule by a database job.
• Build options
• Immediate - the materialized view is populated when it is created
• Deferred - the materialized view is not poplulated when it is created, and will have to be completely refreshed at some point in the future.

Choice of refresh method and mode is often a function question.  How stale can the materialized view be allowed to be, especially if you can't get query rewrite to work.  Thoughtful design is required.  I have seen systems severely degraded by the query and redo overhead of excessive refresh. 
PeopleTools support of materialized views certainly has some good things, but as it stands it is of limited use when it still leaves you with a lot of manual administration to do. 

In most systems it is the DBAs who will have to manage the materialized views.  They are generally resistant to using PeopleSoft specific tools to do database administration.  That is going to be even more challenging when only a part of the job can be done in PeopleSoft.

Truncating a Table does not affect the Materialized View Log

I am working on a site that is replicating tables between databases using Materialized Views. I have realised that if a replicated table is truncated on the source database, that the rows remain in the materialized view on the target until a complete refresh is performed. Worse, if ROWID based Materialized View is used, then the fast refresh process will error with 'ORA-12034: materialized view log on "SYSADM"."T_R" younger than last refresh'. In PeopleSoft, there are no primary keys, and it is not possible to build them if any of the key columns are nullable, which is the case with non-required date fields in PeopleSoft. Here is an example. I will create two tables, and replicate them with Materialized Views. One by primary key, and the other by ROWID.

CREATE TABLE t_pk
(a NUMBER
,b VARCHAR2(20)
,CONSTRAINT t PRIMARY KEY(a));

CREATE TABLE t_r
(a NUMBER
,b VARCHAR2(20));

CREATE MATERIALIZED VIEW LOG ON t_pk WITH PRIMARY KEY;
CREATE MATERIALIZED VIEW t_pk_mv REFRESH FAST WITH PRIMARY KEY
AS SELECT * FROM t_pk ;

CREATE MATERIALIZED VIEW LOG ON t_r WITH ROWID;
CREATE MATERIALIZED VIEW t_r_mv REFRESH FAST WITH ROWID
AS SELECT * FROM t_r ;

INSERT INTO t_pk VALUES(1,'Old');
INSERT INTO t_pk VALUES(2,'Old');
INSERT INTO t_r VALUES(1,'Old');
INSERT INTO t_r VALUES(2,'Old');

BEGIN dbms_mview.refresh(list => 'T_PK_MV', method => 'f'); END;
BEGIN dbms_mview.refresh(list => 'T_R_MV', method => 'f'); END;

SELECT * FROM t_pk_mv;
A B
- ---
1 Old
2 Old

SELECT * FROM t_r_mv;
A B
- ---
1 Old
2 Old

So the data has replicated and everything seems to working fine. Now lets truncate the source tables, and put some new data in. One row has the same key value, one does not.

TRUNCATE TABLE t_pk;
TRUNCATE TABLE t_r;

INSERT INTO t_pk VALUES(2,'New');
INSERT INTO t_pk VALUES(3,'New');
INSERT INTO t_r VALUES(2,'New');
INSERT INTO t_r VALUES(3,'New');

The fast refresh of the Materialized View with the primary key appears to work. The new rows are inserted into the Materialized View, replacing existing rows with the same key values, but the old rows remain.

BEGIN dbms_mview.refresh(list => 'T_PK_MV', method => 'f'); END;
PL/SQL procedure successfully completed.

SELECT * FROM t_pk;
A B
- ---
2 New
3 New

SELECT * FROM t_pk_mv;
A B
- ---
1 Old
2 New
3 New

The fast refresh of the Materialized View by ROWID fails, and the old data remains in place.

BEGIN dbms_mview.refresh(list => 'T_R_MV', method => 'f'); END;
*
ERROR at line 1:
ORA-12034: materialized view log on "SYSADM"."T_R" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 1

But rows 1 & 2 are in MV but not source table

SELECT * FROM t_r;
A B
- ---
2 New
3 New

SELECT * FROM t_r_mv;
A B
- ---
1 Old
2 Old

However, a full refresh corrects the discrepancy

BEGIN dbms_mview.refresh(list => 'T_PK_MV', method => 'c'); END;
BEGIN dbms_mview.refresh(list => 'T_R_MV', method => 'c'); END;

SELECT * FROM t_pk_mv;
A B
- ---
2 New
3 New

SELECT * FROM t_r_mv;
A B
- ---
2 New
3 New

It seems to me, that it would be appropriate to prevent a Truncate command executing if there is a Materialized View Log on the table. I have written such a trigger

CREATE OR REPLACE TRIGGER mvtrunc_lock
BEFORE TRUNCATE
ON SYSADM.SCHEMA
DECLARE
 e_generate_message EXCEPTION;
 l_recname  VARCHAR2(15 CHAR);
 l_msg      VARCHAR2(100 CHAR) := 'No Message.';
 l_msg2     VARCHAR2(100 CHAR) := 'Cannot '||ora_sysevent
   ||' '||lower(ora_dict_obj_type)
   ||' '||ora_dict_obj_owner||'.'||ora_dict_obj_name||'.  ';

 sql_text ora_name_list_t;
 l_sql_stmt VARCHAR2(1000 CHAR) := '';
 n          INTEGER;
 i          INTEGER;

BEGIN
 /*extract the originating SQL statement into a string variable*/
 n := ora_sql_txt(sql_text);
 FOR i IN 1..n LOOP
  l_sql_stmt := SUBSTR(l_sql_stmt || sql_text(i),1,1000);
 END LOOP;

 IF ora_dict_obj_type = 'TABLE' AND 
    ora_sysevent = 'TRUNCATE' THEN

  BEGIN /*if a materialized view log exists*/
   SELECT 'There is a materialized view log.'
   INTO   l_msg
   FROM   all_mview_logs l
   WHERE  ROWNUM = 1
   AND    l.master = ora_dict_obj_name
   AND    l.log_owner = ora_dict_obj_owner
   ;
   RAISE e_generate_message;
  EXCEPTION
   WHEN NO_DATA_FOUND THEN NULL;
  END;
 END IF;

 EXCEPTION
  WHEN NO_DATA_FOUND THEN NULL;
  WHEN e_generate_message THEN
   RAISE_APPLICATION_ERROR(-20042,
     'MVTRUNC_LOCK:'||l_msg2||l_msg||CHR(10)||'SQL:'||l_sql_stmt);
END;
/

show errors

The trigger MVTRUNC_LOCK can be downloaded from the Go-Faster website.