Skip to content
Oracle DBA Scripts & Database Utilities

Database Pipes Monitor (vdbpipe.sql)

Database Pipes Monitor Script (vdbpipe.sql)

Section titled “Database Pipes Monitor Script (vdbpipe.sql)”

Overview

Section titled “Overview”

The vdbpipe.sql script provides comprehensive monitoring of Oracle database pipes (DBMS_PIPE), showing pipe ownership, types, and current sizes. This tool is essential for monitoring inter-session communication and debugging applications that use database pipes.

Script

Section titled “Script”
REM vdbpipe.sql
REM Checks for database pipes
REM


Column pipe_owner        Format a20
Column pipe_name         Format a30
Column pipe_type         Format a10
Column pipe_size         Format 9999999


TTITLE ' DATABASE PIPE STATUS '


SELECT
   ownerid                     pipe_owner,
   name                        pipe_name,
   decode(type,1,'Private',2,'Public','Unknown') pipe_type,
   pipe_size
FROM   v$db_pipes
ORDER BY 1,2
/

What This Script Does

Section titled “What This Script Does”
  • Displays all active database pipes in the system
  • Shows pipe ownership for security and access control
  • Identifies pipe types (Private vs Public)
  • Reports current pipe size for capacity monitoring
  • Orders results by owner and pipe name for easy navigation

Usage

Section titled “Usage”
SQL> @vdbpipe.sql

Parameters

Section titled “Parameters”

This script requires no input parameters.

Required Privileges

Section titled “Required Privileges”
  • SELECT privilege on V$DB_PIPES
  • No special system privileges required

Sample Output

Section titled “Sample Output”
                    DATABASE PIPE STATUS


PIPE_OWNER           PIPE_NAME                      PIPE_TYPE  PIPE_SIZE
-------------------- ------------------------------ ---------- ---------
HR                   EMPLOYEE_SYNC_PIPE             Private         4096
SCOTT                DEBUG_PIPE                     Public          8192
SYSTEM               JOB_QUEUE_PIPE                 Private        16384
SYSTEM               ALERT_PIPE                     Public          2048

Key Output Columns

Section titled “Key Output Columns”

PIPE_OWNER

Section titled “PIPE_OWNER”
  • The schema that owns the database pipe
  • Important for security and access control

PIPE_NAME

Section titled “PIPE_NAME”
  • The name of the database pipe
  • Used to identify pipes in DBMS_PIPE calls

PIPE_TYPE

Section titled “PIPE_TYPE”
  • Private: Only accessible by the owner
  • Public: Accessible by any user with EXECUTE on DBMS_PIPE
  • Unknown: Pipe type cannot be determined

PIPE_SIZE

Section titled “PIPE_SIZE”
  • Current size of the pipe in bytes
  • Indicates how much data is currently in the pipe

Understanding the Metrics

Section titled “Understanding the Metrics”

Private vs Public Pipes

Section titled “Private vs Public Pipes”
  • Private pipes are secure and only accessible by the creating user
  • Public pipes can be accessed by any user with appropriate privileges
  • Choose pipe type based on security requirements

Pipe Size Monitoring

Section titled “Pipe Size Monitoring”
  • Large pipe sizes may indicate:
    • Slow consumer processes
    • Failed message processing
    • Application bottlenecks
  • Pipes have a maximum size limit (typically 1MB)

Common Use Cases

Section titled “Common Use Cases”

Application Debugging

Section titled “Application Debugging”
-- Check if application pipes exist
SQL> @vdbpipe.sql


-- Look for expected pipe names and verify ownership

Performance Monitoring

Section titled “Performance Monitoring”
  • Monitor pipe sizes over time
  • Identify pipes that consistently have large sizes
  • Detect potential message processing delays

Security Auditing

Section titled “Security Auditing”
  • Review all public pipes for security implications
  • Verify pipe ownership matches application design
  • Identify unexpected or unauthorized pipes

Performance Analysis

Section titled “Performance Analysis”

High Pipe Sizes

Section titled “High Pipe Sizes”

If you see consistently high pipe sizes:

  1. Check consumer process status
  2. Verify message processing speed
  3. Consider increasing consumer concurrency
  4. Review message size and frequency

Missing Expected Pipes

Section titled “Missing Expected Pipes”

Pipes are created on first use, so missing pipes may indicate:

  • Application not yet started
  • Configuration issues
  • Permission problems

Troubleshooting

Section titled “Troubleshooting”

No Rows Returned

Section titled “No Rows Returned”
  • No active pipes in the database
  • Pipes only exist while messages are present
  • Check if applications using DBMS_PIPE are running

Permission Errors

Section titled “Permission Errors”
ORA-00942: table or view does not exist
  • Grant SELECT on V$DB_PIPES to the user
  • Run as a privileged user (SYSDBA)

Large Number of Pipes

Section titled “Large Number of Pipes”
  • May indicate pipe cleanup issues
  • Review application pipe management
  • Consider implementing pipe expiration logic

Best Practices

Section titled “Best Practices”
  1. Regular Monitoring: Check pipe status during peak usage
  2. Size Alerts: Set up alerts for pipes exceeding size thresholds
  3. Security Review: Regularly audit public pipes
  4. Documentation: Document all production pipes and their purposes
  5. Cleanup: Implement proper pipe cleanup in applications

Integration with Applications

Section titled “Integration with Applications”

Monitoring Application Communication

Section titled “Monitoring Application Communication”
-- Check specific application pipes
SELECT * FROM v$db_pipes WHERE name LIKE 'APP_%';


-- Monitor pipe growth
SELECT name, pipe_size FROM v$db_pipes WHERE pipe_size > 50000;
Section titled “Related Scripts”