Oracle DBMS_SYSTEM
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose This officially unsupported package contains some wonderful functionality for making system calls some of which are unavailable by any other means.
AUTHID DEFINER
Constants
Name Data Type Value
trace_file BINARY_INTEGER 1
alert_file BINARY_INTEGER 2
? BINARY_INTEGER 3
Dependencies SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_SYSTEM'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_SYSTEM';

-- returns 128 objects
Documented Yes
First Available 7.3.4
Security Model Owned by SYS: Execute is granted to DVSYS and MDSYS users and DBFS_ROLE and OEM_MONITOR roles
Source {ORACLE_HOME}/rdbms/admin/prvtutil.plb
Subprograms
 
ADD_PARAMETER_VALUE
Writes a listed parameter to the SPFILE following a call to ALTER SYSTEM SET dbms_system.add_parameter_value(
parname  IN VARCHAR2,
value    IN VARCHAR2,
scope    IN VARCHAR2,
sid      IN VARCHAR2,
position IN BINARY_INTEGER);
col value format a100

SELECT value
FROM gv$parameter
WHERE name = 'control_files';

exec dbms_system.add_parameter_value('control_files', 'c:\temp\control04.ctl', 'BOTH', 'orabase', 4);

SELECT value
FROM gv$parameter
WHERE name = 'control_files';
 
DIST_TXN_SYNC
Distributed transaction synchronization used in XA interfaces. Not intended for end-user use. dbms_system.dist_txn_sync(inst_num IN NUMBER);
exec dbms_system.dist_txn_sync(2);
 
GET_ENV
Returns the value of environment variables dbms_system.get_env(var IN VARCHAR2, val OUT VARCHAR2);
set serveroutput on

DECLARE
 RetVal VARCHAR2(100);
BEGIN
  dbms_system.get_env('ORACLE_SID', RetVal);
  dbms_output.put_line(RetVal);
END;
/

DECLARE
 RetVal VARCHAR2(100);
BEGIN
  dbms_system.get_env('ORACLE_HOME', RetVal);
  dbms_output.put_line(RetVal);
END;
/

DECLARE
 RetVal VARCHAR2(100);
BEGIN
  dbms_system.get_env('TEMP', RetVal);
  dbms_output.put_line(RetVal);
END;
/
 
KCFRMS
Resets the timers displayed by MAX_WAIT in GV$SESSION_EVENT and MAXIORTM and MAXIOWTM in GV$FILESTAT (X$KCFIO) dbms_system.kcfrms;
SELECT max_wait FROM gv_$session_event;

SELECT maxiortm, maxiowtm FROM gv_$filestat;

exec dbms_system.kcfrms;

SELECT max_wait FROM gv_$session_event;

SELECT maxiortm, maxiowtm FROM gv_$filestat;
 
KSDDDT
Prints the date stamp to the target file (alert log and/or trace file) dbms_system.ksdddt;
exec dbms_system.ksdddt;
 
KSDFLS
Flushes any pending output to the target alert log or trace file dbms_system.ksdfls;
exec dbms_system.ksdfls;
 
KSDIND
Does an 'indent' before the next write (ksdwrt) by printing that many colons (:) before the next write. dbms_system.ksdind(lvl IN BINARY_INTEGER);

Range of valid values from 0 to 30.
exec dbms_system.ksdind(5);
exec dbms_system.ksdwrt(3, 'Test Message');
 
KSDWRT
Prints a message to the target file (alert log and/or trace file) dbms_system.ksdwrt (dest IN BINARY_INTEGER, tst IN VARCHAR2);

1: Write to the standard trace file
2: Write to the alert log
3: Write to both files at once
exec dbms_system.ksdwrt(3, '-- Start Message --');
exec dbms_system.ksdwrt(3, 'Test Message');
exec dbms_system.ksdwrt(dbms_system.alert_file, '-- End Message --');
 
READ_EV
Get the level for events set in the current session dbms_system.read_ev (iev IN  BINARY_INTEGER, oev OUT BINARY_INTEGER);

iev: event numbers 10000 to 10999
oev: default is 0 if not set otherwise returns the event level
ALTER SYSTEM SET SQL_TRACE=TRUE;

set serveroutput on

DECLARE
 lev BINARY_INTEGER;
BEGIN
  dbms_system.read_ev(10046, lev);
  dbms_output.put_line(lev);
END;
/
 
REMOVE_PARAMETER_VALUE
Removes a listed parameter to the spfile following a call to ALTER SYSTEM SET.

Overload 1
dbms_system.remove_parameter_value(
parname IN VARCHAR2,
value   IN VARCHAR2,
scope   IN VARCHAR2,
sid     IN VARCHAR2);
TBD
Overload 2 dbms_system.remove_parameter_value(
parname  IN VARCHAR2,
position IN BINARY_INTEGER,
scope    IN VARCHAR2,
sid      IN VARCHAR2);
TBD
 
SET_BOOL_PARAM_IN_SESSION
Sets boolean-type init.ora parameters in any session dbms_system.set_bool_param_in_session(
sid     IN NUMBER,
serial# IN NUMBER,
parnam  IN VARCHAR2,
bval    IN BOOLEAN);
exec dbms_system.set_bool_param_in_session(10, 161, 'sql_trace', TRUE);
 
SET_EV
Set event trace level dbms_system.set_ev(
si IN BINARY_INTEGER,  -- session id
se IN BINARY_INTEGER,  -- session serial number
ev IN BINARY_INTEGER,  -- event number between 10000 and 10999
le IN BINARY_INTEGER,  -- event level
nm IN VARCHAR2);

Level Waits Binds
1 False False
4 False True
8 True False
12 True True
exec dbms_system.set_ev(10, 1008, 10046, 12, NULL);
 
SET_INT_PARAM_IN_SESSION
Sets integer-type init.ora parameters in any session dbms_system.set_int_param_in_session(
sid     IN NUMBER,
serial# IN NUMBER,
parnam  IN VARCHAR2,
intval  IN BINARY_INTEGER);
exec dbms_system.set_int_param_in_session(10, 161, 'sort_area_size', 1048576);
 
SET_SQL_TRACE_IN_SESSION
Turn tracing on or off in any session dbms_system.set_sql_trace_in_session(
sid       NUMBER,
serial#   NUMBER,
sql_trace BOOLEAN);
exec dbms_system.set_sql_trace_in_session(10, 1008, TRUE);

exec dbms_system.set_sql_trace_in_session(10, 1008, FALSE);
 
WAIT_FOR_EVENT
Puts the current session into a wait state for any named wait event dbms_system.wait_for_event(
event       VARCHAR2,
extended_id BINARY_INTEGER,
timeout     BINARY_INTEGER);

extended_id is placed into the P1 column of gv_$session_wait
exec dbms_system.wait_for_event('rdbms ipc message', 50, 20);

SELECT sid, event, p1, seconds_in_wait, state
FROM gv_$session_wait
WHERE sid = 10;

Related Topics
DBMS_LOG
DBMS_MONITOR
DBMS_SUPPORT
DBMS_TRACE
Packages
Trace & KPROF

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved