News Stay informed about the latest enterprise technology news and product updates.

Interpolate when an export/import/refresh will finish

Application development tip #1 from "30 Oracle tips in 30 minutes," brought to you by the IOUG.

The following is application development tip #1 from the "30 tips in 30 minutes" series, brought to you by the IOUG. Return to the main page for more tips on this topic.

When you've got a SQL process that takes a long time, you can project the time that the work will done. Depending on the type of work, you can take two statistic samples of the session. From these samples, you can determine the rate of work. Interpolating the rate against the work remaining, you can find the time that the job will be done. Session statistics you can use to measure progress are: 'table scan rows gotten', 'bytes received via SQL*Net from client', 'bytes received via SQL*Net from dblink', 'bytes sent via SQL*Net from client', 'bytes sent via SQL*Net from dblink' etc. Pick a statistic that relates to the work you are doing.

This script can be used to project the finish time of export of a table. In this script, the number of rows exported will be 2298410.

Rem Determine how much time until a long job is finished.
Rem  Take two measurements and interpolate the finish time.
Rem  Parameters:
Rem   Session id of the process to interpolate
Rem   Statistic to take a sample of total work to accomplish
Rem   Start statistic for the session ** must know this beforehand
Rem   Seconds to take the sample 
set verify off serveroutput on feedback off;
define vsid   = 10;
define vstat  = 'table scan rows gotten';
define vtotal = 2298410;
define vstart = 0;
define vseconds = 20;
alter session set nls_date_format = 'HH24:MI:SS MON-DD';
 CURSOR c_measure is
  SELECT value, sysdate
   FROM v$sesstat s, v$statname n
  WHERE s.statistic# = n.statistic#
    AND = '&vstat'
    AND s.sid = &vsid;
 t1 DATE;      -- First Sample Time

 t2 DATE;      -- Second Sample Time
 m1 NUMBER;    -- First Sample Measurement
 m2 NUMBER;    -- Second Sample Measurement
 v_rate NUMBER;     -- Rate of work per second
 v_finish INTEGER;  -- Remaining seconds til finished
  OPEN  c_measure;
  FETCH c_measure INTO m1, t1;
  CLOSE c_measure;
  DBMS_LOCK.SLEEP(&vseconds);  -- Wait between samples
  OPEN  c_measure;
  FETCH c_measure INTO m2, t2;
  CLOSE c_measure;
  IF (m2 - m1 > 10) THEN
   v_rate   := (m2 - m1) / ((t2-t1)*24*60*60);
   v_finish := (&vtotal - &vstart - m2) / v_rate;
   dbms_output.put_line('Sample time:    &vseconds');
   dbms_output.put_line('Remaining work: ' ||(&vtotal - &vstart - m2)||
                                         ' &vstat');
   dbms_output.put_line('Moving at:'||to_char(v_rate,'9,999,990.9')||
                                         ' per second');
   dbms_output.put_line('Finished at... '|| (t2+v_finish/(24*60*60)));
   dbms_output.put_line('Nothing is happening');

Get more tips in minutes! Return to the main page.

About the author: Kenny Smith has been working with Oracle technology on HP servers for over a decade. He specializes in Oracle database architecture, database administration and development. He has presented at numerous Oracle conferences on two continents. He has published many articles describing Oracle solutions and has co-authored "Oracle backup and recovery 101" from Oracle Press.

IOUG: Become a member of the IOUG to access the paper referenced here and a repository of technical content created for Oracle users by Oracle users.

Dig Deeper on Oracle database export, import and migration

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.