Oracle DBMS_MVIEW aka DBMS_SNAPSHOT
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose API supporting the refreshing of snapshot (materialized view) that are not part of the same refresh group and purging of MV snapshot logs.

dbms_mview is a synonym for dbms_snapshot which is the name of the actual package.
AUTHID DEFINER
Constants
Name Data Type Value
reg_unknown NUMBER 0
reg_v7_snapshot NUMBER 1
reg_v8_snapshot NUMBER 2
reg_repapi_snapshot NUMBER 3
reg_rowid_mview NUMBER 16
reg_primary_key_mview NUMBER 32
reg_object_id_mview NUMBER 536870912
reg_fast_refreshable_mview NUMBER 1
reg_updatable_mview NUMBER 2
Data Types CREATE TYPE sys.RewriteMessage AS OBJECT(
mv_owner       VARCHAR2(30),
mv_name        VARCHAR2(30),
sequence       NUMBER(3),
query_text     VARCHAR2(2000),
message        VARCHAR2(512),
pass           VARCHAR2(3),
mv_in_msg      VARCHAR2(30),
measure_in_msg VARCHAR2(30),
join_back_tbl  VARCHAR2(30),
join_back_col  VARCHAR2(30),
original_cost  NUMBER(10),
rewritten_cost NUMBER(10),
flags          NUMBER,
reserved1      NUMBER,
reserved2      VARCHAR2(10));
/

CREATE TYPE sys.RewriteArrayType AS VARRAY(256) OF RewriteMessage
/

CREATE TYPE SYS.ExplainMVMessage AS OBJECT (
MVOWNER         VARCHAR(30),
MVNAME          VARCHAR(30),
CAPABILITY_NAME VARCHAR(30),
POSSIBLE        VARCHAR(1),
RELATED_TEXT    VARCHAR(2000),
RELATED_NUM     NUMBER,
MSGNO           NUMBER,
MSGTXT          VARCHAR2(2000),
SEQ             NUMBER);
/

CREATE TYPE SYS.ExplainMVArrayType AS VARRAY(50) OF SYS.ExplainMVMessage
/
Dependencies
AW$ DBMS_SCHEDULER MVSCHEDULEDEPENDENCIES
CDEF$ DBMS_SESSION MVSCHEDULEENTRY
CON$ DBMS_SNAPSHOT MV_RF$JOBSEQ
DBA_BASE_TABLE_MVIEWS DBMS_SNAPSHOT_LIB OBJ$
DBA_SCHEDULER_JOBS DBMS_SNAPSHOT_UTL OLAP_TAB$
DBA_SUMMARIES DBMS_SNAP_INTERNAL PLITBLM
DBMS_ASSERT DBMS_SQL REG_SNAP$
DBMS_DEFER_SYS DBMS_STANDARD REWRITEARRAYTYPE
DBMS_IJOB DBMS_SUMMARY SLOG$
DBMS_INDEX_UTL DBMS_SYSTEM SNAP$
DBMS_INTERNAL_TRIGGER DBMS_SYS_ERROR SUM$
DBMS_IREFRESH DBMS_TRANSACTION SUMDEP$
DBMS_ISCHED DBMS_UTILITY USER$
DBMS_ISNAPSHOT DBMS_XRWMV UTL_ALL_IND_COMPS
DBMS_I_INDEX_UTL DEFTRANDEST V$AW_OLAP
DBMS_JOB DUAL V$INSTANCE
DBMS_OUTPUT EXPLAINMVARRAYTYPE V$PARAMETER
DBMS_PIPE INDEXREBUILDLIST V$SQLTEXT
DBMS_REGISTERED_MVIEWS INDEXREBUILDRECORD V$SQL_PLAN
DBMS_REPCAT_MIG_INTERNAL JOB_DEFINITION XS_DATA_SECURITY_UTIL
DBMS_REPCAT_SNA_UTL JOB_DEFINITION_ARRAY X$KSPPCV
DBMS_REPCAT_UNTRUSTED MLOG$ X$KSPPI
DBMS_ROWID MVREFRESHSCHEDULE  
Documented Yes
First Available 8.1.5
Security Model Owned by SYS with EXECUTE granted to the DBA role
GRANT execute ON sys.RewriteMessage TO PUBLIC;
GRANT execute ON sys.RewriteArrayType TO PUBLIC;
GRANT execute ON sys.ExplainMVMessage TO PUBLIC;
GRANT execute ON sys.ExplainMVArrayType TO PUBLIC;
GRANT execute ON dbms_mview TO uwclass;
Source {ORACLE_HOME}/rdbms/admin/dbmssnap.sql
Subprograms
 
