Materialized Views Overview
The data in a materialized view is updated either by complete or by incremental refresh.
Log Table: To support Incremental Refresh, Oracle maintains a log table to keep track of changes on the master table.
Materialized View Related Objects
ALL_MVIEWS
USER_MVIEWS, ALL_MVIEWS, DBA_MVIEWS
Data exists on The Remote Site.
It got data about all the Materialized Views in the schema, when they were last refreshed, refresh type, SQL that was used to populate the MV, and more.
USER_MVIEWS or USER_SNAPSHOTS
SELECT mview_name,
query,
master_link,
refresh_mode,
refresh_method,
last_refresh_type,
last_refresh_date
FROM USER_MVIEWS;
is same as:
SELECT name,
query,
master_link,
refresh_method,
type,
last_refresh
FROM USER_SNAPSHOTS;
REFRESH_MODE - Can be DEMAND,COMMIT,NEVER
REFRESH_METHOD - Can be C-COMPLETE, F-FAST, N-NEVER, ?-FORCE
REFRESH_MODE
The Frequency of Refresh can be configured to run on-demand or at regular time intervals.
Complete Refresh: The materialized view is build from scratch
Incremental (or Fast Refresh): Only the changes, or delta, from master table are copied to Materialized View.
If a fast refresh is attempted for such a materialized view after its master materialized view has performed a complete refresh, then Oracle returns the following error:
===================
Error ORA-12034 mview log is younger than last refresh
===================
ORA-12034 mview log is younger than last refresh
ORA-12034 mview log is younger than last refresh
ORA-12048: error encountered while refreshing materialized view "IG1_GATE"."ALARMER_SS"
ORA-12034: materialized view log on "VIP700"."ALARMER" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025
ORA-06512: at "SYS.DBMS_IREFRESH", line 689
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at "IG1_GATE.SH_REFRESH_PKG", line 19
ORA-06512: at line 3
The solution:
The error is encountered in Fast refresh - because incremental refreshed cannot be done
1st - Do Complete Refresh
2nd - Do Fast Refresh
BEGIN
DBMS_SNAPSHOT.refresh ('IG1_GATE.ALARMER_SS', 'C');
DBMS_SNAPSHOT.refresh ('IG1_GATE.ALARMER_SS', 'F');
END;
/
USER_MVIEW_LOGS
Lists the Name of the table where the changes to the master table or master materialized view are logged.
SELECT log_owner, master, log_table
FROM DBA_MVIEW_LOGS;
ALL_MVIEW_REFRESH_TIMES
- Lists the last refresh time per Materialized View.
MATERIALIZED VIEW LOG
These are the MLOG$_<TABLE> tables listed in USER_MVIEW_LOGS and in USER_SNAPSHOT_LOGS.
They should be created explicitly.
CREATE MATERIALIZED VIEW LOG ON MASTER_OWNER.MASTER_TABLE
DBMS_REFRESH Package
See Oracle Documentation: DBMS_REFRESH
DBMS_REFRESH enables you to create groups of materialized views that can be refreshed together as a single transaction.
The methods for DBMS_REFRESH:
MAKE(), CHANGE(), DESTROY() - Create, Modify and Delete the Refresh Group.
ADD(), SUBTRACT() - Add and Remove materialized views Refresh Group
DBA_RGROUP
See Oracle Documentation: DBA_RGROUP
A View that lists all the Refresh Groups.
Each entry in DBA_RGROUP has an entry in DBA_JOBS, linked by DBA_RGROUP.job.
SELECT *
FROM DBA_JOBS
WHERE job IN (SELECT job FROM DBA_RGROUP)
DBMS_SNAPSHOT Package
See Oracle Documentation: DBMS_SNAPSHOT
DBMS_MVIEW is a synonym for DBMS_SNAPSHOT
Package that handles refresh snapshots that are not part of the same refresh group and purge logs.
DBMS_MVIEW Package
DBMS_MVIEW is a synonym for DBMS_SNAPSHOT
It got several useful procedures:
REFRESH - Refreshes materialized views that are not members of the same refresh group
PURGE_LOG - Purges rows from the materialized view log.
ESTIMATE_MVIEW_SIZE - This procedure estimates the size of a materialized view that you might create
EXPLAIN_MVIEW - That would give some info about the existing Materialized View.
and more...
DBA_REGISTERED_SNAPSHOTS
This table lists Remote snapshots of local tables.
This table exists in the Master DB.
SELECT owner, name, snapshot_id
FROM DBA_REGISTERED_SNAPSHOTS
WHERE owner like 'REMOTE_DB%'
ORDER BY snapshot_id
OWNER NAME SNAPSHOT_ID
------------- ------------- ------------
REMOTE_DB_A TABLE_A_SS 43661
REMOTE_DB_A TABLE_B_SS 43662
REMOTE_DB_A TABLE_C_SS 43663
REMOTE_DB_B TABLE_A_SS 43664
REMOTE_DB_B TABLE_B_SS 43665
If There are N Remote sites - There should be N Entries in per each table, in DBA_REGISTERED_SNAPSHOTS.
Join with USER_SNAPSHOT_LOGS, to see last refresh date.
SELECT DBA_REGISTERED_SNAPSHOTS.owner,
DBA_REGISTERED_SNAPSHOTS.name,
DBA_REGISTERED_SNAPSHOTS.snapshot_id,
USER_SNAPSHOT_LOGS.current_snapshots
FROM USER_SNAPSHOT_LOGS,
DBA_REGISTERED_SNAPSHOTS
WHERE DBA_REGISTERED_SNAPSHOTS.snapshot_id =
USER_SNAPSHOT_LOGS.snapshot_id
AND DBA_REGISTERED_SNAPSHOTS.owner = 'REMOTE_DB_A'
What if there are several Remote sites with same Materialized View looking at the same Master Table, and all are doing Fast Refresh?
With Fast Refresh, rows are deleted from Log Table once the data was refreshed.
Multiple simple snapshots can use the same snapshot log, in that case already used to refresh one snapshot would still be needed to refresh another snapshot.
Oracle does not delete rows from the log until all snapshots have used them.
MLOG$ Tables
Applicable only to Refresh Fast option.
The MLOG% Tables exist only in the Master DB.
SELECT *
FROM USER_TABLES
WHERE TABLE_NAME LIKE 'MLOG%'
Per each table that participates in a materialized view there is one MLOG$ table, with the name MLOG$_<TABLENAME>.
These MLOG$ tables serve to store all DML changes that will be transported to a materialized view.
SQL> SELECT * FROM MLOG$_TABLE_A
WHERE rownum < 21
ORDER BY 2 DESC;
CDR_FORWARD_NAME SNAPTIME$$ DMLTYPE$$ OLD_NEW$$
CHANGE_VECTOR$$
------------------------- ----------- --------- --------- ------
REMOTE_DB-01_TABLE_A 08/08/2012 I N FEFF
REMOTE_DB-01_TABLE_A 05/08/2012 U U 400C
REMOTE_DB-01_TABLE_A 05/08/2012 U U 600C
REMOTE_DB-02_TABLE_A 05/08/2012 U U 600C
REMOTE_DB-03_TABLE_A 05/08/2012 U U 400C
REMOTE_DB-03_TABLE_A 26/06/2012 I N FEFF
RUPD$ Tables
There is one RUPD$ table per MLOG$ table
This is a temporary updatable snapshot log created for Java Rep API.
When the user creates a PK snapshot Log on a Master Table, or does an 'ALTER SNAPSHOT LOG ADD PRIMARY KEY', in
addition to MLOG$_<tablename>, a temporary table by name RUPD$_<tablename> is also automatically created.
SYS.SNAP$
Lists the existing snapshots on the consumer site
SYS.SLOG$
On the Master side.
Lists the Consumer snapshots that are using entries in SYS.SLOG$ at the master site.
This table might grow up to quite large, having tens thousands records.
SQL> SELECT mowner, master, snapid, snaptime
2 FROM SYS.SLOG$
WHERE ORDER BY 2 DESC ;
MOWNER MASTER SNAPID SNAPTIME
------------------ ------------ ------- ----------
MASTER_USER TABLE_A 95 31/03/2014
MASTER_USER TABLE_A 375 18/12/2008
MASTER_USER TABLE_A 655 30/12/2012
MASTER_USER TABLE_B 961 07/01/2014
MASTER_USER TABLE_B 1335 15/03/2009
The problem might arise if a Snapshot on remote DB was dropped, but the Master DB is not aware of this change.
For example - bring down a test environment that had a Snapshot from Production DB.
The problem is Oracle would not purge old records from DBA_SNAPSHOT_LOG, and the table would grow bigger and bigger.
When deciding whether to purge snapshot log records, Oracle compares SYS.SLOG$.snaptime for the table with MLOG$_.snaptime$$.
The rows with a MLOG$_.snaptime$$ equal to or older than the oldest SYS.SLOG$.snaptime for the table are purged from the log.
If an orphan entry exists in SYS.SLOG$ at the master site for a deleted snapshot, the SNAPTIME in SLOG$ will not be updated.
Consequently, any records in the snapshot log will never be purged during a refresh.
The following query can be useful in identifying situations where a snapshot entry exists in SLOG$ but is not registered and has not been updated in a long time.
SELECT
REGISTERED_SNAPSHOTS.name AS snapname,
snapid,
NVL(REGISTERED_SNAPSHOTS.snapshot_site, 'not registered') AS snapsite,
snaptime
FROM
SYS.SLOG$ SYS_SLOG,
DBA_REGISTERED_SNAPSHOTS REGISTERED_SNAPSHOTS
WHERE SYS_SLOG.snapid=REGISTERED_SNAPSHOTS.snapshot_id(+)
AND mowner LIKE UPPER('&owner')
AND master LIKE UPPER('&table_name');
To remove orphaned entries and Free up space:
Remove orphaned entries:
DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG
Reset the high watermark of the materialized view log:
ALTER MATERIALIZED VIEW LOG '&snapshot_log' SHRINK SPACE.
DBMS_REFRESH.refresh and USER_JOBS
Per Oracle documentation:
"Some job queue requests are created automatically.
An example is refresh support for materialized views.
If you wish to have your materialized views updated automatically, you must set
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10 SCOPE=BOTH;
USER_SNAPSHOT_LOGS
Important Tables for querying Refresh issues
On Consumer side
USER_REFRESH/ALL_REFRESH/DBA_REFRESH
List of Refresh Groups
USER_REFRESH_CHILDREN/ALL_REFRESH_CHILDREN/DBA_REFRESH_CHILDREN
List of Materialized Views per Refresh Group
SELECT REFRESH.rowner,
REFRESH.rname,
REFRESH.job,
REFRESH_CHILDREN.name,
REFRESH.next_date next_refresh
FROM ALL_REFRESH REFRESH,
ALL_REFRESH_CHILDREN REFRESH_CHILDREN
WHERE REFRESH.job = REFRESH_CHILDREN.job
ORDER BY name;
USER_MVIEW_LOGS and USER_SNAPSHOT_LOGS
Both Tables exist on Master side.
USER_MVIEW_LOGS
List Master Table and their MLOG$ table.
One record per Owner, Master Table, Log Table (MLOG$)
USER_SNAPSHOT_LOGS
Lists same data as in USER_MVIEW_LOGS plus data per each snapshot_id.
One record per Owner, Master Table, Log Table (MLOG$), Snapshot ID.
USER_SNAPSHOT_LOGS Reference.
Each time a table in Master is refreshed, an entry is updated in this table.
To see the refreshed tables:
SELECT UNIQUE MASTER FROM LOG_TABLE.
The rows looks like:
SQL> SELECT log_owner, master, log_table, current_snapshots
FROM USER_SNAPSHOT_LOGS WHERE rownum < 5;
LOG_OWNER MASTER LOG_TABLE CURRENT_SNAPSHOTS
--------- ------------ --------------------- ----------------
USER_A TABLE_A MLOG$_TABLE_A 18/12/2008 09:28:
USER_A TABLE_A MLOG$_TABLE_A 15/03/2009 08:54:
USER_A TABLE_A MLOG$_TABLE_A 15/03/2009 08:54:
USER_A TABLE_B MLOG$_TABLE_B 25/03/2009 15:35:
USER_A TABLE_B MLOG$_TABLE_B 26/03/2009 15:35:
MLOG$_<TABLE>
Per each Materialized View, there is a MLOG$_<TABLE> in the Master DB.
USER_MVIES
List of Materialized Views
Same entries as in USER_REFRESH_CHILDREN
================================================
ORA-23413: Table does not have a materialized view log
SELECT log_owner, master, log_table
FROM DBA_MVIEW_LOGS;
ALL_MVIEW_REFRESH_TIMES
- Lists the last refresh time per Materialized View.
MATERIALIZED VIEW LOG
These are the MLOG$_<TABLE> tables listed in USER_MVIEW_LOGS and in USER_SNAPSHOT_LOGS.
They should be created explicitly.
CREATE MATERIALIZED VIEW LOG ON MASTER_OWNER.MASTER_TABLE
DBMS_REFRESH Package
See Oracle Documentation: DBMS_REFRESH
DBMS_REFRESH enables you to create groups of materialized views that can be refreshed together as a single transaction.
The methods for DBMS_REFRESH:
MAKE(), CHANGE(), DESTROY() - Create, Modify and Delete the Refresh Group.
ADD(), SUBTRACT() - Add and Remove materialized views Refresh Group
DBA_RGROUP
See Oracle Documentation: DBA_RGROUP
A View that lists all the Refresh Groups.
Each entry in DBA_RGROUP has an entry in DBA_JOBS, linked by DBA_RGROUP.job.
SELECT *
FROM DBA_JOBS
WHERE job IN (SELECT job FROM DBA_RGROUP)
DBMS_SNAPSHOT Package
See Oracle Documentation: DBMS_SNAPSHOT
DBMS_MVIEW is a synonym for DBMS_SNAPSHOT
Package that handles refresh snapshots that are not part of the same refresh group and purge logs.
DBMS_MVIEW Package
DBMS_MVIEW is a synonym for DBMS_SNAPSHOT
It got several useful procedures:
REFRESH - Refreshes materialized views that are not members of the same refresh group
PURGE_LOG - Purges rows from the materialized view log.
ESTIMATE_MVIEW_SIZE - This procedure estimates the size of a materialized view that you might create
EXPLAIN_MVIEW - That would give some info about the existing Materialized View.
and more...
DBA_REGISTERED_SNAPSHOTS
This table lists Remote snapshots of local tables.
This table exists in the Master DB.
SELECT owner, name, snapshot_id
FROM DBA_REGISTERED_SNAPSHOTS
WHERE owner like 'REMOTE_DB%'
ORDER BY snapshot_id
OWNER NAME SNAPSHOT_ID
------------- ------------- ------------
REMOTE_DB_A TABLE_A_SS 43661
REMOTE_DB_A TABLE_B_SS 43662
REMOTE_DB_A TABLE_C_SS 43663
REMOTE_DB_B TABLE_A_SS 43664
REMOTE_DB_B TABLE_B_SS 43665
If There are N Remote sites - There should be N Entries in per each table, in DBA_REGISTERED_SNAPSHOTS.
Join with USER_SNAPSHOT_LOGS, to see last refresh date.
SELECT DBA_REGISTERED_SNAPSHOTS.owner,
DBA_REGISTERED_SNAPSHOTS.name,
DBA_REGISTERED_SNAPSHOTS.snapshot_id,
USER_SNAPSHOT_LOGS.current_snapshots
FROM USER_SNAPSHOT_LOGS,
DBA_REGISTERED_SNAPSHOTS
WHERE DBA_REGISTERED_SNAPSHOTS.snapshot_id =
USER_SNAPSHOT_LOGS.snapshot_id
AND DBA_REGISTERED_SNAPSHOTS.owner = 'REMOTE_DB_A'
What if there are several Remote sites with same Materialized View looking at the same Master Table, and all are doing Fast Refresh?
With Fast Refresh, rows are deleted from Log Table once the data was refreshed.
Multiple simple snapshots can use the same snapshot log, in that case already used to refresh one snapshot would still be needed to refresh another snapshot.
Oracle does not delete rows from the log until all snapshots have used them.
MLOG$ Tables
Applicable only to Refresh Fast option.
The MLOG% Tables exist only in the Master DB.
SELECT *
FROM USER_TABLES
WHERE TABLE_NAME LIKE 'MLOG%'
Per each table that participates in a materialized view there is one MLOG$ table, with the name MLOG$_<TABLENAME>.
These MLOG$ tables serve to store all DML changes that will be transported to a materialized view.
SQL> SELECT * FROM MLOG$_TABLE_A
WHERE rownum < 21
ORDER BY 2 DESC;
CDR_FORWARD_NAME SNAPTIME$$ DMLTYPE$$ OLD_NEW$$
CHANGE_VECTOR$$
------------------------- ----------- --------- --------- ------
REMOTE_DB-01_TABLE_A 08/08/2012 I N FEFF
REMOTE_DB-01_TABLE_A 05/08/2012 U U 400C
REMOTE_DB-01_TABLE_A 05/08/2012 U U 600C
REMOTE_DB-02_TABLE_A 05/08/2012 U U 600C
REMOTE_DB-03_TABLE_A 05/08/2012 U U 400C
REMOTE_DB-03_TABLE_A 26/06/2012 I N FEFF
RUPD$ Tables
There is one RUPD$ table per MLOG$ table
This is a temporary updatable snapshot log created for Java Rep API.
When the user creates a PK snapshot Log on a Master Table, or does an 'ALTER SNAPSHOT LOG ADD PRIMARY KEY', in
addition to MLOG$_<tablename>, a temporary table by name RUPD$_<tablename> is also automatically created.
SYS.SNAP$
Lists the existing snapshots on the consumer site
SYS.SLOG$
On the Master side.
Lists the Consumer snapshots that are using entries in SYS.SLOG$ at the master site.
This table might grow up to quite large, having tens thousands records.
SQL> SELECT mowner, master, snapid, snaptime
2 FROM SYS.SLOG$
WHERE ORDER BY 2 DESC ;
MOWNER MASTER SNAPID SNAPTIME
------------------ ------------ ------- ----------
MASTER_USER TABLE_A 95 31/03/2014
MASTER_USER TABLE_A 375 18/12/2008
MASTER_USER TABLE_A 655 30/12/2012
MASTER_USER TABLE_B 961 07/01/2014
MASTER_USER TABLE_B 1335 15/03/2009
The problem might arise if a Snapshot on remote DB was dropped, but the Master DB is not aware of this change.
For example - bring down a test environment that had a Snapshot from Production DB.
The problem is Oracle would not purge old records from DBA_SNAPSHOT_LOG, and the table would grow bigger and bigger.
When deciding whether to purge snapshot log records, Oracle compares SYS.SLOG$.snaptime for the table with MLOG$_.snaptime$$.
The rows with a MLOG$_.snaptime$$ equal to or older than the oldest SYS.SLOG$.snaptime for the table are purged from the log.
If an orphan entry exists in SYS.SLOG$ at the master site for a deleted snapshot, the SNAPTIME in SLOG$ will not be updated.
Consequently, any records in the snapshot log will never be purged during a refresh.
The following query can be useful in identifying situations where a snapshot entry exists in SLOG$ but is not registered and has not been updated in a long time.
SELECT
REGISTERED_SNAPSHOTS.name AS snapname,
snapid,
NVL(REGISTERED_SNAPSHOTS.snapshot_site, 'not registered') AS snapsite,
snaptime
FROM
SYS.SLOG$ SYS_SLOG,
DBA_REGISTERED_SNAPSHOTS REGISTERED_SNAPSHOTS
WHERE SYS_SLOG.snapid=REGISTERED_SNAPSHOTS.snapshot_id(+)
AND mowner LIKE UPPER('&owner')
AND master LIKE UPPER('&table_name');
To remove orphaned entries and Free up space:
Remove orphaned entries:
DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG
Reset the high watermark of the materialized view log:
ALTER MATERIALIZED VIEW LOG '&snapshot_log' SHRINK SPACE.
DBMS_REFRESH.refresh and USER_JOBS
Per Oracle documentation:
"Some job queue requests are created automatically.
An example is refresh support for materialized views.
If you wish to have your materialized views updated automatically, you must set
JOB_QUEUE_PROCESSES to a value of one or higher."ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10 SCOPE=BOTH;
USER_SNAPSHOT_LOGS
Important Tables for querying Refresh issues
On Consumer side
USER_REFRESH/ALL_REFRESH/DBA_REFRESH
List of Refresh Groups
USER_REFRESH_CHILDREN/ALL_REFRESH_CHILDREN/DBA_REFRESH_CHILDREN
List of Materialized Views per Refresh Group
SELECT REFRESH.rowner,
REFRESH.rname,
REFRESH.job,
REFRESH_CHILDREN.name,
REFRESH.next_date next_refresh
FROM ALL_REFRESH REFRESH,
ALL_REFRESH_CHILDREN REFRESH_CHILDREN
WHERE REFRESH.job = REFRESH_CHILDREN.job
ORDER BY name;
USER_MVIEW_LOGS and USER_SNAPSHOT_LOGS
Both Tables exist on Master side.
USER_MVIEW_LOGS
List Master Table and their MLOG$ table.
One record per Owner, Master Table, Log Table (MLOG$)
USER_SNAPSHOT_LOGS
Lists same data as in USER_MVIEW_LOGS plus data per each snapshot_id.
One record per Owner, Master Table, Log Table (MLOG$), Snapshot ID.
USER_SNAPSHOT_LOGS Reference.
Each time a table in Master is refreshed, an entry is updated in this table.
To see the refreshed tables:
SELECT UNIQUE MASTER FROM LOG_TABLE.
The rows looks like:
SQL> SELECT log_owner, master, log_table, current_snapshots
FROM USER_SNAPSHOT_LOGS WHERE rownum < 5;
LOG_OWNER MASTER LOG_TABLE CURRENT_SNAPSHOTS
--------- ------------ --------------------- ----------------
USER_A TABLE_A MLOG$_TABLE_A 18/12/2008 09:28:
USER_A TABLE_A MLOG$_TABLE_A 15/03/2009 08:54:
USER_A TABLE_A MLOG$_TABLE_A 15/03/2009 08:54:
USER_A TABLE_B MLOG$_TABLE_B 25/03/2009 15:35:
USER_A TABLE_B MLOG$_TABLE_B 26/03/2009 15:35:
MLOG$_<TABLE>
Per each Materialized View, there is a MLOG$_<TABLE> in the Master DB.
USER_MVIES
List of Materialized Views
Same entries as in USER_REFRESH_CHILDREN
================================================
ORA-23413: Table does not have a materialized view log
================================================
Reference
Oracle Materialized Views
Blogs
MATERIALIZED VIEWS concepts
Metalink
MATERIALIZED VIEW REFRESH: Locking, Performance, Monitoring (Doc ID 258252.1)
Monitoring Locks During Materialized View Refreshes (Doc ID 258258.1)
How to Monitor the Progress of a Materialized View Refresh (MVIEW) (Doc ID 258021.1)
ERROR at line 1:
ORA-23413: table "OWNER"."SOME_TABLE" does not have a materialized view log
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
CREATE MATERIALIZED VIEW LOG ON VIP700.WAP_PUSH_MSG
================================================
Error refresh Materialized view, after Master was migrated to another server
Error refresh Materialized view, after Master was migrated to another server
================================================
The Solution:
A. Refresh all Child MViews with Completer option.
A. Refresh all Child MViews with Completer option.
B. Refresh all Child MViews with Fast option.
C. refresh the Group
Errors
1. During Complete Refresh - Foreign Keys might be violated
Errors
1. During Complete Refresh - Foreign Keys might be violated
BEGIN
DBMS_SNAPSHOT.refresh ('GSM_IDD_LIST_SS', 'C');
END;
/
Error report:
ORA-12008: error in materialized view or zonemap refresh path
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3020
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2432
ORA-02292: integrity constraint (GSM_IDD_SET_FK)
ALTER TABLE GSM_IDDS DISABLE CONSTRAINT GSM_IDD_SET_FK;
BEGIN
DBMS_SNAPSHOT.refresh ('GSM_IDD_LIST_SS', 'C');
END;
/
ALTER TABLE GSM_IDDS ENABLE CONSTRAINT GSM_IDD_SET_FK;
BEGIN
DBMS_SNAPSHOT.refresh ('GSM_IDD_LIST_SS', 'F');
END;
/
2. If Running Fast refresh, without Complete refresh, following error is encountered:
Error report:
ORA-12048: error encountered while refreshing materialized view "GSM_PREFIXES_SS"
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3020
ORA-12034: materialized view log on GSM_PREFIXES younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 88
The solution: run Complete, and then Fast refresh.
BEGIN
DBMS_SNAPSHOT.refresh ('GSM_PREFIXES_SS', 'C');
DBMS_SNAPSHOT.refresh ('GSM_PREFIXES_SS', 'F');
END;
/
View All Groups and Child per group.
SELECT DBA_RGROUP.owner owner,
DBA_RGROUP.name group_name,
DBA_RCHILD.name mv_name
FROM DBA_RCHILD,
DBA_RGROUP
WHERE DBA_RCHILD.owner = DBA_RGROUP.owner
AND DBA_RCHILD.refgroup=DBA_RGROUP.refgroup
AND DBA_RCHILD.owner = (SELECT user FROM DUAL)
ORDER BY DBA_RGROUP.owner, DBA_RGROUP.name, DBA_RCHILD.name;
Generate refresh commands for a group:
SELECT 'BEGIN '||CHR(13)||'DBMS_SNAPSHOT.refresh ('''||mv_name||''', ''C'');'||CHR(13)||'END;'||CHR(13)||'/'
FROM (
SELECT DBA_RGROUP.owner,
DBA_RGROUP.name group_name,
DBA_RCHILD.name mv_name
FROM DBA_RCHILD,
DBA_RGROUP
WHERE DBA_RCHILD.owner = DBA_RGROUP.owner
AND DBA_RCHILD.refgroup=DBA_RGROUP.refgroup
AND DBA_RCHILD.owner = (SELECT user FROM DUAL)
ORDER BY DBA_RGROUP.owner, DBA_RGROUP.name, DBA_RCHILD.name
)
WHERE group_name = 'RI_REFRESH_GROUP'
BEGIN
DBMS_SNAPSHOT.refresh ('SNAPSHOT_A', 'C');
END;
/
BEGIN
DBMS_SNAPSHOT.refresh ('SNAPSHOT_B', 'C');
END;
/
BEGIN
DBMS_SNAPSHOT.refresh ('SNAPSHOT_A', 'F');
END;
/
BEGIN
DBMS_SNAPSHOT.refresh ('SNAPSHOT_B', 'F');
END;
/
Oracle Materialized Views
Blogs
MATERIALIZED VIEWS concepts
Metalink
MATERIALIZED VIEW REFRESH: Locking, Performance, Monitoring (Doc ID 258252.1)
Monitoring Locks During Materialized View Refreshes (Doc ID 258258.1)
How to Monitor the Progress of a Materialized View Refresh (MVIEW) (Doc ID 258021.1)
No comments:
Post a Comment