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 will be available on generic Linux platforms in January and soon on AIX and Windows

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.