dbms_system

DBMS_SYSTEM包支持很多功能(设置会话参数、启动跟踪、设置特定错误导出、写入跟踪文件、重置统计值为0),但在官方文档中没有说明.根据<>中的介绍,整理如下:

*获取环境变量值
DBMS_SYSTEM.GET_ENV(
var IN VARCHAR2,
val OUT VARCHAR2);

用法:
SQL> SET AUTOPRINT ON
SQL> VARIABLE val VARCHAR2(4000)
SQL> BEGIN
dbms_system.get_env('ORACLE HOME', :val);
END;
/
PL/SQL procedure successfully completed.
VAL
-------------------------------------------------
/opt/oracle/product/10.2

*重值最大值为0
DBMS_SYSTEM.KCFRMS();

NOTE:
影响以下3类统计值,每个事件的最大等待时间(V$SESSION EVENT.MAX WAIT),
每个文件最大读取时间(V$FILESTAT.MAXIORTM),每个文件最大写入时间(V$FILESTAT. MAXIOWTM).

*在跟踪文件中写入时间戳
DBMS_SYSTEM.KSDDDT();

NOTE:
在未启动SQL TRACE的会话中,会自动打开跟踪文件.

*将跟踪缓存写入文件(跟踪,alert.log)
DBMS SYSTEM.KSDFLS();

*在下一个输出前加':'前缀(对写入alert.log的DBMS_SYSTEM.KSDWRT操作无效果)
DBMS_SYSTEM.KSDIND(
lvl IN BINARY INTEGER);

*在跟踪文件中写消息
DBMS_SYSTEM.KSDWRT(
dest IN BINARY INTEGER,  --Destination file, 1=SQL trace file, 2=alert log, 3=both
tst IN VARCHAR2); --String to write to destination file

NOTE:
在未启动SQL TRACE的会话中,会自动打开跟踪文件.
可以利用SERVERERROR触发器,将特定错误跟踪信息加入跟踪文件.

*获取事件level
DBMS_SYSTEM.READ_EV(
iev IN BINARY INTEGER,  --Event number; usually between 10000 and 10999
oev OUT BINARY INTEGER);  --Level at which the event is set; level=0 means the event is disabled

NOTE:
Does not work in conjunction with ALTER SESSION SET SQL TRACE=TRUE
in Oracle10g and Oracle11g, since the oev returned by DBMS SYSTEM.READ EV remains 0 when
this statement is executed.
In the case,SELECT value FROM v$parameter WHERE name='sql trace' can be used to find
out whether SQL trace is switched on or not in both releases.

*设置会话参数(整型)
DBMS_SYSTEM.SET_INT_PARAM_IN_SESSION(
sid IN NUMBER,
serial# IN NUMBER,
parnam IN VARCHAR2,  --V$PARAMETER.NAME
intval IN BINARY INTEGER);

用法:
SQL> EXEC sys.dbms system.set int param in session(9, 19, 'sort_area_size', 1048576);

*设置会话参数(BOOL型)
DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION(
sid IN NUMBER, 
serial# IN NUMBER,
parnam IN VARCHAR2, --V$PARAMETER.NAME
bval IN BOOLEAN);

用法:
SQL> EXEC dbms system.set bool param in session(12, 16, 'timed_statistics', TRUE);

*设定特定事件,或发生特定错误时dump特定内容(仅限于本会话)
DBMS_SYSTEM.SET EV(
si IN BINARY INTEGER,  --V$SESSION.SID
se IN BINARY INTEGER,  --V$SESSION.SERIAL#
ev IN BINARY INTEGER,  --Event number between 10000 and 10999 for numeric events
le IN BINARY INTEGER,  --Level of the event; 0=disable event
nm IN VARCHAR2);  --Name, e.g. for taking diagnostic dumps,ORADEBUG DUMPLIST给出所有名称

NOTE:
If nm is not NULL and ev is an ORACLE error
number outside of the range 10000 to 10999, the dump named by nm is
taken when the session throws the error specified. If ev=65535 and nm
is not NULL, an immediate dump of the type specified by nm is taken.
This is equivalent to ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME
event_name LEVEL level', where event_name is the name of the dump
to take, for example SYSTEMSTATE, PROCESSSTATE, or ERRORSTACK, and
level is the event level. Named dumps are only possible within the
session of the caller, that is, they cannot be taken in a foreign session.

用法1:
启动SQL跟踪
EXECUTE dbms system.set ev(140, 862, 10046, 8, '')

用法2:
获取命名dump
SQL> VARIABLE sid NUMBER
SQL> VARIABLE serial NUMBER
SQL> BEGIN
SELECT sid, serial# INTO :sid, :serial
FROM v$session
WHERE sid=(SELECT sid FROM v$mystat WHERE rownum=1);
sys.dbms_system.set ev(:sid, :serial, 65535, 3, 'errorstack');
END;
/

等效于:
SQL> ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME ERRORSTACK LEVEL 3';

*启动/关闭会话SQL跟踪(LEVEL=1)
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(
sid IN NUMBER,
serial# IN NUMBER,
sql trace IN BOOLEAN);

*人工设置特定事件,用于调试跟踪文件分析工具：
DBMS_SYSTEM.WAIT_FOR_EVENT(
event IN VARCHAR2,  --V$EVENT_NAME.NAME
extended id IN BINARY INTEGER,
timeout IN BINARY INTEGER);

相关：
*oradebug
*alter session set events '';
*dbms_support.*
*dbms_monitor.session_trace_enable

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-716794/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-716794/