| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: check how many rows processed of a sql statement
A copy of this was sent to dshi_at_magpage.com (David Shi)
(if that email address didn't require changing)
On 6 Jul 1998 23:45:41 GMT, you wrote:
>Thanks Tom, I was actually wondering whether it is possible to check before a
>long-running sql statement get finished, that how many rows has been processed,
>which gives me an idea about how far is the job. Is this possible?
>
if you do an insert/update/delete/select, it will not return until it is complete. So, for a single insert/update/delete/select statement, not really (but then again, yes from another session for insert/update/delete statements, show you that in a minute).
On the other hand, if you are procedurally processing the statment in a pl/sql block, yes, you can let another session see how far it has gotten....
So, lets say you wanted to be able to track the process of a long running update/insert/delete statement. You need 2 sessions for this. One to run the DML and the other can watch it.
Consider a table like:
create table x ( x int );
create or replace package x_pkg
as
g_cnt number;
end;
/
create or replace trigger x_biud
before insert or update or delete on X
begin
x_pkg.g_cnt := 0;
if ( inserting ) then
dbms_application_info.set_module( 'X', 'Inserting' ); elsif (updating ) then
dbms_application_info.set_module( 'X', 'Updating' ); else
dbms_application_info.set_module( 'X', 'Deleting' );
end if;
end;
/
create or replace trigger x_aiud_fer
after insert or update or delete on X
for each row
begin
x_pkg.g_cnt := x_pkg.g_cnt + 1;
if ( mod( x_pkg.g_cnt, 100 ) = 0 ) then
dbms_application_info.set_client_info( to_char( x_pkg.g_cnt ) );
end if;
end;
/
create or replace trigger x_aiud
after insert or update or delete on X
begin
dbms_application_info.set_module( 'X', 'Done' );
dbms_application_info.set_client_info( to_char( x_pkg.g_cnt ) );
end;
/
Then, in another session you can query v$session with:
select usename, module, action, client_info from v$session;
to watch the insert/update/delete progress...
Of course, you can use the same technique for long running pl/sql routines as well, putting calls to dbms_application_info at the head and tail of routines for example to watch them run...
>David
>
>On Mon, 06 Jul 1998 14:09:16 GMT, tkyte_at_us.oracle.com (Thomas Kyte) wrote:
>
>>A copy of this was sent to dshi_at_magpage.com (David Shi)
>>(if that email address didn't require changing)
>>On 6 Jul 1998 01:07:27 GMT, you wrote:
>>
>>>Is there a way to check how many rows processed of a sql statement? Thanks.
>>>
>>>David
>>
>>depends on the language.
>>
>>In C and Pro*C sqlca.sqlerrd[2] has it.
>>
>>In PL/SQL with 'static' sql, sql%rowcount after an insert/update/delete has it.
>>
>>In PL/SQL with 'dynamic' sql, dbms_sql.execute returns it as a value...
>>
>>
>>
>>Thomas Kyte
>>tkyte_at_us.oracle.com
>>Oracle Government
>>Herndon VA
>>
>>http://govt.us.oracle.com/ -- downloadable utilities
>>
>>----------------------------------------------------------------------------
>>Opinions are mine and do not necessarily reflect those of Oracle Corporation
>>
>>Anti-Anti Spam Msg: if you want an answer emailed to you,
>>you have to make it easy to get email to you. Any bounced
>>email will be treated the same way i treat SPAM-- I delete it.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Jul 07 1998 - 12:01:50 CDT
 |
 |