Oracle 10g中dbms_system包的使用方法
来源:互联网 发布:dropbox 类似软件 编辑:程序博客网 时间:2024/05/20 11:48
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
GeneralSource{ORACLE_HOME}/rdbms/admin/prvtutil.plbFirst Available7.3.4ConstantsNameValue
trace_file
1alert_file
2?
3DependenciesSELECT name FROM dba_dependencies WHERE referenced_name = ‘DBMS_SYSTEM’UNION
SELECT referenced_name FROM dba_dependencies WHERE name = ‘DBMS_SYSTEM’;Security ModelExecute 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’;
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;
/
SELECT maxiortm, maxiowtm FROM gv_$filestat;
exec dbms_system.kcfrms;
SELECT max_wait FROM gv_$session_event;
SELECT maxiortm, maxiowtm FROM gv_$filestat;
KSDDDTPrints the date stamp to the target file (alert log and/or trace file)dbms_system.ksdddt;exec dbms_system.ksdddt; KSDFLSFlushes any pending output to the target alert log or trace filedbms_system.ksdfls;exec dbms_system.ksdfls; KSDINDDoes 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’); KSDWRTPrints 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, ‘Test Message’);
exec dbms_system.ksdwrt(3, ‘– End Message –’); READ_EVGet the level for events set in the current sessiondbms_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
set serveroutput on
DECLARE
lev BINARY_INTEGER;
BEGIN
dbms_system.read_ev(10046, lev);
dbms_output.put_line(lev);
END;
/
Overload 1
dbms_system.remove_parameter_value(parname IN VARCHAR2,
value IN VARCHAR2,
scope IN VARCHAR2,
sid IN VARCHAR2);TBDOverload 2dbms_system.remove_parameter_value(
parname IN VARCHAR2,
position IN BINARY_INTEGER,
scope IN VARCHAR2,
sid IN VARCHAR2);TBD SET_BOOL_PARAM_IN_SESSIONSets boolean-type init.ora parameters in any sessiondbms_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_EVSet event trace leveldbms_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);
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_SESSIONTurn tracing on or off in any sessiondbms_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_EVENTPuts the current session into a wait state for any named wait eventdbms_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;
- Oracle 10g中dbms_system包的使用方法
- DBMS_SYSTEM包的使用
- oracle DBMS_SYSTEM
- Oracle中包(PACKAGE)的使用方法
- 关于oracle 10g的LogMiner使用方法
- oracle 10g中的SGA_MAX_SIZE与SGA_TARGET的使用方法
- ORACLE 11G 触发器调试记录Error: PLS-00201: identifier'SYS.DBMS_SYSTEM' must be declared
- Using Oracle DBMS_SYSTEM.SET_EV and Oracle DBMS_SYSTEM.READ_EV
- dbms_system
- dbms_system
- Oracle SQL 跟踪 --- dbms_system.set_sql_trace_in_session
- Oracle SQL 跟踪 --- dbms_system.set_sql_trace_in_session
- ORACLE 10g EXPDP,IMPDP使用方法
- Oracle 10g中LogMiner的使用
- 利用dbms_system包SET_SQL_TRACE_IN_SESSION开启sql跟踪
- 利用dbms_system包SET_SQL_TRACE_IN_SESSION开启sql跟踪
- Oracle 10g dbms_rowid 包源码
- Oracle 10g dbms_rowid 包源码
- tomcat学习(2)
- Mysql联合查询UNION和UNION ALL的使用介绍
- 有关c语言编译的小问题
- AndroidStudio与Eclipse快捷键
- Android doc |Getting Started|部分 --Building a Flexible UI(部分翻译)
- Oracle 10g中dbms_system包的使用方法
- CSS3一览(3) Canvas画布(2)
- POJO
- 十个由浅入深的Angular.js面试问题
- ASP.net 学习路线
- BSBacktraceLogger 堆栈抓取
- 两款优秀的复现测试步骤gif利器
- IO基础--OutputStream·FileWriter的基本用法
- androidstudio使用Svn忽略文件