Oracle UTL_RPADV
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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.
Source {ORACLE_HOME}/rdbms/admin/utlrpadv.sql
Subprograms
 
ALTER_MONITORING
Alters monitoring of replication performance 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);
exec utl_rpadv.alter_monitoring(600, 20, 25, 20, 72);
 
COLLECT_STATS
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);
exec utl_rpadv.collect_stats(num_runs=>6);
 
IS_MONITORING
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;
/
 
SHOW_STATS
Print statistics for a replication path 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);
exec utl_rpadv.show_stats(show_setting => TRUE);
 
SHOW_STATS_HTML
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);
exec utl_rpadv.show_stats('ORACLE_HOME', 'SPADVRPT_' || TO_CHAR(TRUNC(sysdate)));
 
START_MONITORING
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);
exec utl_rpadv.start_monitoring(top_event_threshold => 20);
 
STOP_MONITORING
Stops persistent monitoring of replication performance utl_rpadv.stop_monitoring(purge IN BOOLEAN DEFAULT FALSE);
exec utl_rpadv.stop_monitoring(TRUE);

Related Topics
Built-in Functions
Built-in Packages
What's New In 19c
What's New In 20c-21c

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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx