FAST out-of-place materialized view refresh problem

View All Public Scripts Login to Run Script

Script Name FAST out-of-place materialized view refresh problem
Visibility Unlisted - anyone with the share link can access
Description Workbook shows problem with refreshing materialized views using combination of FAST with out-of-place. Unfortunately, due to the LiveSQL tool's limited access to objects: - DBMS_MVIEW_STATS package - SYS.MV_REFRESH_USAGE_STATS$ table I recommend running the script on the local database.
Area SQL General / DDL
Contributor Bart
Created Thursday May 09, 2024

Statement 1
```
DROP TABLE FOO
```
```
ORA-00942: table or view does not exist 
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

DROP MATERIALIZED VIEW FOO_MV

Statement processed.

Statement 3

CREATE TABLE FOO ( 
    product_id NUMBER PRIMARY KEY, 
    product_name VARCHAR2(100), 
    product_price NUMBER(10, 2) 
)

Table created.

Statement 4

INSERT INTO FOO (product_id, product_name, product_price) VALUES (1, 'Widget A', 19.99)

1 row(s) inserted.

Statement 5

INSERT INTO FOO (product_id, product_name, product_price) VALUES (2, 'Gizmo B', 29.99)

1 row(s) inserted.

Statement 6
```
COMMIT
```
```
Statement processed.
```

Statement 7

CREATE MATERIALIZED VIEW LOG ON FOO 
WITH ROWID, PRIMARY KEY, SEQUENCE

Statement processed.

Statement 8

CREATE MATERIALIZED VIEW FOO_MV 
BUILD DEFERRED 
REFRESH FAST ON DEMAND 
AS 
SELECT 
    product_id, 
    product_name, 
    product_price 
FROM FOO

Statement processed.

Statement 9

ALTER TABLE FOO_MV DROP PRIMARY KEY

Table altered.

Statement 10

EXEC DBMS_MVIEW_STATS.SET_MVREF_STATS_PARAMS ('FOO_MV','ADVANCED',30)

ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_MVIEW_STATS' must be declared

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 11

EXEC DBMS_MVIEW.REFRESH('FOO_MV', out_of_place => true, atomic_refresh => false, method => 'C')

Statement processed.

Statement 12

INSERT INTO FOO (product_id, product_name, product_price) VALUES (3, 'Gadget X', 49.99)

1 row(s) inserted.

Statement 13

INSERT INTO FOO (product_id, product_name, product_price) VALUES (4, 'Widget B', 24.99)

1 row(s) inserted.

Statement 14
```
COMMIT
```
```
Statement processed.
```

Statement 15

EXEC DBMS_MVIEW.REFRESH('FOO_MV', out_of_place => true, atomic_refresh => false, method => 'F')

Statement processed.

Statement 16

SELECT * FROM USER_MVREF_STATS ORDER BY refresh_id DESC


MV_NAME REFRESH_ID REFRESH_METHOD REFRESH_OPTIMIZATIONS ADDITIONAL_EXECUTIONS START_TIME END_TIME ELAPSED_TIME LOG_SETUP_TIME LOG_PURGE_TIME INITIAL_NUM_ROWS FINAL_NUM_ROWS
FOO_MV 56436 FAST  -  - 09-MAY-24 11.44.36.000000 AM 09-MAY-24 11.44.36.000000 AM 0 0 0 0 0
BIN$GATIwagdksbgY0EOqMA8rA==$0 56416 COMPLETE  -  - 09-MAY-24 11.44.25.000000 AM 09-MAY-24 11.44.35.000000 AM 11 0 1 0 0

2 rows selected.

MV_NAME	REFRESH_ID	REFRESH_METHOD	REFRESH_OPTIMIZATIONS	ADDITIONAL_EXECUTIONS	START_TIME	END_TIME	ELAPSED_TIME	LOG_SETUP_TIME	LOG_PURGE_TIME	INITIAL_NUM_ROWS	FINAL_NUM_ROWS
FOO_MV	56436	FAST	-	-	09-MAY-24 11.44.36.000000 AM	09-MAY-24 11.44.36.000000 AM	0	0	0	0	0
BIN$GATIwagdksbgY0EOqMA8rA==$0	56416	COMPLETE	-	-	09-MAY-24 11.44.25.000000 AM	09-MAY-24 11.44.35.000000 AM	11	0	1	0	0

Statement 17

SELECT * FROM USER_MVREF_RUN_STATS ORDER BY refresh_id DESC


REFRESH_ID NUM_MVS MVIEWS BASE_TABLES METHOD ROLLBACK_SEG PUSH_DEFERRED_RPC REFRESH_AFTER_ERRORS PURGE_OPTION PARALLELISM HEAP_SIZE ATOMIC_REFRESH NESTED OUT_OF_PLACE NUMBER_OF_FAILURES START_TIME END_TIME ELAPSED_TIME LOG_SETUP_TIME LOG_PURGE_TIME COMPLETE_STATS_AVAILABLE
56436 1 "SQL_BMOGXDXGQUHRLSINDFZSHWKPE"."FOO_MV"  - F  - Y N 1 0 0 N N N 0  -  -  -  -  - Y
56416 1 "SQL_BMOGXDXGQUHRLSINDFZSHWKPE"."FOO_MV"  - C  - Y N 1 0 0 N N N 0  -  -  -  -  - Y
56377 1 "SQL_BMOGXDXGQUHRLSINDFZSHWKPE"."FOO_MV"  - C  - Y N 1 0 0 N N N 0  -  -  -  -  - Y
56338 1 "SQL_BMOGXDXGQUHRLSINDFZSHWKPE"."FOO_MV"  - C  - Y N 1 0 0 N N N 0  -  -  -  -  - Y
56318 1 "SQL_BMOGXDXGQUHRLSINDFZSHWKPE"."FOO_MV"  - C  - Y N 1 0 0 N N N 0  -  -  -  -  - Y
56298 1 "SQL_BMOGXDXGQUHRLSINDFZSHWKPE"."FOO_MV"  - C  - Y N 1 0 0 N N N 0  -  -  -  -  - Y

6 rows selected.

Statement 18

SELECT * FROM USER_MVREF_CHANGE_STATS ORDER BY refresh_id DESC


TBL_OWNER TBL_NAME MV_OWNER MV_NAME REFRESH_ID NUM_ROWS_INS NUM_ROWS_UPD NUM_ROWS_DEL NUM_ROWS_DL_INS PMOPS_OCCURRED PMOP_DETAILS NUM_ROWS
SQL_BMOGXDXGQUHRLSINDFZSHWKPE FOO SQL_BMOGXDXGQUHRLSINDFZSHWKPE FOO_MV 56436 2 0 0 0 N  - 0
SQL_BMOGXDXGQUHRLSINDFZSHWKPE FOO SQL_BMOGXDXGQUHRLSINDFZSHWKPE BIN$GATIwagdksbgY0EOqMA8rA==$0 56416 0 0 0 0 N  - 0

2 rows selected.

TBL_OWNER	TBL_NAME	MV_OWNER	MV_NAME	REFRESH_ID	NUM_ROWS_INS	NUM_ROWS_UPD	NUM_ROWS_DEL	NUM_ROWS_DL_INS	PMOPS_OCCURRED	PMOP_DETAILS	NUM_ROWS
SQL_BMOGXDXGQUHRLSINDFZSHWKPE	FOO	SQL_BMOGXDXGQUHRLSINDFZSHWKPE	FOO_MV	56436	2	0	0	0	N	-	0
SQL_BMOGXDXGQUHRLSINDFZSHWKPE	FOO	SQL_BMOGXDXGQUHRLSINDFZSHWKPE	BIN$GATIwagdksbgY0EOqMA8rA==$0	56416	0	0	0	0	N	-	0

Statement 19

SELECT * FROM SYS.MV_REFRESH_USAGE_STATS$ ORDER BY seq# DESC

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database

REFRESH_ID	NUM_MVS	MVIEWS	BASE_TABLES	METHOD	ROLLBACK_SEG	PUSH_DEFERRED_RPC	REFRESH_AFTER_ERRORS	PURGE_OPTION	ATOMIC_REFRESH	NESTED	OUT_OF_PLACE	START_TIME	END_TIME	ELAPSED_TIME	LOG_SETUP_TIME	LOG_PURGE_TIME	COMPLETE_STATS_AVAILABLE
56436	1	"SQL_BMOGXDXGQUHRLSINDFZSHWKPE"."FOO_MV"	-	F	-	Y	N	1	N	N	N	-	-	-	-	-	Y
56416	1	"SQL_BMOGXDXGQUHRLSINDFZSHWKPE"."FOO_MV"	-	C	-	Y	N	1	N	N	N	-	-	-	-	-	Y
56377	1	"SQL_BMOGXDXGQUHRLSINDFZSHWKPE"."FOO_MV"	-	C	-	Y	N	1	N	N	N	-	-	-	-	-	Y
56338	1	"SQL_BMOGXDXGQUHRLSINDFZSHWKPE"."FOO_MV"	-	C	-	Y	N	1	N	N	N	-	-	-	-	-	Y
56318	1	"SQL_BMOGXDXGQUHRLSINDFZSHWKPE"."FOO_MV"	-	C	-	Y	N	1	N	N	N	-	-	-	-	-	Y
56298	1	"SQL_BMOGXDXGQUHRLSINDFZSHWKPE"."FOO_MV"	-	C	-	Y	N	1	N	N	N	-	-	-	-	-	Y