Summary

Implement the Oracle-compatible DBMS_MONITOR package in the ivorysql_ora extension. DBMS_MONITOR allows database administrators to control end-to-end application tracing, gather metrics by client identifier, and manage diagnostic tracing for sessions and database instances.

Key Features

• Monitoring Catalog Tables:
  • sys.monitored_client_ids: tracks monitored client identifiers and their statistics/tracing states.
  • sys.monitored_sessions: stores session tracing configuration, wait/bind flag settings, and plan stats.
• Procedures:
  • CLIENT_ID_STAT_ENABLE(client_id) / CLIENT_ID_STAT_DISABLE(client_id): enables or disables metric collection by client ID.
  • CLIENT_ID_TRACE_ENABLE(client_id, waits, binds, plan_stat) / CLIENT_ID_TRACE_DISABLE(client_id): toggles cross-session trace for client ID.
  • SESSION_TRACE_ENABLE(session_id, serial_num, waits, binds, plan_stat) / SESSION_TRACE_DISABLE(session_id, serial_num): toggles tracing on specific sessions.
  • DATABASE_TRACE_ENABLE(waits, binds, plan_stat) / DATABASE_TRACE_DISABLE(): toggles instance-wide SQL tracing.

Security & Governance

• Security compliance: internal resolver functions in sys revoke execute permissions from PUBLIC (CWE-862).
• Pure backend C implementation using SPI.
• Integrated into contrib/ivorysql_ora/Makefile, contrib/ivorysql_ora/meson.build, and contrib/ivorysql_ora/ivorysql_ora_merge_sqls.
• Comprehensive regression test suite sql/dbms_monitor.sql and expected/dbms_monitor.out (>640 lines added) covering client_id statistics, client_id tracing, session tracing, database-wide tracing, parameter validation, and catalog ACL checks.

Test plan

• Pure backend C implementation using SPI.
• PL/iSQL package specification and body with default arguments.
• Catalog tables created under sys.
• Build configuration: registered in Makefile, meson.build, and ivorysql_ora_merge_sqls.
• Full regression test coverage matching ORA_REGRESS:
  • CLIENT_ID_STAT_ENABLE and CLIENT_ID_STAT_DISABLE.
  • CLIENT_ID_TRACE_ENABLE and CLIENT_ID_TRACE_DISABLE.
  • SESSION_TRACE_ENABLE and SESSION_TRACE_DISABLE.
  • DATABASE_TRACE_ENABLE and DATABASE_TRACE_DISABLE.
  • NULL parameter error checks.
  • Catalog ACL checks verifying revocation from PUBLIC.