Oracle | DBMS_SNAPSHOT

Oracle DBMS_SNAPSHOT_KKXRCA Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Almost all functionality in the DBMS_MVIEW built-in package is the result of a call to this package. Much of the functionality is deprecated, much of the functionality is secured using the ACCESSIBLE BY clause, and a few objects are accessible though they provide little if any given that the same call can be made using the supported object.

AUTHID DEFINER

Dependencies

AW$	DBMS_SNAPSHOT_LIB	MVREFRESHSCHEDULE
CDEF$	DBMS_SNAPSHOT_UTL	MVSCHEDULEDEPENDENCIES
CON$	DBMS_SNAP_INTERNAL	MVSCHEDULEENTRY
DBA_SCHEDULER_JOBS	DBMS_SQL	OBJ$
DBA_SUMMARIES	DBMS_STANDARD	PLITBLM
DBMS_ASSERT	DBMS_SYSTEM	REG_SNAP$
DBMS_IJOB	DBMS_SYS_ERROR	REWRITEARRAYTYPE
DBMS_INDEX_UTL	DBMS_TRANSACTION	SLOG$
DBMS_INTERNAL_TRIGGER	DBMS_UTILITY	SNAP$
DBMS_IREFRESH	DBMS_XRWMV	SUM$
DBMS_ISCHED	DUAL	SUMDEP$
DBMS_ISNAPSHOT	EXPLAINMVARRAYTYPE	USER$
DBMS_I_INDEX_UTL	INDEXREBUILDLIST	UTL_ALL_IND_COMPS
DBMS_JOB	INDEXREBUILDRECORD	V$AW_OLAP
DBMS_LOGREP_UTIL	JOB_DEFINITION	V$INSTANCE
DBMS_OUTPUT	JOB_DEFINITION_ARRAY	V$PARAMETER
DBMS_PIPE	MLOG$	V$PROCESS
DBMS_ROWID	MVREF$_RUN_STATS	V$SQLTEXT
DBMS_SCHEDULER	MVREF$_STATS_PARAMS	V$SQL_PLAN
DBMS_SESSION	MVREF$_STATS_SEQ	X$KSPPCV
DBMS_SNAPSHOT	MVREF$_STATS_SYS_DEFAULTS	X$KSPPI

Documented No

First Available 18.1

Security Model Owned by SYS with no privileges granted

Direct access to this package is prevented by means of an Accessible By clause.

Source {ORACLE_HOME}/rdbms/admin/prvtsnps.plb

Subprograms

BEGIN_TABLE_REORGANIZATION	PMARKER	REFRESH_MV
DROP_SNAPSHOT	PURGE_DIRECT_LOAD_LOG	REGISTER_MVIEW
END_TABLE_REORGANIZATION	PURGE_LOG	REGISTER_SNAPSHOT
EXPLAIN_MVIEW	PURGE_MVIEW_FROM_LOG	SET_I_AM_A_REFRESH
EXPLAIN_REWRITE	PURGE_SNAPSHOT_FROM_LOG	SET_UP
EXPLAIN_REWRITE_SQLID	REFRESH	TESTING
GET_LOG_AGE	REFRESH_ALL	UNREGISTER_MVIEW
GET_MV_DEPENDENCIES	REFRESH_ALL_MVIEWS	UNREGISTER_SNAPSHOT
I_AM_A_REFRESH	REFRESH_DEPENDENT	WRAP_UP

BEGIN_TABLE_REORGANIZATION

Performs a process to preserve materialized view data needed for refresh dbms_snapshot_kkxrca.begin_table_reorganization( tabowner IN VARCHAR2, tabname IN VARCHAR2);

Protected by ACCESSIBLE BY clause

DROP_SNAPSHOT

Deprecated in v8 but available for backward compatibility dbms_snapshot_kkxrca.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_kkxrca.end_table_reorganization( tabowner IN VARCHAR2, tabname IN VARCHAR2);

Protected by ACCESSIBLE BY clause

EXPLAIN_MVIEW

Explains what is possible with a materialized view or potential materialized view

Overload 1 dbms_snapshot_kkxrca.explain_mview( mv IN VARCHAR2, stmt_id IN VARCHAR2:= NULL);

Protected by ACCESSIBLE BY clause

Overload 2 dbms_snapshot_kkxrca.explain_mview( mv IN CLOB, stmt_id IN VARCHAR2:= NULL);

Protected by ACCESSIBLE BY clause

Overload 3 dbms_snapshot_kkxrca.explain_mview( mv IN VARCHAR2, msg_array IN OUT sys.ExplainMVArrayType);

Protected by ACCESSIBLE BY clause

Overload 4 dbms_snapshot_kkxrca.explain_mview( mv IN CLOB, msg_array IN OUT sys.ExplainMVArrayType);

Protected by ACCESSIBLE BY clause

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_kkxrca.explain_rewrite( query IN VARCHAR2, mv IN VARCHAR2, statement_id IN VARCHAR2);

Protected by ACCESSIBLE BY clause

