Oracle 10g中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(
sid       NUMBER,
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); 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 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;