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
The UTL_RPADV (RePlication ADVisor)
package contains subprograms to collect and analyze statistics for the Oracle Replication components in a distributed database environment. This package uses the Oracle Replication Performance Advisor to gather statistics.
This package is not automatically installed in version 20c.
AUTHID
DEFINER
Constants
Name
Data Type
Value
Package Header Constants
version
VARCHAR2(30)
'2.0'
Package Body Constants
minutes_per_day
NUMBER
1440
seconds_per_day
NUMBER
86400
seconds_per_hour
NUMBER
3600
Dependencies
ALL_DIRECTORIES
DBMS_SQL
REPL$_PA_DATABASE_PROP
DBA_REPL_TP_COMPONENT
DBMS_STANDARD
REPL$_PA_MONITORING
DBA_REPL_TP_COMPONENT_LINK
DBMS_UTILITY
REPL$_PA_PATH_BOTTLENECK
DBA_REPL_TP_COMPONENT_STAT
DUAL
REPL$_PA_PATH_STAT
DBA_REPL_TP_DATABASE
GLOBAL_NAME
REPL$_PA_SHOW_COMP_STAT
DBA_REPL_TP_PATH_BOTTLENECK
PLITBLM
REPL$_PA_SHOW_PATH_STAT
DBA_REPL_TP_PATH_STAT
REPL$_PA_COMPONENT
USER_SCHEDULER_JOBS
DBA_SCHEDULER_JOBS
REPL$_PA_COMPONENT_LINK
USER_TABLES
DBMS_ASSERT
REPL$_PA_COMPONENT_PROP
USER_TAB_COLUMNS
DBMS_LOCK
REPL$_PA_COMPONENT_STAT
UTL_FILE
DBMS_OUTPUT
REPL$_PA_CONTROL
UTL_URL
DBMS_SCHEDULER
REPL$_PA_DATABASE
_DBA_REPL_TP_COMPONENT_PROP
Documented
Yes
Exceptions
Error Code
Reason
ORA-20113
No active monitoring job found
First Available
19c
Installation
SQL> @?/rdbms/admin/utlrpadv.sql
declare
*
ERROR at line 1:
ORA-20100: The package UTL_RPADV should not be loaded into SYS schema
ORA-06512: at line 10
Security Model
Owned by a schema, cannot be SYS, with the SELECT ANY DICTIONARY. After installation no privileges are granted on the package.
utl_rpadv.alter_monitoring(
interval IN NUMBER DEFAULT NULL, -- max 3600 sec
top_event_threshold IN NUMBER DEFAULT NULL,
bottleneck_idle_threshold IN NUMBER DEFAULT NULL,
bottleneck_flowctrl_threshold IN NUMBER DEFAULT NULL,
retention_time IN NUMBER DEFAULT NULL);
Collect statistics for all active replication paths
utl_rpadv.collect_stats(
interval IN NUMBER DEFAULT 6,
num_runs IN NUMBER DEFAULT 2,
comp_stat_table IN VARCHAR2 DEFAULT 'REPL$_ADVISOR_COMP_STAT',
path_stat_table IN VARCHAR2 DEFAULT 'REPL$_ADVISOR_PATH_STAT',
top_event_threshold IN NUMBER DEFAULT 15,
bottleneck_idle_threshold IN NUMBER DEFAULT 50,
bottleneck_flowctrl_threshold IN NUMBER DEFAULT 50);
Returns TRUE if a client has submitted a monitoring job
utl_rpadv.is_monitoring(
job_name IN VARCHAR2 DEFAULT 'REPL$_MONITORING_JOB',
client_name IN VARCHAR2 DEFAULT NULL)
RETURN BOOLEAN;
BEGIN
IF utl_rpadv.is_monitoring; THEN
dbms_output.put_line('Monitoring Job Submitted');
ELSE
dbms_output.put_line('No Monitoring Job');
END IF;
END;
/
utl_rpadv.show_stats(
path_stat_table IN VARCHAR2 DEFAULT 'REPL$_ADVISOR_PATH_STAT',
path_id IN NUMBER DEFAULT NULL, -- show all replication paths
bgn_run_id IN NUMBER DEFAULT -1, -- show the last 10 runs
end_run_id IN NUMBER DEFAULT -10,
show_path_id IN BOOLEAN DEFAULT TRUE,
show_run_id IN BOOLEAN DEFAULT TRUE,
show_run_time IN BOOLEAN DEFAULT TRUE,
show_optimization IN BOOLEAN DEFAULT TRUE,
show_setting IN BOOLEAN DEFAULT FALSE,
show_stat IN BOOLEAN DEFAULT TRUE,
show_sess IN BOOLEAN DEFAULT FALSE,
show_legend IN BOOLEAN DEFAULT TRUE);
Generates a html report of the replication performance statistics collected using collect_stats
utl_rpadv.show_stats(
directory IN VARCHAR2,
reportName IN VARCHAR2 DEFAULT 'SPADVREPORT.HTML',
comp_stat_table IN VARCHAR2 DEFAULT 'REPL$_ADVISOR_COMP_STAT',
path_id IN NUMBER DEFAULT NULL, -- show all replication paths
bgn_run_id IN NUMBER DEFAULT -1, -- show the last 10 runs
end_run_id IN NUMBER DEFAULT -10,
detailed IN BOOLEAN DEFAULT TRUE);
Begins persistent monitoring of replication performance. Allows (1) at most one monitoring job per schema, and (2) at most one EM monitoring job per database.
utl_rpadv.start_monitoring(
job_name IN VARCHAR2 DEFAULT 'REPL$_MONITORING_JOB',
client_name IN VARCHAR2 DEFAULT NULL,
query_user_name IN VARCHAR2 DEFAULT NULL,
interval IN NUMBER DEFAULT 10,
top_event_threshold IN NUMBER DEFAULT 15,
bottleneck_idle_threshold IN NUMBER DEFAULT 50,
bottleneck_flowctrl_threshold IN NUMBER DEFAULT 50,
retention_time IN NUMBER DEFAULT 24);
