MVIEW

check mviews status
set lines 300
set pages 100
select owner,MVIEW_NAME,STALENESS,COMPILE_STATE,UNKNOWN_IMPORT,REFRESH_MODE, REFRESH_METHOD,LAST_REFRESH_DATE from dba_mviews order by owner,MVIEW_NAME;
mviews not imported

if mviews not imported for any user (e.g. co_apps) then grant below privilege to user and start import of mviews only

grant CREATE MATERIALIZED VIEW to co_apps;
grant create table to co_apps;
impdp directory=ICP01ONL_DUMP dumpfile=expdp_TROY_oel6-node04_23jul2020_%U.dmp logfile=DATA_PUMP_DIR:mview_MV_INVCDR_REPORT.log include=materialized_view:"IN('MV_REPORT')"

Import: Release 11.2.0.3.0 - Production on Thu Jul 23 15:59:58 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA directory=TROY_DUMP dumpfile=expdp_TROY_oel6-node04_23jul2020_%U.dmp logfile=DATA_PUMP_DIR:mview_MV_REPORT.log include=materialized_view:IN('MV_REPORT')
Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 16:00:11
do complete refresh
select 'execute dbms_mview.refresh('||''''||owner||'.'||MVIEW_NAME||''''||','||''''||'C'||''''||');' from dba_mviews ;
mview compile
select 'alter MATERIALIZED VIEW '||owner||'.'||MVIEW_NAME||' compile;' from dba_mviews ;
do fast refresh
select 'execute dbms_mview.refresh('||''''||owner||'.'||MVIEW_NAME||''''||','||''''||'F'||''''||');' from dba_mviews ;
mviews scheduler jobs status
set lines 300
set pages 1000
col JOB_NAME for a30
col OWNER for a10
col ADDITIONAL_INFO for a40
col LOG_DATE for a40
select OWNER,JOB_NAME,LOG_DATE,STATUS,ADDITIONAL_INFO,RUN_DURATION from DBA_SCHEDULER_JOB_RUN_DETAILS where JOB_NAME='MVIEW_REFRESH_JOB' order by LOG_DATE;
ORA-12034: materialized view log on younger than last refresh

run the job if prior execution were failing with this error

BEGIN
   dbms_scheduler.run_job('TROY.MVIEW_REFRESH_JOB');
END;
/
check oter jobs
set lines 300
col LAST_START_DATE for a40
col NEXT_RUN_DATE for a40
col JOB_ACTION for a40
select owner,state,JOB_ACTION,LAST_START_DATE, NEXT_RUN_DATE from DBA_SCHEDULER_JOBS WHERE JOB_NAME ='MVIEW_REFRESH_JOB';
mviews details
SET LONG 9999999 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','BS_TAB_MV','SYS') from dual;
mviews on prebuilt error
ORA-39083: Object type MATERIALIZED_VIEW failed to create with error:
ORA-32334: cannot create prebuilt materialized view on a table already referenced by a MV

find the child mviews and drop it then create parent mviews and then child mview

set lines 300
select  name, owner, master_owner, master 
from dba_snapshots where 
master ='NC_OMD_M_II_AV';

NAME                  OWNER                   MASTER_OWNER                   MASTER
--------------------- ----------------------- --------------------- ------------------------------
NC_OMD_MT_SP_AV       TROY_TRN_6800_KPI       TROY_TRN_6800_KPI      NC_OMD_M_II_AV

drop the child mview

alter session set current_schema=TROY_TRN_6800_KPI;
drop MATERIALIZED VIEW NC_OMD_MT_SP_AV;

import first the master mview and then child

impdp directory=PUMP_DIR dumpfile=expdp_TROY_oel6-node04_22jul2020_%U.dmp logfile=LOG_DIR:impdp_TROY_mview_NC_OMD_M_II_AV.log include=materialized_view:"IN('NC_OMD_M_II_AV')"
impdp directory=PUMP_DIR dumpfile=expdp_TROY_oel6-node04_22jul2020_%U.dmp logfile=LOG_DIR:impdp_TROY_mview_NC_OMD_M_II_AV.log include=materialized_view:"IN('NC_OMD_MT_SP_AV')"
import the refresh group
impdp directory=PUMP_DIR dumpfile=expdp_NCT01ORT_lonodc0702_22jul2020_%U.dmp logfile=LOG_DIR:refresh_group.log include=REFRESH_GROUP sqlfile=LOG_DIR:refresh_group.sql

open the sqlfile

cat refresh_group.sql
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: DATABASE_EXPORT/SCHEMA/REFRESH_GROUP
-- CONNECT COLTNC_TRN_6800
 BEGIN
dbms_refresh.make('"COLTNC_TRN_6800"."NRM_RG"',list=>null,next_date=>null,interval=>null,implicit_destroy=>FALSE,lax=>FALSE,job=>477,rollback_seg=>NULL,push_deferred_rpc=>FALSE,refresh_after_errors=>FALSE,purge_option => 0,parallelism => 0,heap_size => 0);
 END;
/

modify to below and remove remove job=>477

 

 cat refresh_group.sql
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: DATABASE_EXPORT/SCHEMA/REFRESH_GROUP
-- CONNECT COLTNC_TRN_6800
conn COLTNC_TRN_6800/COLTNC_TRN_6800
 BEGIN
dbms_refresh.make('"COLTNC_TRN_6800"."NRM_RG"',list=>null,next_date=>null,interval=>null,implicit_destroy=>FALSE,lax=>FALSE,rollback_seg=>NULL,push_deferred_rpc=>FALSE,refresh_after_errors=>FALSE,purge_option => 0,parallelism => 0,heap_size => 0);
 END;
/

now connect as refreshgroup owner and run the script 

conn TROY_TRN_6800/TROY_TRN_6800
SQL> @refresh_group_mod.sql

Session altered.


Session altered.


Session altered.


Session altered.


Session altered.


Session altered.

Connected.

PL/SQL procedure successfully completed.

check status

SQL> select ROWNER,RNAME,JOB from dba_refresh;

ROWNER                         RNAME                                 JOB
------------------------------ ------------------------------ ----------
TROY_TRN_6800                NRM_RG                                  2