Setting and Unsetting System Diagnostic Events on AWS RDS

Introduction

Normally, to set a event on Oracle, you would use the  ALTER SESSION SET EVENTS. However, in RDS you do not have access to run this command.  So instead, you need to use the AWS provided RDS Admin package called rdsadmin.rdsadmin_util and call the prodcedure set_system_event. This RDS Admin procedure has only recently been introduced, so is only available for the versions listed below

• All Oracle Database 21c versions and greater

• 19.0.0.0.ru-2020-10.rur-2020-10.r1 and higher Oracle Database 19c versions

Listing Allowed System Diagnostic Events

First of all, lets look at how we can list the system events we can set.   To list all the available system events that are permisible via set_system_event,  we can use another recently introduced procedure called list_allowed_system_event.

The following shows you how to list the allowed events

SQL> SET SERVEROUTPUT ON

SQL> EXEC rdsadmin.rdsadmin_util.list_allowed_system_events; 

At the time of writing, this will produce the following output

604   - error occurred at recursive SQL level

942   - table or view does not exist

1401  - inserted value too large for column

1403  - no data found

1410  - invalid ROWID

1422  - exact fetch returns more than requested number of rows

1426  - numeric overflow

1427  - single-row subquery returns more than one row

1476  - divisor is equal to zero

1483  - invalid length for DATE or NUMBER bind variable

1489  - result of string concatenation is too long

1652  - unable to extend temp segment by  in tablespace

1858  - a non-numeric character was found where a numeric was expected

4031  - unable to allocate  bytes of shared memory ("","","","")

6502  - PL/SQL: numeric or value error

10027 - Specify Deadlock Trace Information to be Dumped

10046 - enable SQL statement timing

10053 - CBO Enable optimizer trace

10173 - Dynamic Sampling time-out error

10442 - enable trace of kst for ORA-01555 diagnostics

12008 - error in materialized view refresh path

12012 - error on auto execute of job

12504 - TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

14400 - inserted partition key does not map to any partition

31693 - Table data object  failed to load/unload and is being skipped due to error: 

Setting System Diagnostic Events

To set a system event, use the Amazon RDS procedure rdsadmin.rdsadmin_util.set_system_event. You can only set events listed in the output of rdsadmin.rdsadmin_util.list_allowed_system_events. 

The procedure2 parameters, all mandatory

• p_event (Number) - The system event numbe as listed above or from the output of the list_allowed_system_events

• p_level (number) - The event level. Oracle Support will normally provide you with the level required..

Example

SQL> EXEC rdsadmin.rdsadmin_util.set_system_event(10442,10);

Setting system event 10442 with: alter system set events '10442 level 10'

PL/SQL procedure successfully completed. 

Listing System Events that are Already Set

To list the system events that are currently set, the following procedure can be used rdsadmin.rdsadmin_util.list_set_system_events. 

Example

SQL> SET SERVEROUTPUT ON

SQL> EXEC rdsadmin.rdsadmin_util.list_set_system_events;

942 errorstack (3) - set at 2020-11-03 11:42:27

10442 level 10 - set at 2020-11-03 11:42:41

PL/SQL procedure successfully completed.

Unsetting System Diagnostic Events

To unset a system event, use the procedure rdsadmin.rdsadmin_util.unset_system_event.

The unset_system_event procedure has 1 parameters, which is  mandatory

• p_event (Number) - The system event numbe as listed above or from the output of the list_allowed_system_events

Example

SQL> SET SERVEROUTPUT ON

SQL> EXEC rdsadmin.rdsadmin_util.unset_system_event(942);

Unsetting system event 942 with: alter system set events '942 off '

PL/SQL procedure successfully completed.

Published on 

7 October 2024