Oracle | DBMS

Oracle DBMS_SYSTEM Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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 184 objects

Documented Yes

First Available 7.3.4

Security Model Owned by SYS: Execute is granted to DVSYS, GSMADMIN_INTERNAL, MDSYS, OEM_MONITOR, and SYSTEM

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
GET_OBH	READ_EV	SET_SQL_TRACE_IN_SESSION
KCFRMS	REMOVE_PARAMETER_VALUE	WAIT_FOR_EVENT
KSDDDT

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;

      /

      

      test21db

      

      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/21.0.0.0/dbhome_1

      

      PL/SQL procedure successfully completed.

      

      DECLARE

       retVal VARCHAR2(100);

      BEGIN

        dbms_system.get_env('TEMP', RetVal);

        dbms_output.put_line(RetVal);

      END;

      /

      

      PL/SQL procedure successfully completed.

GET_OBH

Returns the Oracle (Base?) Home 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/homes/OraDB21000_home1

      

      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;

      

      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 IN NUMBER, serial# IN NUMBER, sql_trace IN 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 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);

      

      SELECT sid, event, p1, seconds_in_wait, state

      FROM gv_$session_wait

      WHERE sid = 10;