BEGIN_TABLE_REORGANIZATION
Performs a process to preserve materialized view data needed for refresh dbms_snapshot.begin_table_reorganization(
tabowner IN VARCHAR2,
tabname  IN VARCHAR2);
exec dbms_snapshot.begin_table_reorganization('UWCLASS', MV_COMPLEX');
 
DROP_SNAPSHOT
Deprecated in v8 but available for backward compatibility dbms_snapshot.drop_snapshot(
mowner   IN VARCHAR2,
master   IN VARCHAR2,
snapshot IN DATE);
Deprecated
 
END_TABLE_REORGANIZATION
Ensures that the materialized view data for the master table is valid and that the master table is in the proper state dbms_snapshot.end_table_reorganization(
tabowner IN VARCHAR2,
tabname  IN VARCHAR2);
exec dbms_snapshot.end_table_reorganization('UWCLASS', MV_COMPLEX');
 
ESTIMATE_MVIEW_SIZE
Estimates the size of a materialized view that you might create, in bytes and rows dbms_snapshot.estimate_mview_size(
stmt_id       IN  VARCHAR2,
select_clause IN  VARCHAR2,
num_rows      OUT NUMBER,
num_bytes     OUT NUMBER);
set serveroutput on

DECLARE
 out_rows   NUMBER;
 out_bytes  NUMBER;
BEGIN
  dbms_snapshot.estimate_mview_size('abc',
  'SELECT srvr_id FROM servers s WHERE EXISTS (SELECT srvr_id
  FROM serv_inst i WHERE s.srvr_id = i.srvr_id)',
  out_rows, out_bytes);

  dbms_output.put_line(out_rows);
  dbms_output.put_line(out_bytes);
END;
/
 
EXPLAIN_MVIEW
Explains what is possible with a materialized view or potential materialized view

Overload 1
dbms_snapshot.explain_mview(
mv      IN VARCHAR2,
stmt_id IN VARCHAR2:= NULL);
Note: You must run the utlxmv.sql script to create MV_CAPABILITIES_TABLE in the current schema prior to calling EXPLAIN_MVIEW except when you direct output to a VARRAY. The script is found in the admin directory.

SQL> @c:\oracle\product\11.2.0\db_1\rdbms\admin\utlxmv.sql

desc mv_capabilities_table

CREATE MATERIALIZED VIEW mv_complex
TABLESPACE uwdata
REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + 1
AS SELECT s.srvr_id, i.installstatus, COUNT(*)
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
GROUP BY s.srvr_id, i.installstatus;

exec dbms_snapshot.explain_mview('MV_COMPLEX');

SELECT capability_name, possible
FROM mv_capabilities_table;
Overload 2 dbms_snapshot.explain_mview(
mv      IN CLOB,
stmt_id IN VARCHAR2:= NULL);
TRUNCATE TABLE mv_capabilities_table;

DECLARE
 mv CLOB :=
'CREATE MATERIALIZED VIEW mv_rewrite TABLESPACE uwdata
 REFRESH ON DEMAND ENABLE QUERY REWRITE AS SELECT s.srvr_id,
 i.installstatus, COUNT(*) FROM servers s, serv_inst i
 WHERE s.srvr_id = i.srvr_id GROUP BY s.srvr_id, i.installstatus';
BEGIN
  dbms_snapshot.explain_mview(mv);
END;
/

SELECT capability_name, possible
FROM mv_capabilities_table;
Overload 3 dbms_snapshot.explain_mview(
mv        IN     VARCHAR2,
msg_array IN OUT sys.ExplainMVArrayType);
desc sys.ExplainMVArrayType

CREATE TABLE test (
explaincol sys.ExplainMVArrayType);

DECLARE
 mv VARCHAR2(512) :=
'CREATE MATERIALIZED VIEW mv_rewrite TABLESPACE uwdata
 REFRESH ON DEMAND ENABLE QUERY REWRITE AS SELECT s.srvr_id,
 i.installstatus, COUNT(*) FROM servers s, serv_inst i
 WHERE s.srvr_id = i.srvr_id GROUP BY s.srvr_id, i.installstatus';
 rset sys.ExplainMVArrayType;
BEGIN
  dbms_snapshot.explain_mview(mv, rset);

  INSERT INTO test
  VALUES
  (rset);
END;
/

SELECT * FROM test;
Overload 4 dbms_snapshot.explain_mview(
mv        IN     CLOB,
msg_array IN OUT sys.ExplainMVArrayType);
desc sys.ExplainMVArrayType

CREATE TABLE test (
explaincol sys.ExplainMVArrayType);

DECLARE
 mv CLOB :=
'CREATE MATERIALIZED VIEW mv_rewrite TABLESPACE uwdata
 REFRESH ON DEMAND ENABLE QUERY REWRITE AS SELECT s.srvr_id,
 i.installstatus, COUNT(*) FROM servers s, serv_inst i
 WHERE s.srvr_id = i.srvr_id GROUP BY s.srvr_id, i.installstatus';
 rset sys.ExplainMVArrayType;
BEGIN
  dbms_snapshot.explain_mview(mv, rset);

  INSERT INTO test
  VALUES
  (rset);
END;
/

SELECT * FROM test;
 
EXPLAIN_REWRITE
Explains why a query failed to rewrite or why the optimizer chose to rewrite a query with a particular materialized view

Overload 1
dbms_snapshot.explain_rewrite(
query        IN VARCHAR2,
mv           IN VARCHAR2 := NULL,
statement_id IN VARCHAR2 := NULL);
Note: To obtain the output into a table, you must run the utlxrw.sql script before calling EXPLAIN_REWRITE. This script creates a table named REWRITE_TABLE in the current schema.

SQL> c:\oracle\product\11.2.0\dbhome_1\rdbms\admin\utlxrw.sql

desc rewrite_table

DECLARE

SQLstr VARCHAR2(4000) := 'SELECT s.srvr_id, i.installstatus, COUNT(*)
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
GROUP BY s.srvr_id, i.installstatus';

BEGIN
  dbms_snapshot.explain_rewrite(SQLstr, 'UWCLASS.MV_COMPLEX');
END;
/

SELECT query, message
FROM rewrite_table;

TRUNCATE TABLE rewrite_table;

DECLARE

SQLstr VARCHAR2(4000) := 'SELECT COUNT(*) FROM serv_inst
WHERE srvr_id = 2 AND installstatus = ''I''';

BEGIN
  dbms_snapshot.explain_rewrite(SQLstr, 'UWCLASS.MV_COMPLEX');
END;
/

SELECT query, message
FROM rewrite_table;
Overload 2 dbms_snapshot.explain_rewrite(
query        IN CLOB,
mv           IN VARCHAR2 := NULL,
statement_id IN VARCHAR2 := NULL);
TBD
Overload 3 dbms_snapshot.explain_rewrite(
query        IN     VARCHAR2,
mv           IN     VARCHAR2 := NULL,
msg_array    IN OUT sys.RewriteArrayType);
TBD
Overload 4 dbms_snapshot.explain_rewrite(
query        IN     CLOB,
mv           IN     VARCHAR2 := NULL,
msg_array    IN OUT sys.RewriteArrayType);
TBD
 
EXPLAIN_REWRITE_SQLID
Explain Rewrite user interface using a table for output for implementation in OEM dbms_snapshot.explain_rewrite_sqlid(
query        IN VARCHAR2,          -- SQL to explain
mv           IN VARCHAR2 := NULL,  -- fully qualified MV name
statement_id IN VARCHAR2 := NULL,  -- a unique identifier for the explain statement
sqlid        IN VARCHAR2 := NULL); -- OEM SQL_ID of the query
TBD
 
GET_LOG_AGE
Deprecated in v8 but available for backward compatibility dbms_snapshot.get_log_age(
oldest IN OUT DATE,
mow    IN     VARCHAR2,
mas    IN     VARCHAR2);
Deprecated
 
GET_MV_DEPENDENCIES
Finds the list of materialized view that are directly dependent on the list of tables or materialized views that has been specified dbms_snapshot.get_mv_dependencies(
list    IN  VARCHAR2,
deplist OUT VARCHAR2);
TBD
 
I_AM_A_REFRESH
Returns the value of the I_AM_REFRESH package state dbms_snapshot.i_am_a_refresh RETURN BOOLEAN;
set serveroutput on

BEGIN
  IF dbms_snapshot.i_am_a_refresh THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/
 
PMARKER
Returns a partition marker from a rowid, and is used for Partition Change Tracking (PCT) dbms_mview.pmarker(rid IN ROWID) RETURN NUMBER PARALLEL_ENABLE;
CREATE MATERIALIZED VIEW mv_complex
TABLESPACE uwdata
REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + 1
AS SELECT s.srvr_id, i.installstatus, COUNT(*)
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
GROUP BY s.srvr_id, i.installstatus;

SELECT rowid FROM mv_complex;

SELECT dbms_snapshot.pmarker('AAATxjAAEAAAAJtAAS') FROM dual;
 
PURGE_DIRECT_LOAD_LOG
Purges rows from the direct loader log after they are no longer needed by any MV dbms_snapshot.purge_direct_load_log;
exec dbms_snapshot.purge_direct_load_log;
 
PURGE_LOG
Purges rows from the materialized view log dbms_snapshot.purge_log(
master IN VARCHAR2,
num    IN BINARY_INTEGER := 1,
flag   IN VARCHAR2       := 'NOP');
exec dbms_snapshot.purge_log('servers',1,'delete');
 
PURGE_MVIEW_FROM_LOG
Purges rows from the materialized view log

Overload 1
dbms_snapshot.purge_mview_from_log(mview_id IN BINARY_INTEGER);
CREATE MATERIALIZED VIEW LOG ON servers
PCTFREE 0 PCTUSED 99
TABLESPACE uwdata
WITH PRIMARY KEY, ROWID, SEQUENCE;

CREATE MATERIALIZED VIEW mv_simple
TABLESPACE uwdata
BUILD IMMEDIATE
REFRESH FAST ON COMMIT AS
SELECT * FROM servers;

SELECT *
FROM dba_base_table_mviews
WHERE owner = 'UWCLASS';

OWNER                     MASTER                         MVIEW_LAST_REFRESH_T   MVIEW_ID
------------------------- ------------------------------ -------------------- ----------
UWCLASS                   SERVERS                        17-MAY-2012 15:43:02         23

exec dbms_snapshot.purge_mview_from_log(23);
Purges rows from the materialized view log

Overload 2
dbms_snapshot.purge_mview_from_log(
mviewowner IN VARCHAR2,
mviewname  IN VARCHAR2,
mviewsite  IN VARCHAR2);
-- use materialized view created in the overload 1 demo

desc dba_registered_mviews

col mview_site format a15

SELECT name, mview_site, mview_id
FROM dba_registered_mviews
WHERE owner = 'UWCLASS';

NAME                           MVIEW_SITE
------------------------------ ---------------
MV_SIMPLE                      ORABASE

exec dbms_snapshot.purge_mview_from_log('UWCLASS', 'MV_SIMPLE', 'ORABASE');

*
ERROR at line 1:
ORA-23425: invalid materialized view identifier 23
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2873
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2911
ORA-06512: at line 1

-- looks like we have an unreported bug in the code so stick to overload 1
 
PURGE_SNAPSHOT_FROM_LOG
Legacy technology for purching snapshot logs
Overload 1
dbms_snapshot.purge_snapshot_from_log(snapshot_id IN BINARY_INTEGER);
Deprecated
Overload 2 dbms_snapshot.purge_snapshot_from_log(
snapowner IN VARCHAR2,
snapname  IN VARCHAR2,
snapsite  IN VARCHAR2);
Deprecated
 
REFRESH
Refreshes one or more materialized views that are not members of the same refresh group

Overload 1
dbms_snapshot.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);
exec dbms_mview.refresh('MV_COMPLEX', 'C');
Refreshes one or more materialized views that are not members of the same refresh group

Overload 2
dbms_snapshot.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);
TBD
 
REFRESH_ALL
Refresh all snapshots that are due to be refreshed dbms_snapshot.refresh_all;
exec dbms_snapshot.refresh_all;
 
REFRESH_ALL_MVIEWS
Refreshes all materialized views that do not reflect changes to their master table or master materialized view dbms_snapshot.refresh_all_mview(
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);
TBD
 
REFRESH_DEPENDENT
Refreshes all table-based materialized views that depend on a specified master table or master materialized view, or list of master tables or master materialized views

Overload 1
dbms_snapshot.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);
TBD
Refreshes all table-based materialized views that depend on a specified master table or master materialized view, or list of master tables or master materialized views

Overload 2
dbms_snapshot.refresh_dependent(
number_of_failures   OUT BINARY_INTEGER,
tab                  IN  dmbs_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);
TBD
 
REFRESH_MV
Internal Procedure ONLY. DO NOT USE DIRECTLY. The added parameter 'resources' for internal parallel resource load balancing dbms_snapshot.refresh_mv(
pipename       IN VARCHAR2,
mv_index       IN BINARY_INTEGER,
owner          IN VARCHAR2,
name           IN VARCHAR2,
method         IN VARCHAR2,
rollseg        IN VARCHAR2,
atomic_refresh IN BINARY_INTEGER,
env            IN BINARY_INTEGER,
resources      IN BINARY_INTEGER DEFAULT 0);
TBD
 
REGISTER_MVIEW
Enables the administration of individual materialized views

Overload 1
dbms_snapshot.register_mview(
mviewowner IN VARCHAR2,
mviewname  IN VARCHAR2,
mviewsite  IN VARCHAR2,
mview_id   IN DATE,
flag       IN BINARY_INTEGER,
qry_txt    IN VARCHAR2,
rep_type   IN BINARY_INTEGER := dbms_snapshot.reg_unknown);
TBD
Enables the administration of individual materialized views

Overload 2
dbms_snapshot.register_mview(
mviewowner IN VARCHAR2,
mviewname  IN VARCHAR2,
mviewsite  IN VARCHAR2,
mview_id   IN BINARY_INTEGER,
flag       IN BINARY_INTEGER,
qry_txt    IN VARCHAR2,
rep_type   IN BINARY_INTEGER := dbms_snapshot.reg_unknown);
TBD
 
REGISTER_SNAPSHOT
Internal Procedure ONLY. DO NOT USE DIRECTLY

Overload 1
dbms_snapshot.register_snapshot(
snapowner   IN VARCHAR2,
snapname    IN VARCHAR2,
snapsite    IN VARCHAR2,
snapshot_id IN DATE,
flag        IN BINARY_INTEGER,
qry_txt     IN VARCHAR2,
rep_type    IN BINARY_INTEGER := dbms_snapshot.reg_unknown);
Deprecated
Overload 2 dbms_snapshot.register_snapshot(
snapowner   IN VARCHAR2,
snapname    IN VARCHAR2,
snapsite    IN VARCHAR2,
snapshot_id IN BINARY_INTEGER,
flag        IN BINARY_INTEGER,
qry_txt     IN VARCHAR2,
rep_type    IN BINARY_INTEGER := dbms_snapshot.reg_unknown);
Deprecated
 
SET_I_AM_A_REFRESH
Disables or enables snapshot replication trigger at the local snapshot site dbms_snapshot.set_i_am_a_refresh(value IN BOOLEAN);
-- run the I_AM_A_REFRESH first to determine the current package value
exec dbms_snapshot.set_i_am_a_refresh(TRUE);
 
SET_UP
Deprecated in v8 but available for backward compatibility dbms_snapshot.register_set_up(
mowner   IN     VARCHAR2,
master   IN     VARCHAR2,
log      IN OUT VARCHAR2,
snapshot IN OUT DATE,
snaptime IN OUT DATE);
Deprecated
 
TESTING
Deprecated in v8 but available for backward compatibility dbms_snapshot.testing;
Deprecated
 
UNREGISTER_MVIEW
Enables the administration of individual materialized views once invoked at a master site or master materialized view site to unregister a materialized view dbms_snapshot.unregister_mview(
mviewowner IN VARCHAR2,
mviewname  IN VARCHAR2,
mviewsite  IN VARCHAR2);
TBD
 
UNREGISTER_SNAPSHOT
Enables the administration of individual snapshots (pre MV) dbms_snapshot.unregister_snapshot(
snapowner IN VARCHAR2,
snapname  IN VARCHAR2,
snapsite  IN VARCHAR2);
Deprecated
 
WRAP_UP
Deprecated in v8 but available for backward compatibility dbms_snapshot.wrap_up(
mowner IN VARCHAR2,
master IN VARCHAR2,
sshot  IN DATE,
stime  IN DATE);
Deprecated

Related Topics
DBMS_REFRESH
DBMS_SNAPSHOT_UTL
Materialized Views
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved