Learn how to a build a cloud-first strategyRegister Now


How to track the status of plsql

Posted on 2012-09-17
Medium Priority
Last Modified: 2012-10-02
I am updating table in a loop reading the records into a cursor. I can track the progress of this program using DBMS_APPLICATION_INFO. I can find out how much time is getting elapsed to insert every 10000 records.

Now I am modifying the update to use bulk update and I am not able to use this package to effectively track the progress..

I want to know how time is getting elapsed to insert every 10000 records..Is there a way to do this..

Question by:gs79
  • 2
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38408369
I believe for BULK update it is not possible because the whole is treated like a single statement right, so until that gets completed we will not come to know.
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 400 total points
ID: 38408381
But you can try to check the rows_processed in v$sqlarea. query can be executed for V$session & v$sqlarea as shown below:

SELECT   s.username, s.sid, s.serial#, s.audsid,
         TO_CHAR(s.logon_time, 'DD/MM/YYYY HH24:MI:SS') "LOGON TIME",
         s.status, sa.rows_processed, sa.sql_text "SQLTEXT",  s.program
FROM     v$sqlarea sa, v$session s
WHERE    sa.hash_value(+) = s.sql_hash_value
AND      s.username ='SCOTT' -- put the actual user id from which bulk update is running
AND      s.status = 'ACTIVE'
AND      s.AUDSID <> USERENV('SESSIONID') -- ignore this session
ORDER BY s.logon_time DESC
LVL 27

Accepted Solution

sujith80 earned 1600 total points
ID: 38408402
Are you referring to bulk update as in PL/SQL FORALL?

If yes, why are you not able to use DBMS_APPLICATION_INFO? You will still be doing an update in batches of 10000 and after each iteration you might call a routine from DBMS_APPLICATION_INFO to record the timing in the session. Say, DBMS_APPLICATION_INFO.SET_ACTION and you might construct a string with the timing info.

snippet -

DBMS_APPLICATION_INFO.SET_ACTION('Starting update '||to_char(systimestamp, 'YYYYMMDD HH24:MI:SS.FF'));
<bulk update here>
DBMS_APPLICATION_INFO.SET_ACTION('Completed update '||to_char(systimestamp, 'YYYYMMDD HH24:MI:SS.FF'));

Alternatively - If you have a log table you might record the timings in there as well.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses
Course of the Month20 days, 15 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question