Oracle DBMS_SYSTEM Demos

Oracle DBMS_SYSTEM Version 11.2.0.3

General Information

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';

First Available

7.3.4

Security Model

Owned by SYS: Execute is granted to DBFS_ROLE, MDSYS, and OEM_MONITOR

Source

{ORACLE_HOME}/rdbms/admin/prvtutil.plb

Subprograms

ADD_PARAMETER_VALUE	KSDFLS	SET_BOOL_PARAM_IN_SESSION
DIST_TXN_SYNC	KSDIND	SET_EV
GET_ENV	KSDWRT	SET_INT_PARAM_IN_SESSION
KCFRMS	READ_EV	SET_SQL_TRACE_IN_SESSION
KSDDDT	REMOVE_PARAMETER_VALUE	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);

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;