DBMS_SYSTEM包的使用

10g以前，对别的session做10046跟踪,sql_trace跟踪需要用到dbms_system这个包，可以我在10gR2官方文档上面没有找到这个包的解释，然而这个包很重要，下面收集一些有用的过程，有备无患。

dbms_system.get_env(var IN VARCHAR2, val OUT VARCHAR2)

这个过程返回ORACLE的环境变量

例如：

SQL> declare env varchar2(200);
2 begin
3 dbms_system.get_env('ORACLE_HOME',env);
4 dbms_output.put_line(env);
5 end;
6 /

C:/oracle/product/10.2.0/db_1 ------我的ORACLE_HOME
PL/SQL procedure successfully completed

SQL> declare sid varchar2(200);
2 begin
3 dbms_system.get_env('ORACLE_SID',sid);
4 dbms_output.put_line(sid);
5 end;
6 /

robinson-------我的ORACLE_SID

PL/SQL procedure successfully completed

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

这个过程返回events level.

例如：

SQL> declare lev integer;
2 begin
3 dbms_system.read_ev(10046,lev);
4 dbms_output.put_line(lev);
5 end;
6 /

0---0表示没有设置10046跟踪级别，因为10046级别只有1，4，8，12

PL/SQL procedure successfully completed

dbms_system.set_sql_trace_in_session(
sidNUMBER,
serial# NUMBER,
sql_trace BOOLEAN);

这个过程就是我们最常用的给别的SESSION 设置sql_trace跟踪了。

例如：

exec dbms_system.set_sql_trace_in_session(159,15,true);---开启SQL_TRACE跟踪
exec dbms_system.set_sql_trace_in_session(159,15,false);---关闭SQL_TRACE跟踪

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

这个过程就是我们常用的给别的session设置10046跟踪了

例如：

exec dbms_system.set_ev(159,15,10046,8,'robinson'); ----开启级别为8的跟踪
exec dbms_system.set_ev(159,15,10046,0,'robinson'); -----关闭跟踪

dbms_system.set_int_param_in_session(
sid IN NUMBER,
serial# IN NUMBER,
parnam IN VARCHAR2,
intval IN BINARY_INTEGER);

这个过程给任意session设置integer的参数

例如：

SQL> exec dbms_system.set_int_param_in_session(159,15,'db_file_multiblock_read_count',128);

PL/SQL procedure successfully completed

dbms_system.set_bool_param_in_session(
sid IN NUMBER,
serial# IN NUMBER,
parnam IN VARCHAR2,
bval IN BOOLEAN);

这个存储过程给任意的session设置boolean的参数

例如：

SQL> exec dbms_system.set_bool_param_in_session(159,15,'sql_trace',TRUE);

PL/SQL procedure successfully completed

SQL> exec dbms_system.set_bool_param_in_session(159,15,'sql_trace',FALSE);

PL/SQL procedure successfully completed

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:/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 usage.

dbms_system.dist_txn_sync(inst_num IN NUMBER);

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

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

execdbms_system.ksdddt;

KSDFLS

Flushes any pending output to the target alert log or trace file

dbms_system.ksdfls;

execdbms_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 ofvalid values from 0 to 30.

execdbms_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

execdbms_system.ksdwrt(3, '-- Start Message --');
execdbms_system.ksdwrt(3, 'Test Message');
execdbms_system.ksdwrt(3, '-- 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);

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

execdbms_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

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

execdbms_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(
sidNUMBER,
serial# NUMBER,
sql_trace BOOLEAN);

execdbms_system.set_sql_trace_in_session(10, 1008, TRUE);

execdbms_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

execdbms_system.wait_for_event('rdbms ipc message', 50, 20);

SELECT sid, event, p1, seconds_in_wait, state
FROM gv_$session_wait
WHERE sid = 10;