Hi Folks , I need help in monitoring progress of Merge and delete statement which I am executing inside a oracle package .I was wondering is there way to do that .I am not allowed to modify package .
I have tried v$session_longops for long running sql but no luck .Any idea or suggestion are welcome.
Below is the example of the statement .
--------Wanted to monitor progress of Delete in target table ---------
DELETE FROM target_table
WHERE some condition AND
NOT EXISTS (SELECT 1
FROM source_table
WHERE some condition);
-------Wanted to monitor progress while execution of merge is in progress-----
Progress of update how many records updated and how many left.
Progress of insert how many insert completed and how many left.
MERGE INTO target_table
USING source_table
ON (join condition)
WHEN MATCHED THEN
UPDATE SET x
WHERE some condition
WHEN NOT MATCHED THEN
INSERT (
some operation)
VALUES (some values );