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