CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_SYSTEM
Version 11.1
 
General
Source {ORACLE_HOME}/rdbms/admin/prvtutil.plb
First Available 7.3.4
Constants
Name Value

trace_file

1

alert_file

2

?

3
Dependencies SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_SYSTEM'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_SYSTEM';
Security Model Execute is granted to OEM_MONITOR
 
ADD_PARAMETER_VALUE (new in 11g)

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: emp\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 usage. 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(4000);
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;
/
 
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(3, '-- End Message --');
 
READ_EV

Get the level for events set in the current session

NOTE: We've received reports that READ_EV doesn't work for event number 10046 in 11gR2 (11.2.0.3)

dbms_system.read_ev (iev IN  BINARY_INTEGER, oev OUT BINARY_INTEGER);

lev: event numbers 10000 to 10999
event level: default is 0 if not set
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 (new in 11g)

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_MONITOR
DBMS_SUPPORT
Trace & TKPROF
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [54 users online]    © 2010 psoug.org