dbms

SQL> exec dbms_application_info.set_module('module_wang','action_2020'); PL/SQL procedure successfully completed. SQL> select sys_context('userenv','module') from dual; SYS_CONTEXT('USERENV','MODULE') -------------------------------------------------------------------------------- module_wang

http://blog.itpub.net/26736162/viewspace-2135626/

DBMS_APPLICATION_INFO是一个非常有用的程序包，他提供了通过V$SESSION跟踪脚本运行情况的能力，该包允许你在v$session中的如下三列中填值： CLIENT_INFO,MODULE,ACTION，该包不仅提供了设置这些列值的过程，还提供了 返回这些列值的过程，在CLIENT_INFO列中适合存放允许你的程序的客户端信息， MODULE列适合存放你的主程序名，如包的名称，ACTION列适合存放你的程序包中 的过程名，现在我们先简单了解一下DBMS_APPLICATION_INFO的和V$session相关 的函数：

dbms_application_info.set_client_info：允许你向v$session中写入你的客户端的信息dbm{s}_{a}pplicatio{n}_{i}nfo.se{t}_{m}odule：允许你向v$session中写入你的主程序（如包） 和你的过程的名称 dbms_application_info.read_client_info：允许你从v$session中读取客户端的信息dbm{s}_{a}pplicatio{n}_{i}nfo.rea{d}_{m}odule：允许你从v$session中读取主程序（如包） 和你的过程的名称

SYS@orclasm > desc dbms_application_info PROCEDURE READ_CLIENT_INFO Argument Name Type In/Out Default?

---

CLIENT_INFO VARCHAR2 OUT PROCEDURE READ_MODULE Argument Name Type In/Out Default?

---

MODULE_NAME VARCHAR2 OUT ACTION_NAME VARCHAR2 OUT PROCEDURE SET_ACTION Argument Name Type In/Out Default?

---

ACTION_NAME VARCHAR2 IN PROCEDURE SET_CLIENT_INFO Argument Name Type In/Out Default?

---

CLIENT_INFO VARCHAR2 IN PROCEDURE SET_MODULE Argument Name Type In/Out Default?

---

MODULE_NAME VARCHAR2 IN ACTION_NAME VARCHAR2 IN PROCEDURE SET_SESSION_LONGOPS Argument Name Type In/Out Default?

---

RINDEX BINARY_INTEGER IN/OUT SLNO BINARY_INTEGER IN/OUT OP_NAME VARCHAR2 IN DEFAULT TARGET BINARY_INTEGER IN DEFAULT CONTEXT BINARY_INTEGER IN DEFAULT SOFAR NUMBER IN DEFAULT TOTALWORK NUMBER IN DEFAULT TARGET_DESC VARCHAR2 IN DEFAULT UNITS VARCHAR2 IN DEFAULT

SYS@orclasm >

看一个简单的例子：

SQL> set serveroutput on SQL> SQL> DECLARE 2 l_clinent VARCHAR2(100); 3 l_mod_name VARCHAR2(100); 4 l_act_name VARCHAR2(100); 5 BEGIN 6 dbms_application_info.set_client_info(‘my client’); 7 dbms_application_info.read_client_info(l_clinent); 8 dbms_output.put_line(‘client=’||l_clinent); 9 dbms_application_info.set_module(‘my mod’,‘inserting’); 10 FOR i IN 1…100 11 LOOP 12 execute immediate ‘INSERT INTO pp_test(c1) VALUES(:X)’ USING i; 13 END LOOP; 14 dbms_application_info.read_module(l_mod_name,l_act_name); 15 dbms_output.put_line(‘mod_name=’||l_mod_name); 16 dbms_output.put_line(‘act_name=’||l_act_name); 17 END; 18 19 /

client=my client mod_name=my mod act_name=inserting

PL/SQL procedure successfully completed

SQL> commit;

Commit complete

SQL> select sid from v$mystat where rownum=1;

SID

1065

SQL> SELECT sid,serial#,client_info,MODULE,action FROM v$session WHERE sid=1065;

SID SERIAL# CLIENT_INFO MODULE ACTION

---

1065 18968 my client my mod inserting

再看一个更有用的例子：

1.首先我们构建一个有大量数据的表：

SQL> DELETE FROM pp_test;

1320 rows deleted

SQL> insert into pp_test(c1) select object_name from all_objects;

116441 rows inserted

SQL> insert into pp_test(c1) select c1 from pp_test;

116441 rows inserted

SQL> insert into pp_test(c1) select c1 from pp_test;

232882 rows inserted

SQL> insert into pp_test(c1) select c1 from pp_test;

465764 rows inserted

SQL> commit;

Commit complete

2.我们现在需要更新PP_TEST表的C1列，在脚本执行过程中我们需要知道 已经处理的行数和已经花费的时间，执行结束后，我们需要知道处理的 总的行数和执行的总时间，使用如下代码：

–在session1中执行： DECLARE CURSOR cur_test IS SELECT c1,ROWID FROM pp_test; l_new_c1 VARCHAR2(2000); l_count_num PLS_INTEGER := 0; l_start_time_num PLS_INTEGER; BEGIN l_start_time_num := DBMS_UTILITY.GET_TIME; FOR cur_test_rec IN cur_test LOOP l_count_num := l_count_num + 1; l_new_c1 := cur_test_rec.c1||’_NEW’; UPDATE pp_test SET c1 = l_new_c1 WHERE rowid = cur_test_rec.ROWID; IF MOD(l_count_num, 1000) = 0 THEN DBMS_APPLICATION_INFO.SET_MODULE('Records Processed: ’ || l_count_num, ‘Elapsed: ’ || (DBMS_UTILITY.GET_TIME - l_start_time_num)/100 || ’ sec’); --每更新1000行，记录一次执行时间 END IF; END LOOP; COMMIT; DBMS_APPLICATION_INFO.SET_MODULE('Records Processed: ’ || l_count_num, ‘Elapsed: ’ || (DBMS_UTILITY.GET_TIME - l_start_time_num)/100 || ’ sec’); --更新结束，记录总的执行时间 END;

执行过程中我们可以查询v$session,如下所示：

SQL> SELECT sid,serial#,client_info,MODULE,action FROM v$session WHERE sid=307;

SID SERIAL# CLIENT_INFO MODULE ACTION

---

307 36536 Records Processed: 360000 Elapsed: 18.69 sec

SQL> SELECT sid,serial#,client_info,MODULE,action FROM v$session WHERE sid=307;

SID SERIAL# CLIENT_INFO MODULE ACTION

---

307 36536 Records Processed: 626000 Elapsed: 32.99 sec

SQL> SELECT sid,serial#,client_info,MODULE,action FROM v$session WHERE sid=307;

SID SERIAL# CLIENT_INFO MODULE ACTION

---

307 36536 Records Processed: 837000 Elapsed: 44.56 sec

执行结束，我们再次查询v$session:

SQL> SELECT sid,serial#,client_info,MODULE,action FROM v$session WHERE sid=307;

SID SERIAL# CLIENT_INFO MODULE ACTION

---

307 36536 Records Processed: 931528 Elapsed: 49.86 sec

SQL>

Application developers can use the DBMS_APPLICATION_INFO package with Oracle Trace and the SQL trace facility to record names of executing modules or transactions in the database for later use when tracking the performance of various modules and debugging.

When an application registers with the database, its name and actions are recorded in the V$SESSIONandV$SQLAREA views.