Database Pipes Monitor (vdbpipe.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

Database Pipes Monitor

This script monitors Oracle database pipes used for inter-session communication via the DBMS_PIPE package. It shows pipe ownership, types, and current sizes.

Script: vdbpipe.sql

rem  vdbpipe.sql
rem
ttitle 'Database Pipes'
rem
col owner format a8 heading 'OWNER'
col name format a30 heading 'PIPE NAME'
col type format a7 heading 'TYPE'
col pipe_size format 99999999 heading 'PIPE SIZE'
rem
select u.username owner,
       p.name,
       p.type,
       p."SIZE" pipe_size
  from sys.dba_users u,
       v$db_pipes p
 where p.ownerid = u.user_id(+)
 order by u.username, p.name
/

What This Script Does

This script provides visibility into Oracle’s database pipe infrastructure by showing:

• Pipe Ownership: Which database user owns each pipe
• Pipe Names: Names of all active database pipes
• Pipe Types: Whether pipes are public or private
• Pipe Sizes: Current size of each pipe in bytes
• System Overview: Complete inventory of inter-session communication channels

Usage

-- Monitor all database pipes
@vdbpipe.sql

-- Check for specific pipe patterns
SELECT username, p.name, p.type, p."SIZE"
FROM dba_users u, v$db_pipes p
WHERE p.ownerid = u.user_id(+)
  AND UPPER(p.name) LIKE '%PATTERN%';

Required Privileges

• SELECT privilege on V$DB_PIPES
• SELECT privilege on DBA_USERS
• Typically requires DBA role

Sample Output

                             Database Pipes

OWNER    PIPE NAME                      TYPE     PIPE SIZE
-------- ------------------------------ ------- ---------
HR       HR_NOTIFICATION_PIPE          PRIVATE      1024
APPS     ORA$_PIPE_GET                 PUBLIC       2048
APPS     ORA$_PIPE_PUT                 PUBLIC       2048
SCOTT    SCOTT_DEBUG_PIPE              PRIVATE      512
SYS      ALERT_PIPE                    PUBLIC       4096
         ANONYMOUS_PIPE_001            PUBLIC       1024

Key Output Columns

Column	Description
OWNER	Database user who owns the pipe (NULL for anonymous pipes)
PIPE NAME	Name of the database pipe
TYPE	PRIVATE (owner-specific) or PUBLIC (shared)
PIPE SIZE	Current size of the pipe in bytes

Understanding Database Pipes

Pipe Types

Private Pipes:

• Accessible only to the creating user/session
• More secure for sensitive communications
• Automatically cleaned up when user disconnects

Public Pipes:

• Accessible to any database user with appropriate privileges
• Useful for inter-application communication
• Persist until explicitly removed

Common Uses

Application Integration:

• Message passing between different applications
• Event notification systems
• Batch job coordination

Debugging and Monitoring:

• Debug message passing
• Application state communication
• Performance monitoring alerts

Common Use Cases

1. Monitor Application Pipes

-- Check pipes for specific applications
SELECT u.username, p.name, p.type, p."SIZE",
       CASE WHEN p."SIZE" > 0 THEN 'HAS_DATA' ELSE 'EMPTY' END as status
FROM dba_users u, v$db_pipes p
WHERE p.ownerid = u.user_id(+)
  AND u.username IN ('APP_USER', 'INTEGRATION_USER')
ORDER BY p."SIZE" DESC;

2. Find Large or Busy Pipes

-- Identify pipes with data
SELECT u.username, p.name, p.type, p."SIZE"
FROM dba_users u, v$db_pipes p
WHERE p.ownerid = u.user_id(+)
  AND p."SIZE" > 0
ORDER BY p."SIZE" DESC;

3. Audit Pipe Usage by User

-- Count pipes per user
SELECT u.username,
       COUNT(*) as pipe_count,
       SUM(p."SIZE") as total_size,
       SUM(CASE WHEN p.type = 'PUBLIC' THEN 1 ELSE 0 END) as public_pipes,
       SUM(CASE WHEN p.type = 'PRIVATE' THEN 1 ELSE 0 END) as private_pipes
FROM dba_users u, v$db_pipes p
WHERE p.ownerid = u.user_id(+)
GROUP BY u.username
ORDER BY pipe_count DESC;

4. Monitor System Pipes

-- Find Oracle system pipes
SELECT p.name, p.type, p."SIZE"
FROM v$db_pipes p
WHERE UPPER(p.name) LIKE 'ORA$%'
   OR UPPER(p.name) LIKE 'SYS_%'
ORDER BY p.name;

Pipe Management Examples

Working with DBMS_PIPE

-- Create a private pipe
DECLARE
  result INTEGER;
BEGIN
  result := DBMS_PIPE.CREATE_PIPE('MY_DEBUG_PIPE', 8192, TRUE);
  IF result = 0 THEN
    DBMS_OUTPUT.PUT_LINE('Private pipe created successfully');
  END IF;
END;
/

-- Create a public pipe
DECLARE
  result INTEGER;
BEGIN
  result := DBMS_PIPE.CREATE_PIPE('SHARED_NOTIFICATION_PIPE', 4096, FALSE);
  IF result = 0 THEN
    DBMS_OUTPUT.PUT_LINE('Public pipe created successfully');
  END IF;
END;
/

-- Check the new pipes
@vdbpipe.sql

Send and Receive Messages

-- Send a message
DECLARE
  result INTEGER;
BEGIN
  DBMS_PIPE.PACK_MESSAGE('Test message from session 1');
  DBMS_PIPE.PACK_MESSAGE(SYSDATE);
  result := DBMS_PIPE.SEND_MESSAGE('MY_DEBUG_PIPE', 10);
  DBMS_OUTPUT.PUT_LINE('Send result: ' || result);
END;
/

-- Receive a message (from another session)
DECLARE
  result INTEGER;
  message VARCHAR2(4000);
  msg_date DATE;
BEGIN
  result := DBMS_PIPE.RECEIVE_MESSAGE('MY_DEBUG_PIPE', 10);
  IF result = 0 THEN
    DBMS_PIPE.UNPACK_MESSAGE(message);
    DBMS_PIPE.UNPACK_MESSAGE(msg_date);
    DBMS_OUTPUT.PUT_LINE('Received: ' || message || ' at ' || msg_date);
  END IF;
END;
/

Clean Up Pipes

-- Remove a pipe
DECLARE
  result INTEGER;
BEGIN
  result := DBMS_PIPE.REMOVE_PIPE('MY_DEBUG_PIPE');
  IF result = 0 THEN
    DBMS_OUTPUT.PUT_LINE('Pipe removed successfully');
  END IF;
END;
/

Troubleshooting Pipe Issues

1. Check Pipe Permissions

-- Verify user has EXECUTE on DBMS_PIPE
SELECT grantee, privilege, grantable
FROM dba_tab_privs
WHERE table_name = 'DBMS_PIPE'
  AND privilege = 'EXECUTE'
ORDER BY grantee;

2. Monitor Pipe Activity

-- Create monitoring view for pipe changes
CREATE OR REPLACE VIEW pipe_monitor AS
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') as check_time,
       u.username,
       p.name,
       p.type,
       p."SIZE"
FROM dba_users u, v$db_pipes p
WHERE p.ownerid = u.user_id(+);

-- Check pipe status over time
SELECT * FROM pipe_monitor ORDER BY check_time DESC;

3. Find Orphaned Pipes

-- Look for pipes without valid owners
SELECT p.name, p.type, p."SIZE", p.ownerid
FROM v$db_pipes p
WHERE p.ownerid NOT IN (SELECT user_id FROM dba_users)
   OR p.ownerid IS NULL;

Security Considerations

Pipe Security Review

-- Audit pipe security
SELECT
  CASE WHEN u.username IS NULL THEN 'ANONYMOUS' ELSE u.username END as owner,
  p.name,
  p.type,
  CASE
    WHEN p.type = 'PUBLIC' AND p."SIZE" > 0 THEN 'SECURITY_REVIEW'
    WHEN p.name LIKE '%PASSWORD%' OR p.name LIKE '%SECRET%' THEN 'SENSITIVE_NAME'
    ELSE 'OK'
  END as security_flag
FROM dba_users u, v$db_pipes p
WHERE p.ownerid = u.user_id(+)
ORDER BY security_flag DESC, u.username;

Best Practices for Pipe Security

1. Use Private Pipes for sensitive communications
2. Avoid Sensitive Names that reveal pipe purpose
3. Regular Cleanup of unused pipes
4. Monitor Public Pipes for unauthorized usage
5. Limit DBMS_PIPE Access to necessary users only

Integration with Application Monitoring

Create Pipe Monitoring Table

-- Create table for pipe monitoring history
CREATE TABLE pipe_monitoring_log (
  log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  owner_name VARCHAR2(128),
  pipe_name VARCHAR2(128),
  pipe_type VARCHAR2(10),
  pipe_size NUMBER,
  status VARCHAR2(20)
);

-- Insert current pipe status
INSERT INTO pipe_monitoring_log (owner_name, pipe_name, pipe_type, pipe_size, status)
SELECT NVL(u.username, 'ANONYMOUS'), p.name, p.type, p."SIZE",
       CASE WHEN p."SIZE" > 0 THEN 'HAS_DATA' ELSE 'EMPTY' END
FROM dba_users u, v$db_pipes p
WHERE p.ownerid = u.user_id(+);

Automated Pipe Alerts

-- Create alert for unusual pipe activity
SELECT owner_name, pipe_name, pipe_size,
       'Large pipe detected' as alert_reason
FROM (
  SELECT NVL(u.username, 'ANONYMOUS') as owner_name,
         p.name as pipe_name,
         p."SIZE" as pipe_size
  FROM dba_users u, v$db_pipes p
  WHERE p.ownerid = u.user_id(+)
    AND p."SIZE" > 10240  -- Alert for pipes > 10KB
)
UNION ALL
SELECT owner_name, pipe_name, pipe_count,
       'Too many pipes for user' as alert_reason
FROM (
  SELECT NVL(u.username, 'ANONYMOUS') as owner_name,
         'Multiple pipes' as pipe_name,
         COUNT(*) as pipe_count
  FROM dba_users u, v$db_pipes p
  WHERE p.ownerid = u.user_id(+)
  GROUP BY u.username
  HAVING COUNT(*) > 5  -- Alert for users with > 5 pipes
);

Integration with Other Scripts

This script works well with:

• Session Analysis (gvsess.sql) - To correlate pipe usage with active sessions
• Database Health Check (db.sql) - For overall system monitoring
• User Analysis (duser.sql) - To understand user privileges related to pipes

Performance Considerations

Database pipes use shared pool memory, so monitoring them helps with:

• Memory Management: Understanding shared pool usage
• Application Debugging: Tracking inter-session communication
• Performance Tuning: Identifying communication bottlenecks
• Security Auditing: Monitoring data exchange channels

This script provides essential visibility into Oracle’s inter-session communication infrastructure and is particularly valuable for environments using DBMS_PIPE for application integration or custom messaging systems.