The DBMS_MVIEW package can be used to refresh all materialized views or specified materialized views. This package can also refresh or obtain materialized views that depend on a specified object.
Subprograms
Subprogram | Description |
REFRESH_ALL_MVIEWS Procedure | Refreshes all materialized views. |
REFRESH Procedure | Refreshes specified materialized views. |
REFRESH_DEPENDENT Procedure | Refreshes materialized views that depend on a specified object. |
GET_MV_DEPENDENT Procedure | Obtains materialized views that depend on a specified object. |
REFRESH_ALL_MVIEWS
This stored procedure is used to refresh all materialized views.
Syntax
PROCEDURE REFRESH_ALL_MVIEWS (
number_of_failures OUT BINARY_INTEGER,
method IN VARCHAR2 := NULL,
rollback_seg IN VARCHAR2 := NULL,
refresh_after_errors IN BOOLEAN := FALSE,
atomic_refresh IN BOOLEAN := TRUE,
out_of_place IN BOOLEAN := FALSE);Parameters
Parameter | Description |
number_of_failures | The number of materialized views that failed to be refreshed. |
method | (Optional) The method that is used to refresh materialized views. Valid values:
Note The complete refresh method and the always refresh method are equivalent methods that refresh all materialized views. |
rollback_seg | (Optional) The rollback segment when the materialized view is refreshed. This parameter is provided for compatibility with Oracle. |
refresh_after_errors | (Optional) Specifies whether to continue refreshing the materialized view after an error occurs during the refresh. This parameter is provided for compatibility with Oracle. |
atomic_refresh | (Optional) Specifies whether the refresh is atomic. This parameter is provided for compatibility with Oracle. |
out_of_place | (Optional) This parameter is provided for compatibility with Oracle. |
Example
The following example shows how to refresh all materialized views in the current database:
CREATE TABLE test(a int, b int);
INSERT INTO test VALUES (1, 2);
CREATE MATERIALIZED VIEW mv0 AS SELECT a, b FROM test;
INSERT INTO test VALUES (2, 3);
SELECT * FROM mv0 ORDER BY 1, 2;
a | b
---+---
1 | 2
(1 row)
DECLARE
a BINARY_INTEGER;
BEGIN
DBMS_MVIEW.REFRESH_ALL_MVIEWS(a);
-- The num of refresh failures is:0
DBMS_OUTPUT.PUT_LINE('The num of refresh failures is:' || a);
END;
SELECT * FROM mv0 ORDER BY 1, 2;
a | b
---+---
1 | 2
2 | 3
(2 rows)
REFRESH
This stored procedure is used to refresh specified materialized views.
Syntax
PROCEDURE REFRESH (
list IN VARCHAR2,
method IN VARCHAR2 := NULL,
rollback_seg IN VARCHAR2 := NULL,
push_deferred_rpc IN BOOLEAN := true,
refresh_after_errors IN BOOLEAN := false,
purge_option IN BINARY_INTEGER := 1,
parallelism IN BINARY_INTEGER := 0,
heap_size IN BINARY_INTEGER := 0,
atomic_refresh IN BOOLEAN := true,
nested IN BOOLEAN := false,
out_of_place IN BOOLEAN := false,
skip_ext_data IN BOOLEAN := false);
PROCEDURE REFRESH (
tab IN DBMS_UTILITY.UNCL_ARRAY,
method IN VARCHAR2 := NULL,
rollback_seg IN VARCHAR2 := NULL,
push_deferred_rpc IN BOOLEAN := true,
refresh_after_errors IN BOOLEAN := false,
purge_option IN BINARY_INTEGER := 1,
parallelism IN BINARY_INTEGER := 0,
heap_size IN BINARY_INTEGER := 0,
atomic_refresh IN BOOLEAN := true,
nested IN BOOLEAN := false,
out_of_place IN BOOLEAN := false,
skip_ext_data IN BOOLEAN := false);Parameters
Parameter | Description |
list or tab | The materialized views that you want to refresh. |
method | (Optional) The method that is used to refresh materialized views. Valid values:
Note The complete refresh method and the always refresh method are equivalent methods that refresh all materialized views. |
rollback_seg | (Optional) The rollback segment when the materialized view is refreshed. This parameter is provided for compatibility with Oracle. |
push_deferred_rpc | (Optional) Specifies whether to push materialized view modifications to associated primary tables or primary materialized views. This parameter is valid only for materialized views that can be updated. This parameter is provided for compatibility with Oracle. |
refresh_after_errors | (Optional) Specifies whether to continue refreshing the materialized view after an error occurs during the refresh. This parameter is provided for compatibility with Oracle. |
purge_option | (Optional) This parameter is provided for compatibility with Oracle. |
parallelism | (Optional) This parameter is provided for compatibility with Oracle. |
heap_size | (Optional) This parameter is provided for compatibility with Oracle. |
atomic_refresh | (Optional) This parameter is provided for compatibility with Oracle. |
nested | (Optional) This parameter is provided for compatibility with Oracle. |
out_of_place | (Optional) This parameter is provided for compatibility with Oracle. |
skip_ext_data | (Optional) This parameter is provided for compatibility with Oracle. |
Example
The following example shows how to refresh specified materialized views:
CREATE TABLE test(a int, b int);
INSERT INTO test VALUES (1, 2);
CREATE MATERIALIZED VIEW mv0 AS SELECT a, b FROM test;
INSERT INTO test VALUES (2, 3);
SELECT * FROM mv0 ORDER BY 1, 2;
a | b
---+---
1 | 2
(1 row)
DECLARE
la DBMS_UTILITY.UNCL_ARRAY;
BEGIN
la := DBMS_UTILITY.UNCL_ARRAY('mv0');
DBMS_MVIEW.REFRESH(tab => la,
method => 'A',
rollback_seg => NULL,
push_deferred_rpc => true,
refresh_after_errors => false,
purge_option => 1,
nested => false,
out_of_place => true,
skip_ext_data => true);
END;
SELECT * FROM mv0 ORDER BY 1, 2;
a | b
---+---
1 | 2
2 | 3
(2 rows)
REFRESH_DEPENDENT
This stored procedure is used to refresh materialized views that depend on a specified object.
Syntax
PROCEDURE REFRESH_DEPENDENT (
number_of_failures OUT BINARY_INTEGER,
list IN VARCHAR2,
method IN VARCHAR2 := NULL,
rollback_seg IN VARCHAR2 := NULL,
refresh_after_errors IN BOOLEAN := false,
atomic_refresh IN BOOLEAN := true,
nested IN BOOLEAN := false,
out_of_place IN BOOLEAN := false);
PROCEDURE REFRESH_DEPENDENT (
number_of_failures OUT BINARY_INTEGER,
tab IN DBMS_UTILITY.UNCL_ARRAY,
method IN VARCHAR2 := NULL,
rollback_seg IN VARCHAR2 := NULL,
refresh_after_errors IN BOOLEAN := false,
atomic_refresh IN BOOLEAN := true,
nested IN BOOLEAN := false,
out_of_place IN BOOLEAN := false);Parameters
Parameter | Description |
number_of_failures | The number of materialized views that failed to be refreshed. |
list or tab | The objects on which materialized views to be refreshed depend. |
method | (Optional) The method that is used to refresh materialized views. Valid values:
Note The complete refresh method and the always refresh method are equivalent methods that refresh all materialized views. |
rollback_seg | (Optional) The rollback segment when the materialized view is refreshed. This parameter is provided for compatibility with Oracle. |
refresh_after_errors | (Optional) Specifies whether to continue refreshing the materialized view after an error occurs during the refresh. This parameter is provided for compatibility with Oracle. |
atomic_refresh | (Optional) Specifies whether the refresh is atomic. This parameter is provided for compatibility with Oracle. |
nested | (Optional) This parameter is provided for compatibility with Oracle. |
out_of_place | (Optional) This parameter is provided for compatibility with Oracle. |
Example
The following example shows how to refresh all materialized views that depend on a specified object:
CREATE TABLE test(a int, b int);
INSERT INTO test VALUES (1, 2);
CREATE MATERIALIZED VIEW mv0 AS SELECT a, b FROM test;
INSERT INTO test VALUES (2, 3);
SELECT * FROM mv0 ORDER BY 1, 2;
a | b
---+---
1 | 2
(1 row)
DECLARE
la DBMS_UTILITY.UNCL_ARRAY;
len INTEGER;
nof BINARY_INTEGER;
BEGIN
DBMS_UTILITY.COMMA_TO_TABLE('test', len, la);
DBMS_MVIEW.REFRESH_DEPENDENT(number_of_failures => nof,
tab => la,
method => 'A',
rollback_seg => NULL,
refresh_after_errors => true,
atomic_refresh => false,
nested => false,
out_of_place => true);
-- The num of refresh failures is:0
DBMS_OUTPUT.PUT_LINE('The num of refresh failures is:' || nof);
END;
SELECT * FROM mv0 ORDER BY 1, 2;
a | b
---+---
1 | 2
2 | 3
(2 rows)
GET_MV_DEPENDENT
This stored procedure is used to obtain all materialized views that depend on a specified object.
Syntax
PROCEDURE GET_MV_DEPENDENCIES (
mvlist IN VARCHAR2,
deplist OUT VARCHAR2);Parameters
Parameter | Description |
mvlist | The objects on which materialized views depend. |
deplist | The materialized views that depend on a specified object. |
Example
The following example shows how to return all materialized views that depend on a specified object:
CREATE TABLE test(a int, b int);
CREATE MATERIALIZED VIEW mv0 AS SELECT a, b FROM test;
CREATE MATERIALIZED VIEW mv1 AS SELECT a, b FROM test;
DECLARE
deplist VARCHAR2;
BEGIN
DBMS_MVIEW.GET_MV_DEPENDENCIES('test', deplist);
-- The dependencies are: "PUBLIC"."MV0", "PUBLIC"."MV1"
DBMS_OUTPUT.PUT_LINE('The dependencies are: ' || deplist);
END;