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