Overload 2 dbms_snapshot_kkxrca.explain_rewrite( query IN CLOB, mv IN VARCHAR2, statement_id IN VARCHAR2);

Protected by ACCESSIBLE BY clause

Overload 3 dbms_snapshot_kkxrca.explain_rewrite( query IN VARCHAR2, mv IN VARCHAR2, msg_array IN OUT sys.RewriteArrayType);

Protected by ACCESSIBLE BY clause

Overload 4 dbms_snapshot_kkxrca.explain_rewrite( query IN CLOB, mv IN VARCHAR2, msg_array IN OUT sys.RewriteArrayType);

Protected by ACCESSIBLE BY clause

EXPLAIN_REWRITE_SQLID

Explain Rewrite user interface using a table for output for implementation in OEM dbms_snapshot_kkxrca.explain_rewrite_sqlid( query IN VARCHAR2, mv IN VARCHAR2, statement_id IN VARCHAR2, sqlid IN VARCHAR2);

Protected by ACCESSIBLE BY clause

GET_LOG_AGE

Deprecated in v8 but available for backward compatibility dbms_snapshot_kkxrca.get_log_age( oldest IN OUT DATE, mow IN VARCHAR2, max 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_kkxrca.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_kkxrca.i_am_a_refresh RETURN BOOLEAN;

Protected by ACCESSIBLE BY clause

PMARKER

Returns a partition marker from a rowid, and is used for Partition Change Tracking (PCT) dbms_snapshot_kkxrca.pmarker(rid IN ROWID) RETURN NUMBER;

Protected by ACCESSIBLE BY clause

PURGE_DIRECT_LOAD_LOG

Purges rows from the direct loader log after they are no longer needed by any MV dbms_snapshot_kkxrca.purge_direct_load_log;

Protected by ACCESSIBLE BY clause

PURGE_LOG

Purges rows from the materialized view log dbms_snapshot_kkxrca.purge_log( master IN VARCHAR2, num IN BINARY_INTEGER, flag IN VARCHAR2);

Protected by ACCESSIBLE BY clause

PURGE_MVIEW_FROM_LOG

Purges materialized view log rows
Overload 1 dbms_snapshot_kkxrca.purge_mview_from_log(mview_id IN BINARY_INTEGER);

Protected by ACCESSIBLE BY clause

Overload 2 dbms_snapshot_kkxrca.purge_mview_from_log( mviewowner IN VARCHAR2, mviewname IN VARCHAR2, mviewsite IN VARCHAR2);

Protected by ACCESSIBLE BY clause

PURGE_SNAPSHOT_FROM_LOG

Legacy technology for purching snapshot logs. Deprecated as of 19c.
Overload 1 dbms_snapshot_kkxrca.purge_snapshot_from_log(snapshot_id IN BINARY_INTEGER);

Deprecated

Deprecated as of 19c

Overload 2 dbms_snapshot_kkxrca.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_kkxrca.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);

Protected by ACCESSIBLE BY clause

Refreshes one or more materialized views that are not members of the same refresh group

Overload 2 dbms_snapshot_kkxrca.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);

TBD

REFRESH_ALL

Refresh all snapshots due to be refreshed dbms_snapshot_kkxrca.refresh_all;

Protected by ACCESSIBLE BY clause

REFRESH_ALL_MVIEWS

Refreshes all materialized views that do not reflect changes to their master table or master materialized view dbms_snapshot_kkxrca.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, skip_ext_data IN BOOLEAN := FALSE);

Protected by ACCESSIBLE BY clause

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_kkxrca.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, skip_ext_data 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_kkxrca.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, out_of_place IN BOOLEAN := FALSE, skip_ext_data 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_kkxrca.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, out_of_place IN BINARY_INTEGER, skip_ext_data IN BINARY_INTEGER, env IN BINARY_INTEGER, resources IN BINARY_INTEGER DEFAULT 0, refresh_id IN NUMBER);

TBD

REGISTER_MVIEW

Enables the administration of individual materialized views

Overload 1 dbms_snapshot_kkxrca.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_kkxrca.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. Deprecated as of 19c.

Overload 1 dbms_snapshot_kkxrca.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

Deprecated as of 19c.

Overload 2 dbms_snapshot_kkxrca.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_kkxrca.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_kkxrca.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_kkxrca.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_kkxrca.unregister_mview( mviewowner IN VARCHAR2, mviewname IN VARCHAR2, mviewsie IN VARCHAR2);

TBD

UNREGISTER_SNAPSHOT

Enables the administration of individual snapshots (pre MV). Deprecated as of 19c. dbms_snapshot_kkxrca.unregister_snapshot( snapowner IN VARCHAR2, snapname IN VARCHAR2, snapsite IN VARCHAR2);

Deprecated

WRAP_UP

Deprecated in v8 but available for backward compatibility dbms_snapshot_kkxrca.wrap_up( mowner IN VARCHAR2, master IN VARCHAR2, sshot IN DATE, stime IN DATE);

Deprecated