Tips: PL/SQL中监控执行进度两种方法 -- Oracle -- IT技术博客大学习 -- 共学习共进步！

您现在的位置：首页 --> Oracle --> Tips: PL/SQL中监控执行进度两种方法

Tips: PL/SQL中监控执行进度两种方法

浏览:3120次出处信息

这是我常用的两种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

建议继续学习：

QQ技术交流群：445447336，欢迎加入！
扫一扫订阅我的微信号：IT技术博客大学习

<< 前一篇：尽量缩短oracle upgrade时间

后一篇：安装BBED >>

文章信息

作者：Eagle Fan 来源： eagle's home
标签：监控
发布时间：2009-10-21 09:05:06

建议继续学习

近3天十大热文