Oracle DBMS_SYSTEM
Version 26ai

General Information
Library Note Morgan's Library Page Header
The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
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' ORDER BY 1;

Returns 206 objects
Documented Yes
Exceptions
Error Code Reason
ORA-00030 User session ID does not exist
First Available 7.3.4
Security Model Owned by SYS: Execute is granted to MDSYS and SYSTEM
Source {ORACLE_HOME}/rdbms/admin/prvtsys.plb
Subprograms
ADD_PARAMETER_VALUE KSDWRA KSDWRT
DIST_TXN_SYNC KSDWRC KSDWRU
GET_ENV KSDWRD READ_EV
GET_OBH KSDWRI REMOVE_PARAMETER_VALUE
KCFRMS KSDWRK SET_BOOL_PARAM_IN_SESSION
KSDDDT KSDWRL SET_EV
KSDFLS KSDWRM SET_INT_PARAM_IN_SESSION
KSDIND KWDWRP SET_SQL_TRACE_IN_SESSION
KSDWR KSDWRS WAIT_FOR_EVENT
 
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);

PL/SQL procedure successfully completed.
 
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;
/

ASRA23ai

PL/SQL procedure successfully completed.

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

/u01/app/oracle/product/23.0.0.0/dbhome_1

PL/SQL procedure successfully completed.

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

/var/spool/mail/oracle

PL/SQL procedure successfully completed.
 
GET_OBH
Returns the Oracle Base Home (OBH) dbms_system.get_obh(val OUT VARCHAR2);
DECLARE
 outVal VARCHAR2(60);
BEGIN
  dbms_system.get_obh(outVal);
  dbms_output.put_line(outVal);
END;
/
/u01/app/oracle/product/23.0.0.0/dbhome_1

PL/SQL procedure successfully completed.
 
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;

PL/SQL procedures successfully completed.

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;

PL/SQL procedures successfully completed.
 
KSDFLS
Flushes any pending output to the target alert log or trace file dbms_system.ksdfls;
exec dbms_system.ksdfls;

PL/SQL procedures successfully completed.
 
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);

PL/SQL procedures successfully completed.

exec dbms_system.ksdwrt(3, 'Test Message');

PL/SQL procedures successfully completed.

 -- tailing the alert log the final 2 lines are:
oracle :
Test Message
 
KSDWR (new 23ai)
Undocumented dbms_system.ksdwr(
dest     IN BINARY_INTEGER,
tst      IN VARCHAR2,
seclabel IN BINARY_INTEGER);
exec dbms_system.ksdwr(3, 'Test Message', 1);

PL/SQL procedures successfully completed.
 
KSDWRA (new 23ai)
Undocumented dbms_system.ksdwra(
dest IN BINARY_INTEGER,
tst  IN VARCHAR2);
exec dbms_system.ksdwra((1, 'ksdwrc');

PL/SQL procedures successfully completed.
 
KSDWRC (new 23ai)
Undocumented

wrote to "1" but did not find an output.		 dbms_system.ksdwrc(
dest IN BINARY_INTEGER,
tst  IN VARCHAR2);
exec dbms_system.ksdwrc(1, 'ksdwrc');

PL/SQL procedures successfully completed.
 
KSDWRD (new 23ai)
Undocumented

wrote to "1" but did not find an output.		 dbms_system.ksdwrd(
dest IN BINARY_INTEGER,
tst  IN VARCHAR2);
exec dbms_system.ksdwrd(1, 'ksdwrd');

PL/SQL procedures successfully completed.
 
KSDWRI (new 23ai)
Undocumented

wrote to "1" but did not find an output.		 dbms_system.ksdwri(
dest IN BINARY_INTEGER,
tst  IN VARCHAR2);
exec dbms_system.ksdwri(1, 'ksdwri');

PL/SQL procedures successfully completed.
 
KSDWRK (new 23ai)
Undocumented

wrote to "1" but did not find an output.		 dbms_system.ksdwrk(
dest IN BINARY_INTEGER,
tst  IN VARCHAR2);
exec dbms_system.ksdwrk(dbms_system.trace_file, 'ksdwrk');

PL/SQL procedures successfully completed.
 
KSDWRL (new 23ai)
Undocumented

wrote to "1" but did not find an output.		 dbms_system.ksdwrl(
dest IN BINARY_INTEGER,
tst  IN VARCHAR2);
exec dbms_system.ksdwrl(1, 'ksdwrl');

PL/SQL procedures successfully completed.
 
KSDWRM (new 23ai)
Undocumented

wrote to "1" but did not find an output.		 dbms_system.ksdwrm(
dest IN BINARY_INTEGER,
tst  IN VARCHAR2);
exec dbms_system.ksdwrm(1, 'Test ksdwrm');

PL/SQL procedures successfully completed.
 
KSDWRP (new 23ai)
Undocumented

wrote to "1" but did not find an output.		 dbms_system.ksdwrp(
dest IN BINARY_INTEGER,
tst  IN VARCHAR2);
exec dbms_system.ksdwrp(1, jsdwrp');

PL/SQL procedures successfully completed.
 
KSDWRS (new 23ai)
Undocumented

wrote to "1" but did not find an output.		 dbms_system.ksdwrs(
dest IN BINARY_INTEGER,
tst  IN VARCHAR2);
exec dbms_system.ksdwrs(1, 'ksdwrs');

PL/SQL procedures successfully completed.
 
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 --');

PL/SQL procedures successfully completed.

exec dbms_system.ksdwrt(3, 'Test Message');

PL/SQL procedures successfully completed.

exec dbms_system.ksdwrt(dbms_system.alert_file, '-- End Message --');

PL/SQL procedures successfully completed.
 
KSDWRU (new 23ai)
Undocumented

wrote to "1" but did not find an output.		 dbms_system.ksdwru(
dest IN BINARY_INTEGER,
tst  IN VARCHAR2);
exec dbms_system.ksdwru(1, kasqeu');

PL/SQL procedures successfully completed.
 
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;

System altered.

set serveroutput on

DECLARE
 lev BINARY_INTEGER;
BEGIN
  dbms_system.read_ev(10046, lev);
  dbms_output.put_line(lev);
END;
/
0

PL/SQL procedures successfully completed.
 
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);

PL/SQL procedures successfully completed.
 
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);

PL/SQL procedures successfully completed.
 
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);

PL/SQL procedures successfully completed.
 
SET_SQL_TRACE_IN_SESSION
Turn tracing on or off in any session dbms_system.set_sql_trace_in_session(
sid       IN NUMBER,
serial#   IN NUMBER,
sql_trace IN BOOLEAN);
exec dbms_system.set_sql_trace_in_session(10, 1008, TRUE);

PL/SQL procedures successfully completed.
exec dbms_system.set_sql_trace_in_session(10, 1008, FALSE);

PL/SQL procedures successfully completed.
 
WAIT_FOR_EVENT
Puts the current session into a wait state for any named wait event dbms_system.wait_for_event(
event       IN VARCHAR2,
extended_id IN BINARY_INTEGER,
timeout     IN 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);

-- about 60 second wait here

PL/SQL procedures successfully completed.

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

 SID EVENT                         P1 SECONDS_IN_WAIT STATE
---- ---------------------------- --- --------------- -------
  10 heartbeat redo informer        0        15541738 WAITING

Related Topics
Built-in Functions
Built-in Packages
DBMS_LOG
DBMS_MONITOR
DBMS_SUPPORT
DBMS_TRACE
Trace & TKPROF
What's New In 21c
What's New In 26ai

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