Encountered error ora 12008

Ora-12008 And Ora-01732 When Doing Complete Refresh On Materialized View (Doc ID 732614.1)

Last updated on FEBRUARY 01, 2022

Applies to:

Oracle Database — Enterprise Edition — Version 10.2.0.2 and later
Oracle Database Cloud Schema Service — Version N/A and later
Oracle Database Exadata Cloud Machine — Version N/A and later
Oracle Cloud Infrastructure — Database Service — Version N/A and later
Oracle Database Backup Service — Version N/A and later
Information in this document applies to any platform.

Symptoms

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.В

When try to refresh the materialized view with complete method, the following error is displayed:В

SQL> EXEC DBMS_MVIEW.REFRESH(‘ ‘);
BEGIN DBMS_MVIEW.REFRESH(‘ ‘); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01732: data manipulation operation not legal on this view
ORA-06512: at «SYS.DBMS_SNAPSHOT», line 2255
ORA-06512: at «SYS.DBMS_SNAPSHOT», line 2461
ORA-06512: at «SYS.DBMS_SNAPSHOT», line 2430
ORA-06512: at line 1

Changes

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

ORA-12008 and ORA-00942 Error Reported During Materialized View Refresh Across Schema (Doc ID 2559390.1)

Last updated on FEBRUARY 28, 2021

Applies to:

Symptoms

When usera has a base table, userb has a materialized view based on usera’s table.
userc executed userb’s materialized view, and failed with ORA-12008 and ORA-00942.

The test case is as following:

Event when using sysdba to execute userb’s materialized view, still failed.

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

ORA-12008: Error In Materialized View Refresh Path ORA-00932: Inconsistent Datatypes (Doc ID 2549941.1)

Last updated on MARCH 02, 2021

Applies to:

Symptoms

ORA-12008: error in materialized view refresh path
ORA-00932: inconsistent datatypes: expected — got CLOB
ORA-06512: at «APPS.MRP_CL_REFRESH_SNAPSHOT», line 3077
ORA-06512: at line 1

Same error repeated for different snapshots

1ST RUN
l14781110.req — 12-MAY 18:27:11 : Error refreshing individual snapshot : APPS.WIP_WOPR_RESS_SN
l14781108.req — 12-MAY 18:27:11 : Error refreshing individual snapshot : APPS.WIP_WOPRS_SN.
2ND RUN
l14805237.req — 12-MAY 20:30:11 : Error refreshing individual snapshot : APPS.WIP_WOPR_RESS_SN
l14805235.req — 12-MAY 20:30:11 : Error refreshing individual snapshot : APPS.WIP_WOPRS_SN

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

Refresh Snapshot Completes Error — ORA-12008 OR ORA-12048 & KEY ERROR — ORA-01031 (Doc ID 1924473.1)

Last updated on AUGUST 23, 2022

Applies to:

Oracle Advanced Supply Chain Planning — Version 11.5.10 and later
Information in this document applies to any platform.
MSRFWOR
MSCCLRFS

Symptoms

In 11.5.10, 12.1.x and 12.2.x running Refresh Snapshots

Which is 12.1 and 12.2 can spawn child program MSCCLRFS module: APS Collections Refresh Snapshot Thread,
the following error occurs for varying individual collections snapshots in either MSRFWOR module: Refresh Collection Snapshots OR MSCCLRFS module: APS Collections Refresh Snapshot Thread,

Error : ORA-12008: error in materialized view refresh path
OR
ORA-12048: error encountered while refreshing materialized view [SNAPSHOT NAME]

THEN SEE KEY ERROR IN LOG
ORA-01031: insufficient privileges

ORA-12048: error encountered while refreshing materialized view «APPS».»OE_ODR_LINES_SN»
ORA-01031: insufficient privileges

Error : ORA-12008: error in materialized view refresh path
ORA-01031: insufficient privileges Error refreshing individual snapshot : APPS.BOM_OPR_RESS_SN

— Steps To Reproduce:

Customer states the issue can be reproduced at will at customer site with the following steps:
1. (R) Advanced Supply Chain Planner
2. (N) Collections / Oracle Systems / Standard Collection OR Launch ATP Data Collections from Order Management
3. Observe the noted error for one or more of the snapshots

