CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_MVIEW & DBMS_SNAPSHOT
Version 11.1
 
General Information
Note: dbms_mview is a synonym for dbms_snapshot
Source {ORACLE_HOME}/rdbms/admin/dbmssnap.sql
First Available 8.1.5
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
Defined Data Type 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
CDEF$ SNAP$
CON$ REG_SNAP$
MLOG$ SUM$
OBJ$ SUMDEP$
SLOG$ USER$
 
DBA_SCHEDULER_JOBS DBMS_SQL
DBA_SUMMARIES DBMS_SUMADVISOR
DBMS_ASSERT DBMS_SYSTEM
DBMS_DEFER_SYS DBMS_SYS_ERROR
DBMS_IJOB DBMS_TRANSACTION
DBMS_INDEX_UTL DBMS_UTILITY
DBMS_INTERNAL_TRIGGER DBMS_XRWMV
DBMS_IREFRESH DEFTRANDEST
DBMS_ISCHED EXPLAINMVARRAYTYPE
DBMS_ISNAPSHOT INDEXREBUILDLIST
DBMS_I_INDEX_UTL INDEXREBUILDRECORD
DBMS_JOB MVREFRESHSCHEDULE
DBMS_MVIEW MVSCHEDULEDEPENDENCIES
DBMS_OUTPUT MVSCHEDULEENTRY
DBMS_PIPE MV_RF$JOBSEQ
DBMS_REPCAT_MIG_INTERNAL OWBB_LIA
DBMS_REPCAT_SNA_UTL OWM_VIEW_UTILITIES
DBMS_REPCAT_UNTRUSTED PLITBLM
DBMS_ROWID REWRITEARRAYTYPE
DBMS_SCHEDULER UTL_ALL_IND_COMPS
DBMS_SESSION V$INSTANCE
DBMS_SNAPSHOT V$PARAMETER
DBMS_SNAPSHOT_LIB X$KSPPCV
DBMS_SNAPSHOT_UTL X$KSPPI
DBMS_SNAP_INTERNAL
Object Privileges execute
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;
 
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 .1.0\db_1 dbms\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\ora10 dbms\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
 
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 materialized views 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);
TBD
Purges rows from the materialized view log

Overload 2
dbms_snapshot.purge_mview_from_log(
mviewowner IN VARCHAR2,
mviewname  IN VARCHAR2,
mviewsite  IN VARCHAR2);
TBD
 
PURGE_SNAPSHOT_FROM_LOG
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);
Will not be developed
 
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
dmbs_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);
TBD
 
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
Deprecated. 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
Materialized Views
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [250 users online]    © 2010 psoug.org