Oracle DBMS_SYSTEM
Version 26ai

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

Oracle Database 26ai is now availble. If you haven't you downloaded already: Why?
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