This is most likely to happen on snapshots with high volumes, but the complete list is

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

ORA-12008: error in materialized view refresh path

I am a newbie DBA and while doing daily health check found the below errors.

ORA-12012: error on auto execute of job 576872

ORA-12008: error in materialized view refresh path

ORA-00980: synonym translation is no longer valid

ORA-06512: at «SYS.DBMS_SNAPSHOT», line 2256

ORA-06512: at «SYS.DBMS_SNAPSHOT», line 2462

ORA-06512: at «SYS.DBMS_IREFRESH», line 685

ORA-06512: at «SYS.DBMS_REFRESH», line 195

ORA-06512: at line 1

now I can only understand that error occurred in some materialized view refresh job.

Any help to dig down the issue and with some solution for the same?

Answers

I think you have a problem with a synonym:

ORA-00980: synonym translation is no longer valid Cause: A synonym did not translate to a legal target object. This could happen for one of the following reasons:

1. The target schema does not exist.

2. The target object does not exist.

3. The synonym specifies an incorrect database link.

4. The synonym is not versioned but specifies a versioned target object.

Action: Change the synonym definition so that the synonym points at a legal target object.

thanks a bunch . Any help in finding in which job and in which mview it is located and has failed?

DB version: Oracle 10.2.0.5

OS:SUn solaris 5.10

You could check the alert log for starters (but it looks like you did already).

Also, since you have the job identifier, you could query DBA_JOBS or ALL_JOBS

here’s the query from DBA_JOBS

SQL> select JOB,BROKEN,FAILURES,SCHEMA_USER,LAST_DATE,LAST_SEC,INTERVAL from DBA_JOBS where JOB=576872;

JOB BROKEN FAILURES SCHEMA_USER LAST_DATE LAST_SEC INTERVAL

576872 N 14 APOLLO 02-JUL-14 18:20:14 SYSDATE + 1

I think i am asking too much, but it would be great if you could also help me out finding the failed materialized view refresh name. please

I found the owner and the related mview also.

OWNER MVIEW_NAME LAST_REFRESH

APOLLO MV_APOLLO_RES 08-JUL-14

APOLLO MV_BSE3 08-JUL-14

APOLLO MV_BSE2 08-JUL-14

APOLLO MV_MIN 08-JUL-14

APOLLO MV_MINAPN 08-JUL-14

APOLLO MV_BSE 08-JUL-14

APOLLO MV_NODIR 08-JUL-14

want to know what caused the problem and want to dig down to the root cause now.

Could be something got dropped, could be something changed in a manner different than you expected. I’ve seen the latter in obscure circumstances like a procedure is in use, so it is not flushed from the library cache when someone tries to recompile it (or even flush the cache), and some other procedure is recompiled and expects it to have changed. Or something like that, I get these things backwards sometimes. The key to understanding a particular situation is to understand everything that can invalidate a synonym.

>want to know what caused the problem

somebody changed something.

software does not spontaneously break.

BTW, all changed data can be found using DBMS_LOGMNR

or you could ask who did DDL that broke this job.

Источник

This was just one of those days, when strange things happen. Consider this:

You have a materialized view with left outer join:

CREATE MATERIALIZED VIEW table_mw
      REFRESH FAST ON COMMIT
      WITH ROWID
      AS
SELECT
      v.ROWID v_rid,
      t.ROWID t_rid,
      s.ROWID s_rid,
      v.*,
      s.dfr,
      s.dfr
FROM
      table_v v,
      table_t t,
      table_s s
WHERE v.ref_id = t.id (+)
      AND v.ref_id = s.id (+)
/

CREATE MATERIALIZED VIEW LOG ON table_v WITH ROWID
/

CREATE MATERIALIZED VIEW LOG ON table_t WITH ROWID
/

CREATE MATERIALIZED VIEW LOG ON table_s WITH ROWID
/

With that solved, we did a simple UPDATE of one row in table TABLE_V. Everything looked fine, until we wanted to commit. Commit returned following error stack:

ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist

Cause:  Table SNAP$_<mview_name> reads rows from the view MVIEW$_<mview_name>, which is a view on the master table (the master may be at a remote site). Any error in this path will cause this error at refresh time. For fast refreshes, the table <master_owner>.MLOG$_<master> is also referenced.

