这是我常用的两种PL/SQL监控运行状况的方法：
1. 使用dbms_application_info.SET_CLIENT_INFO
举例如下:
| 以下是代码片段： declare cursor cr is select rowid from test; delete_count number; total_count number; begin delete_count :=0; total_count :=0; for i in cr loop delete from test where rowid=i.rowid; delete_count :=delete_count+1; total_count :=total_count+1; if (delete_count>100) then dbms_application_info.SET_CLIENT_INFO(’ So far ‘||total_count||’ rows has been deleted’); delete_count :=0; commit; end if; end loop; end; / |
另开一session, select client_info from v$session where client_info like ‘So far%’;
注意info的长度有限制，超过64字符会被截断
2. 使用dbms_system.ksdwrt， 这个可以写到300个字符
KSDWRT Procedure
This procedure prints the message to the target file (alert log and/or trace file).
Syntax
DBMS_SYSTEM.KSDWRT (
dest IN BINARY_INTEGER,
tst IN VARCHAR2);
Parameters:
dest Destination is indicated by the following values:
1 - Write to trace file.
2 - Write to alertlog.
3 - Write to both.
tst Message (not tested for max length, but output with 300 chars was successful)
举例如下:
| 以下是代码片段： declare cursor cr is select rowid from test; delete_count number; total_count number; begin delete_count :=0; total_count :=0; for i in cr loop delete from test where rowid=i.rowid; delete_count :=delete_count+1; total_count :=total_count+1; if (delete_count>100) then dbms_system.ksdwrt (1,’ So far ‘||total_count||’ rows has been deleted’); delete_count :=0; commit; end if; end loop; end; / |
然后开一session, tail -30f xxx.trc