The Global Parameter Table
BMC_DEBUG contains a number of features which can be enabled or disabled on a per-session basis. The global parameter table holds the values which control these features, or affect how they operate.
The Global Parameter Table
The global parameter table is called BMC_DEBUG_GLOBAL_PARAMETER. It contains a series of name/value pairs which are used to control BMC_DEBUG features.
The table is read by BMC_DEBUG when a session refers to the package for the first time. This means that changes made to global parameter values will not take effect immediately in running sessions, but only in new sessions.
Parameter Names and Default Values
Here is a list of the parameter names which can appear in the PARAM_NAME column of BMC_DEBUG_GLOBAL_PARAMETER, their types and default parameter values, and a brief description of their functions :-
The BMC_DEBUG_GLOBAL_PARAMETER table is populated with the default values for many of these parameters by the installation script, but if no rows exist in the global parameter table for any of these values, the default will be assigned by BMC_DEBUG.
How Global Parameter values are assigned to sessions
The BMC_DEBUG_GLOBAL_PARAMETER table is defined as :-
SQL> desc bmc_debug_global_parameter
Name Null? Type
----------------------------------- -------- ------------------------
PARAM_NAME NOT NULL VARCHAR2(30)
PARAM_VALUE NOT NULL VARCHAR2(30)
SID NUMBER(6)
SERIAL# NUMBER(6)
USERNAME VARCHAR2(30)
COMMENTS VARCHAR2(500)
The values in the SID, SERIAL# and USERNAME columns are used to decide which values are used for which session. When the value for a particular PARAM_NAME is being queried :-
If the values in SID and SERIAL# match the SID and SERIAL# of the current session, that PARAM_VALUE will be used.
Otherwise, if the USERNAME matches the username of the session user, that PARAM_VALUE is used.
Otherwise, the PARAM_VALUE from the row with a NULL SID, SERIAL# and USERNAME is used.
For example, if the BMC_DEBUG_GLOBAL_PARAMETER table held the following values for CALL STACK MAX SIZE (with blank values being NULL) :-
SQL> SELECT param_name,param_value,sid,serial#,username FROM bmc_debug_global_parameter
WHERE param_name = 'CALL STACK MAX SIZE';
PARAM_NAME PARAM_VALUE SID SERIAL# USERNAME
-------------------------- -------------------- ---------- ---------- ------------------------------
CALL STACK MAX SIZE 300 74 1388
CALL STACK MAX SIZE 200 FRED
CALL STACK MAX SIZE 50
If CALL STACK MAX SIZE was being set for the session with SID/Serial# of 74/1388, the parameter value would be 300.
If CALL STACK MAX SIZE was being set for a session without SID/Serial# of 74/1388, but running as user FRED, the parameter value would be 200.
If CALL STACK MAX SIZE was being set for a session without SID/Serial# of 74/1388, and not running as user FRED, the parameter value would be 50.
Parameter Value Datatypes
As the PARAM_VALUE column in the BMC_DEBUG_GLOBAL_PARAMETER is VARCHAR2, you need to make sure that values you place into that column match the expected datatype for the parameter name, as the values read from the table will be placed into internal variables of a specific datatype.
Some parameters expect INTEGER values, so you need to be sure that the PARAM_VALUE values you set for those parameters only contain the digits 0-9.
For BOOLEAN parameters, the PARAM_VALUE values which will be considered at TRUE are 'TRUE','Y','YES','1','ON' and 'ENABLE'. Any other values will be considered as FALSE.
Setting Global Parameter Values for a specific session
As we've seen, entries in the global parameter table can apply to a specific database session by including the SID and SERIAL# values from the session in the global parameter table row.
If you wanted to do this, the sequence of events would be :-
Start the session to which you want the parameter value to apply, and find the SID and SERIAL# values for it.
INSERT a row into the global parameter table with the PARAM_NAME and PARAM_VALUE which you want to apply, and including the SID/SERIAL# values from the session you want the value to apply to.
Run some instrumented code in the session created in step 1, which will have the custom global parameter value applied to it.
However, there is a problem which might prevent this working. For the new global parameter value to take effect, the session must not have invoked any BMC_DEBUG procedures before step 3, as the global parameters are read when a session first refers to the BMC_DEBUG package.
If, for example, if you were using the Database Error Logging Trigger from the Code Samples page, and something in a login script or AFTER LOGON trigger for that user caused a non-fatal error, it is possible that the BMC_DEBUG package would be initialised as part of the login process, so when you come to run some instrumented code in step 3, it would find BMC_DEBUG already initialised, so would not read the custom global parameter you had created.
You can check for this situation by using the BMC_DEBUG.DUMP_GLOBALS call from SQL*Plus, which will display the internal global variable values, which includes the timestamp when BMC_DEBUG was initialised.
If you first select the current date and time e.g.
SQL> SELECT to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE,'DD-
--------------------
16-NOV-2014 11:01:14
SQL> exec bmc_debug.dump_globals;
BMC_DEBUG.MSG called with empty stack : BMC_DEBUG version = 1.43
BMC_DEBUG.MSG called with empty stack : Oracle session id = 134
BMC_DEBUG.MSG called with empty stack : Oracle session serial# = 32
BMC_DEBUG.MSG called with empty stack : Oracle RAC instance no = 1
BMC_DEBUG.MSG called with empty stack : Oracle client hostname = pts/1
BMC_DEBUG.MSG called with empty stack : BMC_DEBUG Start timestamp = 16-NOV-2014 11:01:23.000000000 +00:00
<etc>
You should find that the 'BMC_DEBUG Start timestamp' is later than the time reported by the SELECT statement. If it is earlier, it is likely that BMC_DEBUG is being initialised by code which is executing as part of the users login process.