Action: Examine the other messages on the stack to find the problem. See if the objects SNAP$_<mview_name>, MVIEW$_<mview_name>, <mowner>.<master>@<dblink>, <mowner>.MLOG$_<master>@<dblink> still exist.

I checked alert log and trace files to find nothing of use there. After some experimentation I found out that for some reason, Oracle cannot cope with timestamp-based materialized view logs. Implementation of commit SCN-based materialized view logs solved the problem.

So in the end materialized view logs looked like this:

CREATE MATERIALIZED VIEW LOG ON table_v WITH ROWID, COMMIT SCN
/

CREATE MATERIALIZED VIEW LOG ON table_t WITH ROWID, COMMIT SCN
/

CREATE MATERIALIZED VIEW LOG ON table_s WITH ROWID, COMMIT SCN
/

Seems like one of those problems you solve, but don’t know why they came in first place.

PS: There is also one important think you should not forget or you’ll get same generic error on COMMIT of one of the source tables. The thing is that if you materialized view is in different schema, don’t forget to also GRANT SELECT on materialized view logs (MLOG$, RUPD$).

During the synch-phase of an online redefintion, I received the error:

begin
ORA-42009: error occurred while synchronizing the redefinition
ORA-12008: error in materialized view or zonemap refresh path
ORA-01843: not a valid month
ORA-06512: at "SYS.DBMS_REDEFINITION", line 219
ORA-06512: at "SYS.DBMS_REDEFINITION", line 5392

Solution:
Alter your session setting for NLS_TERRITORY. Add the line

alter session set nls_territory='';

to your script.

This seems to be due to the fact that I am partitioning on a virtual column, using a column that is in turn of datatype TIMESTAMP:

CREATE TABLE USER1.TAB1_INTERIM
(
ENTRY_ID             VARCHAR2(36 BYTE)        NULL,
DNAME                VARCHAR2(11 BYTE)        NULL,
DTYPE                VARCHAR2(64 BYTE)        NULL,
CREATED_TIME         TIMESTAMP(6)             NULL,
DYEAR                INTEGER                  NULL,
CREATED_DAY AS (
CAST(
TO_CHAR(CREATED_TIME,'DD.MM.YYYY')
AS DATE
)
) VIRTUAL
)
TABLESPACE MYTABLESPACE
PARTITION BY RANGE (CREATED_DAY)
INTERVAL( NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION P_INIT VALUES LESS THAN (TO_DATE('01.01.2017', 'DD.MM.YYYY'))
TABLESPACE MYTABLESPACE
);

Since the creator of the database didn’t set the NLS_TERRITORY to NORWAY, it is using the default value «AMERICA».

Without any altering of the default NLS setting, the CREATED_TIME column will therefore contain data formatted according to US standard:

select created_time from user1.tab1 fetch first 1 rows only;

CREATED_TIME
--------------------------
26-SEP-17 09.30.17.040000 AM

I would like the data to be formatted according to Norwegian locale rules, so I use ‘DD.MM.YYYY’ in my virtual column. So I tested in sqlplus:

alter session set nls_territory='NORWAY';
select created_time from user1.tab1 fetch first 1 rows only;

CREATED_TIME
--------------------------
26.09.2017 09.30.17,040000

So my script would look like this:

alter session set nls_territory='NORWAY';
begin
   DBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname=>'USER1',orig_table=>'TAB1',int_table=>'TAB1_INTERIM');
end;
/

and it completed successfully.

Troubleshooting Materialized View (MV) Problems in PeopleSoft Reporting.

Step1 — Notification: Email will notify PeopleSoft DBA
team on Materialized View issue:

Sample
Email to PeopleSoft DBA’s:

Materialized
Views Refreshes Out of Date as of Thu Sep 10 03:22:03 EDT 2009 on psrptprd

SQL>
select Name, owner, refresh_mode,

 
     2  to_char(last_refresh, ‘dd-MON-yyyy hh:mi PM’)
last_refresh,

 
     3  to_char(Start_with,’dd-MON-yyyy hh:mi PM’)
next_refresh

 
     4  from all_snapshots

 
     5  where start_with < sysdate — 2/24;

NAME                           OWNER           REFRESH_MODE    LAST_REFRESH               NEXT_REFRESH                                                                                     
            

