How to track the status of plsql

Posted on 2012-09-17
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
    LVL 28

    Expert Comment

    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

    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

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now