Oracle | UTL

Oracle UTL_RPADV Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai is now availble. If you haven't you downloaded already: Why?

Purpose The UTL_RPADV (RePlication ADVisor) utility 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 23ai.

AUTHID CURRENT_USER

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	_DBA_REPL_TP_COMPONENT_PROP
DBMS_SCHEDULER	REPL$_PA_DATABASE

Documented Yes

Exceptions

Error Code	Reason
ORA-20113	No active monitoring job found

First Available 19c

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
{ORACLE_HOME}/rdbms/admin/fixed_utlrpadv.sql
{ORACLE_HOME}/rdbms/admin/prvtrpa.plb

SQL> / as sysdba

      

      CREATE USER c##rpadv IDENTIFIED BY apv

      DEFAULT TABLESPACE sysaux

      TEMPORARY TABLESPACE temp

      QUOTA UNLIMITED ON sysaux;

      

      GRANT select any dictionary TO c##rpadv;

      GRANT execute ON dbms_logrep_util TO c##rpadv;

      GRANT select ON system.aq$_queue_tables TO c##rpadv;

      GRANT select ON system.aq$_queues TO c##rpadv;

      GRANT execute ON dbms_lock TO c##rpadv;

      GRANT dba TO c##rpadv;

      

      conn c##rpadv/apv

      

      @?/rdbms/admin/fixed_utlrpadv.sql

      @?/rdbms/admin/prvtrpa.plb

Subprograms

ALTER_MONITORING	SHOW_STATS	START_MONITORING
COLLECT_STATS	SHOW_STATS_HTML	STOP_MONITORING
IS_MONITORING

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);

      

      PL/SQL procedure successfully completed.

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)));

      

      PL/SQL procedure successfully completed.

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);

      

      PL/SQL procedure successfully completed.

STOP_MONITORING

Stops persistent monitoring of replication performance utl_rpadv.stop_monitoring(purge IN BOOLEAN DEFAULT FALSE);

exec utl_rpadv.stop_monitoring(TRUE);

      

      PL/SQL procedure successfully completed.