—————————— —————
————— ————————— —————————                                                                                   

MV_PS_PROJ_RESOURCE_PART       MVIEW_FIN       PERIODIC        09-SEP-2009 11:58 PM       10-SEP-2009 12:58 AM

Step2 – Determining
status of MV: Check status of job to see if it
is broken or failed. If failed column below is > 0 then go to step 3
otherwise if failed column is = 0 and job is not broken go to step 4.

       Example:
Determining status of MV_PS_PROJ_RESOURCE_PART

SELECT

SUBSTR(job,1,6) «Job»,

SUBSTR(log_user,1,5) «User»,

SUBSTR(schema_user,1,5) «Schema»,

SUBSTR(To_CHAR(last_date, ‘DD.MM.YYY
HH24:MI’),1,16) «Last Date»,

SUBSTR(To_CHAR(next_date, ‘DD.MM.YYY
HH24:MI’),1,16) «Next Date»,

SUBSTR(broken,1,2) «B»,

SUBSTR(failures,1,8) «Failed»,

SUBSTR(what,1,100) «Refresh»

FROM DBA_JOBS

Where what like ‘%PS_PROJ_RESOURCE%’;

If job is not broken (B=N)
or has not failed (Failed=0).  No further action required by DBA except to
check database performance and health — job is still running, go to step 4.

Job    User            Schema          Last Date       Next Date       B

—— ————— ————— —————
————— —

Failed

———

Refresh

—————————————————————————-

3250   MVIEW           MVIEW           10.09.009 05:57 10.09.009 06:57 N

0

dbms_refresh.refresh(‘»MVIEW_FIN».»MV_PS_PROJ_RESOURCE_PART»‘);

 If job is broken (B=Y) or has failed (Failed>0).  Check database alert log to
investigate underlying cause. An attempt to refresh the MV may provide an
Oracle error. Go to step 3.

Job    User            Schema          Last Date       Next Date       B

—— ————— ————— —————
————— —

Failed

———

Refresh

—————————————————————————-

3250   MVIEW           MVIEW           10.09.009 05:57 10.09.009 06:57 Y

1

dbms_refresh.refresh(‘»MVIEW_FIN».»MV_PS_PROJ_RESOURCE_PART»‘);

Step3 – MV failed: If job fails it will try multiple times before setting the job status
to broken (N).  Depending on the state of
the job error when you first encounter it you may be require to break the job
manually if it hasn’t been broken already and kill related session.  Ensure that you allow sessions to rollback
completely otherwise a full refresh may hang.

Example
1: Check on job status for MV_PS_PROJ_RESOURCE_PART.

SQL> SELECT a.job, a.sid, what,

TO_CHAR(SYSDATE, ‘mm/dd/yyyy hh24:mi:ss’) now,

TO_CHAR(a.this_date, ‘mm/dd/yyyy hh24:mi:ss’)
this

FROM  
dba_jobs_running a, dba_jobs b

WHERE 
a.job = b.job and a.job=3250; 

no rows selected 
(no sessions to kill).

Example 2: Active session — kill session and monitor
rollback to completion.

SELECT a.sid, a.serial#, a.username, b.xidusn,
b.used_urec, b.used_ublk

FROM v$session a, v$transaction b

WHERE a.saddr = b.ses_addr and a.sid=71;

SQL> SELECT a.sid, a.serial#, a.username,
b.xidusn, b.used_urec, b.used_ublk

  2  FROM v$session a, v$transaction b

  3  WHERE a.saddr = b.ses_addr and a.sid=71;

      
SID    SERIAL# USERNAME                           XIDUSN  USED_UREC

———- ———- ——————————
———- ———-

 USED_UBLK

———-

       
71       6720 MVIEW_FIN                             119  159383334

   4699036

The result of the about query should return “no
rows selected” for a success rollback of the transaction for the session killed. 

Next test whether a fast refresh is possible.  Test fast refresh of MV_PS_PROJ_RESOURCE_PART
to see if you encounter invalid ROWID error. 
If you encounter «ORA-12008:
error in materialized view refresh path» then a full refresh is required.

Example:
Test for fast fresh of MV_PS_PROJ_RESOURCE_PART.

SQL> conn mview_fin/xxxxxxx@psrptprd

