2 Referenced Niall's comment, and added caveats.
source | link

There is no direct method; you'll either have to parse through the logs (as mentioned in another answer), or use alternate methods to see what's happening in a long-running process.

Personally, I suggest using autonomous transactions to enable this feature -- not on the transaction itself, but as a logging mechanism letting you know what is going on. For example, you could have PROCEDURE LONG_ACTION call PROCEDURE WRITE_LOG_ENTRY (defined as an autonomous transaction) that would write a VARCHAR2 to another table. Autonomous transactions do NOT interfere with your current transaction (from a LOGICAL perspective; beware of potential impacts to performance) and so you can see what's going on via your logging entries regardless of a COMMIT or ROLLBACK in your current transaction. That said, you can'd do that with one massive DML statement; you'd have to use a loop.

Consider:

TABLE LOG_ENTRIES defined as
    activity_date  date,
    log_entry varchar2(2000)

TABLE BIG_JOB (definition doesn't really matter)

PROCEDURE WRITE_LOG_ENTRY
                        ( str VARCHAR2 )
IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO LOG_ENTRIES VALUES ( SYSDATE, str );
    COMMIT;
END;

PROCEDURE LONG_ACTION IS
    c NUMBER;
BEGIN
    FOR r IN ( SELECT * FROM BIG_JOB )
    LOOP
       c := c + 1;
       UPDATE BIG_JOB z
          SET fld = hairy_calculation
        WHERE z.rowid = r.rowid;
       IF MOD(c,500) = 0 THEN
           WRITE_LOG_ENTRY ( c || ' rows processed.' );
       END IF;
    END LOOP;
    COMMIT;
END;

Given the above, you'll get a log entry for every 500 rows processed regardless of success of the long action. If you need an exact duplicate of the data to see as it is working, I suggest making a duplicate table and calling an procedure that will duplicate the data (the procedure being an autonomous transaction). Then nuke the data after-the-fact. (No need for duplication.)

Further, if this is for a debugging purpose, I suggest removing or drastically reducing the need for such logging when things have been tested. And, as always, test, test, test on your own system to verify how things will work. (See the comment from Niall for a good example of how logging can drastically affect performance.)

(Finally, because I neglected to mention it before: beware autonomous transactions. Understand them fully before implementing, and don't use them "just because". They can be used in a million ways incorrectly (say, for example, to ATTEMPT to avoid a mutate error in a trigger), so it is always best to find alternatives, if possible. If you can't, then proceed with caution. Logging during long-running ops has always been one case where it is fairly safe (ignoring performance issues), but don't rush in to apply it to other uses without knowing the consequences.)

There is no direct method; you'll either have to parse through the logs (as mentioned in another answer), or use alternate methods to see what's happening in a long-running process.

Personally, I suggest using autonomous transactions to enable this feature -- not on the transaction itself, but as a logging mechanism letting you know what is going on. For example, you could have PROCEDURE LONG_ACTION call PROCEDURE WRITE_LOG_ENTRY (defined as an autonomous transaction) that would write a VARCHAR2 to another table. Autonomous transactions do NOT interfere with your current transaction and so you can see what's going on via your logging entries regardless of a COMMIT or ROLLBACK in your current transaction. That said, you can'd do that with one massive DML statement; you'd have to use a loop.

Consider:

TABLE LOG_ENTRIES defined as
    activity_date  date,
    log_entry varchar2(2000)

TABLE BIG_JOB (definition doesn't really matter)

PROCEDURE WRITE_LOG_ENTRY
                        ( str VARCHAR2 )
IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO LOG_ENTRIES VALUES ( SYSDATE, str );
    COMMIT;
END;

PROCEDURE LONG_ACTION IS
    c NUMBER;
BEGIN
    FOR r IN ( SELECT * FROM BIG_JOB )
    LOOP
       c := c + 1;
       UPDATE BIG_JOB z
          SET fld = hairy_calculation
        WHERE z.rowid = r.rowid;
       IF MOD(c,500) = 0 THEN
           WRITE_LOG_ENTRY ( c || ' rows processed.' );
       END IF;
    END LOOP;
    COMMIT;
END;

Given the above, you'll get a log entry for every 500 rows processed regardless of success of the long action. If you need an exact duplicate of the data to see as it is working, I suggest making a duplicate table and calling an procedure that will duplicate the data (the procedure being an autonomous transaction). Then nuke the data after-the-fact. (No need for duplication.)

There is no direct method; you'll either have to parse through the logs (as mentioned in another answer), or use alternate methods to see what's happening in a long-running process.

Personally, I suggest using autonomous transactions to enable this feature -- not on the transaction itself, but as a logging mechanism letting you know what is going on. For example, you could have PROCEDURE LONG_ACTION call PROCEDURE WRITE_LOG_ENTRY (defined as an autonomous transaction) that would write a VARCHAR2 to another table. Autonomous transactions do NOT interfere with your current transaction (from a LOGICAL perspective; beware of potential impacts to performance) and so you can see what's going on via your logging entries regardless of a COMMIT or ROLLBACK in your current transaction. That said, you can'd do that with one massive DML statement; you'd have to use a loop.

Consider:

TABLE LOG_ENTRIES defined as
    activity_date  date,
    log_entry varchar2(2000)

TABLE BIG_JOB (definition doesn't really matter)

PROCEDURE WRITE_LOG_ENTRY
                        ( str VARCHAR2 )
IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO LOG_ENTRIES VALUES ( SYSDATE, str );
    COMMIT;
END;

PROCEDURE LONG_ACTION IS
    c NUMBER;
BEGIN
    FOR r IN ( SELECT * FROM BIG_JOB )
    LOOP
       c := c + 1;
       UPDATE BIG_JOB z
          SET fld = hairy_calculation
        WHERE z.rowid = r.rowid;
       IF MOD(c,500) = 0 THEN
           WRITE_LOG_ENTRY ( c || ' rows processed.' );
       END IF;
    END LOOP;
    COMMIT;
END;

Given the above, you'll get a log entry for every 500 rows processed regardless of success of the long action. If you need an exact duplicate of the data to see as it is working, I suggest making a duplicate table and calling an procedure that will duplicate the data (the procedure being an autonomous transaction). Then nuke the data after-the-fact. (No need for duplication.)

Further, if this is for a debugging purpose, I suggest removing or drastically reducing the need for such logging when things have been tested. And, as always, test, test, test on your own system to verify how things will work. (See the comment from Niall for a good example of how logging can drastically affect performance.)

(Finally, because I neglected to mention it before: beware autonomous transactions. Understand them fully before implementing, and don't use them "just because". They can be used in a million ways incorrectly (say, for example, to ATTEMPT to avoid a mutate error in a trigger), so it is always best to find alternatives, if possible. If you can't, then proceed with caution. Logging during long-running ops has always been one case where it is fairly safe (ignoring performance issues), but don't rush in to apply it to other uses without knowing the consequences.)

1
source | link

There is no direct method; you'll either have to parse through the logs (as mentioned in another answer), or use alternate methods to see what's happening in a long-running process.

Personally, I suggest using autonomous transactions to enable this feature -- not on the transaction itself, but as a logging mechanism letting you know what is going on. For example, you could have PROCEDURE LONG_ACTION call PROCEDURE WRITE_LOG_ENTRY (defined as an autonomous transaction) that would write a VARCHAR2 to another table. Autonomous transactions do NOT interfere with your current transaction and so you can see what's going on via your logging entries regardless of a COMMIT or ROLLBACK in your current transaction. That said, you can'd do that with one massive DML statement; you'd have to use a loop.

Consider:

TABLE LOG_ENTRIES defined as
    activity_date  date,
    log_entry varchar2(2000)

TABLE BIG_JOB (definition doesn't really matter)

PROCEDURE WRITE_LOG_ENTRY
                        ( str VARCHAR2 )
IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO LOG_ENTRIES VALUES ( SYSDATE, str );
    COMMIT;
END;

PROCEDURE LONG_ACTION IS
    c NUMBER;
BEGIN
    FOR r IN ( SELECT * FROM BIG_JOB )
    LOOP
       c := c + 1;
       UPDATE BIG_JOB z
          SET fld = hairy_calculation
        WHERE z.rowid = r.rowid;
       IF MOD(c,500) = 0 THEN
           WRITE_LOG_ENTRY ( c || ' rows processed.' );
       END IF;
    END LOOP;
    COMMIT;
END;

Given the above, you'll get a log entry for every 500 rows processed regardless of success of the long action. If you need an exact duplicate of the data to see as it is working, I suggest making a duplicate table and calling an procedure that will duplicate the data (the procedure being an autonomous transaction). Then nuke the data after-the-fact. (No need for duplication.)