Creating an ASCII database backup with nhSaveDb, or restoring an ASCII backup with nhLoadDb, is a time consuming process that gives little indication of whether the process is running or in a hung state. Standard output to screen does not update until the process has completed or has failed, which can legitimately take several hours. Customers would like a way to determine progress on a backup or restore process.
SELECT sid, serial#, sofar, totalwork, round(sofar/totalwork*100,2) “% Complete”, dp.state
FROM gv$session_longops sl, gv$datapump_job dp
WHERE sl.opname = dp.job_name;
The table below shows an example of the output the query might display.
Output will display each process, how far that process has gone out of total work to be completed, a simple percentage value of progress, and the current state of the process.