Connected.

SQL> EXECUTE
DBMS_MVIEW.REFRESH(‘MV_PS_PROJ_RESOURCE_PART’,’F’);

BEGIN DBMS_MVIEW.REFRESH(‘MV_PS_PROJ_RESOURCE_PART’,’F’);
END;

*

ERROR at line 1:

ORA-12008: error in materialized view refresh
path

ORA-01410: invalid ROWID

ORA-02063: preceding line from FINANCE

ORA-06512: at «SYS.DBMS_SNAPSHOT»,
line 2545

ORA-06512: at «SYS.DBMS_SNAPSHOT», line
2751

ORA-06512: at «SYS.DBMS_SNAPSHOT»,
line 2720

ORA-06512: at line 1

Finally check alert log for ora-600 errors that indicate a shared memory
errors and flush the shared pool three times to clear this.  Run full refresh and notify Greg Noble; Tripurari
Sinha; Gareth Hale; Manish Verma.  Full
Refresh usually takes 5 and 1/2 hours for MV_PS_PROJ_RESOURCE_PART.

Example:

SQL> conn mview_fin/xxxxxxx@psrptprd

Connected.

SQL> EXECUTE
DBMS_MVIEW.REFRESH(‘MV_PS_PROJ_RESOURCE_PART’,’COMPLETE’,atomic_refresh=>FALSE);

Ensure that statistics is gathered for the
complete mview refresh.

Example:

exec dbms_stats.gather_table_stats(‘MVIEW_FIN’,
‘MV_PS_PROJ_RESOURCE_PART’, CASCADE=>TRUE);

