| 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\11.1.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\ora10\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 |
| |
| 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 |