Step 4 – No major
action required, MV is running and has not failed or broken: Check status of MV job again to ensure job has not broken and is
stilling running.  Also, check the general
health of master  and reporting databases
(by checking the alert log, MV log and undo tablepaces and database performance
(focusing on the shared_pool) to ensure that there are no general issues with
the databases such lack of database memory or lack of space on key tablespaces
or issues with the MV log. 

Example: MV_PS_PROJ_RESOURCE_PART job has not
failed or broken.

SELECT

SUBSTR(job,1,6) «Job»,

SUBSTR(log_user,1,5) «User»,

SUBSTR(schema_user,1,5) «Schema»,

SUBSTR(To_CHAR(last_date, ‘DD.MM.YYY
HH24:MI’),1,16) «Last Date»,

SUBSTR(To_CHAR(next_date, ‘DD.MM.YYY
HH24:MI’),1,16) «Next Date»,

SUBSTR(broken,1,2) «B»,

SUBSTR(failures,1,8) «Failed»,

SUBSTR(what,1,100) «Refresh»

FROM DBA_JOBS

Where what like ‘%PS_PROJ_RESOURCE%’;

Job   
User            Schema          Last Date       Next Date       B

—— ————— —————
————— ————— —

Failed

———

Refresh

———————————————————————————

3250  
MVIEW           MVIEW           25.09.009 02:24 25.09.009 03:24 N

0

dbms_refresh.refresh(‘»MVIEW_FIN».»MV_PS_PROJ_RESOURCE_PART»‘);

Example: MV_PS_PROJ_RESOURCE_PART job is still
running.

oracle >SQL> SELECT a.job, a.sid, what,

TO_CHAR(SYSDATE, ‘mm/dd/yyyy hh24:mi:ss’) now,

TO_CHAR(a.this_date, ‘mm/dd/yyyy hh24:mi:ss’)
this

FROM  
dba_jobs_running a, dba_jobs b

WHERE 
a.job = b.job and a.job=3250;

      
JOB        SID

———- ———-

WHAT

———————————————————————————

NOW                 THIS

——————- ——————-

     
3250          123

dbms_refresh.refresh(‘»MVIEW_FIN».»MV_PS_CA_ACCTG_LN_PC_PART»‘);

09/25/2009 06:35:52 09/25/2009 03:24:17

Appendix

For
a complete refresh (using dbms_mview.refresh) sys.snap$ and sys.mlog$ are
updated to reflect the time of the refresh. The materialized base view is
deleted and all rows selected from the master table are inserted into the
snapshot base table and then sys.slog$ is updated to reflect the time of the
refresh.

For
a fast refresh, the steps are sys.snap$ and sys.mlog$ are updated to reflect
the time of the refresh.  Rows in the
materialized base view are deleted and all rows selected from the master table
are inserted into the snapshot base table and then sys.slog$ is updated to
reflect the time of the refresh.  Rows
that are not needed anymore for a refresh by any materialized view are deleted
from the materialized view log (e.g. SYSFS.MLOG$_PS_PROJ_RESOURCE).

A
long running refresh will have a PC_REPORTS_USER session in the F90xxx or
H90xxx databases and a long running SQL operation for that session normally
gives an indication of how long the refresh will take to complete.  For a full refresh there may be an automatic
fast refresh after the full refresh that gets the materialized view up to the
current time.

See below for examples of fast and complete
refresh for MV_PS_PROJ_RESOURCE_PART. 
Consider using atomic_refresh=FALSE option for complete refresh – the
default for Oracle 11g is atomic_refresh=TRUE (DELETE instead of a TRUNCATE).

Example:

EXECUTE
DBMS_MVIEW.REFRESH(‘MV_PS_PROJ_RESOURCE_PART’,’F’); — fast refresh

EXECUTE
DBMS_MVIEW.REFRESH(‘MV_PS_PROJ_RESOURCE_PART’,’COMPLETE’,atomic_refresh=>FALSE);
— complete refresh by truncating MV.

Use
DBMS_JOB and ALTER MATERIALIZED VIEW statements to manage and schedule MV jobs.

Example: MV_PS_PROJ_RESOURCE_PART is
scheduled to run every hour.

exec dbms_job.broken(3250, FALSE);

ALTER MATERIALIZED VIEW
MVIEW_FIN.MV_PS_PROJ_RESOURCE_PART

REFRESH 

NEXT sysdate + 1/24;

Ensure that
statistics is gathered for complete refresh.

Example:

exec dbms_stats.gather_table_stats(‘MVIEW_FIN’,
‘MV_PS_PROJ_RESOURCE_PART’, CASCADE=>TRUE);

Thanks Thomas. 

How to Create a New Materialized View.

In
F8XPRD

create
materialized view log on sysfs.PS_XXXXX tablespace

with
rowid, sequence excluding new values;

grant
select on sysfs.PS_XXXXX to 
pc_reports_user;

grant
select on sysfs.MLOG$_PS_XXXXX to pc_reports_user;

in
PSRPTPRD as Mview_F8X

DROP
MATERIALIZED VIEW MVIEW_F8X.MV_PS_XXXXX;

CREATE
MATERIALIZED VIEW MVIEW_F8X.MV_PS_XXXXX

TABLESPACE
MVIEW_F8X

NOCACHE

NOLOGGING

COMPRESS

NOPARALLEL

BUILD
IMMEDIATE

USING
INDEX

REFRESH
FAST

START
WITH sysdate

NEXT
SYSDATE + 1/24

WITH
ROWID

AS

SELECT
* FROM «SYSFS».»PS_XXXX»@F8X «PS_XXXXX»;

CREATE
INDEX MView_F8X.MV_PS_XXXXX_IX1 ON MView_F8X.MV_PS_XXXXX

(COL1,
COL2, COL3)

LOGGING

TABLESPACE
PSINDEX

NOPARALLEL;

ALTER
TABLE MVIEW_F8X.MV_PS_XXXXX ADD (

  CONSTRAINT PS_XXXXX_PK

 UNIQUE (COL1, COL2, COL3)

    DEFERRABLE INITIALLY IMMEDIATE);

GRANT
SELECT ON MVIEW_F8X.MV_PS_XXXXX TO SYSFS;

DROP
PUBLIC SYNONYM PS_XXXXX;

CREATE
PUBLIC SYNONYM PS_XXXXX FOR MVIEW_F8X.MV_PS_XXXXX;

Materialized View Monitoring

The
check is simply to ensure that the Materialized view has been refreshed within
2 hours of its scheduled time by checking all_snapshots.

select Name, owner, refresh_mode,

to_char(last_refresh, ‘dd-MON-yyyy hh:mi PM’) last_refresh,

to_char(Start_with,’dd-MON-yyyy hh:mi PM’) next_refresh

from all_snapshots

where start_with < sysdate — 2/24;