Calculate import time of DUMP

Asked

Viewed 819 times

5

I would like to know, if possible, how to approximate the import time of a Dump.

It could be something considering the following items:

  • GB DUMP size.
  • Number of Tables.
  • Number of Records and(or) rows/columns per table.
  • Number of Objects such as Triggers, Functions, Procedures, etc.
  • Some way to calculate computer processing efficiency?

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
NOARCHIVELOG

The computer I use to perform IMP:

Windows 7 Professional 64bits
4gb RAM
i3 3.3GHz

OBS.: Using the command IMP and/or IMPDB.

oracle oracle11c
  • 1

    I don’t know if it helps you, but look here: http://www.dba-oracle.com/t_monitor_data_pump_import_job.htm

  • 1

    @Diego, on the link resolves with accurate IMPDP with IMP too, but thanks a lot!!!

  • Question: Are you planning some bank migration or just want to estimate a restore time for a possible crash? Or none of these? What is your bank version? Standard or EE? is with active archivelog? S.O?

  • 1

    @Diego, I changed the question with the answers.

  • If I use a 1gb dump as a comparison effect and spend 2h to import then make three rule to figure out the time of a 5 GB dump giving 10 hours of import? kkk

  • 1

    @Diego, post your reply with Datapump that I mark as answered.

  • I think this will help you: Data PUMP Import

  • @Diego puts a complete answer, with explanation of the link (and why the content of the link answers) to the question I will also vote +1

Show 3 more comments

1 answer

2


It is possible to monitor time using this query:

col table_name format a30

select substr(sql_text, instr(sql_text,'"')+1, 
               instr(sql_text,'"', 1, 2)-instr(sql_text,'"')-1) 
          table_name, 
       rows_processed, 
       round((sysdate
              - to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))
             *24*60, 1) minutes, 
       trunc(rows_processed / 
                ((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))
             *24*60)) rows_per_min 
from 
   v$sqlarea 
where 
  upper(sql_text) like 'INSERT % INTO "%' 
  and 
  command_type = 2 
  and 
  open_versions > 0;
select 
   sid, 
   serial#
from 
   v$session s, 
   dba_datapump_sessions d
where 
   s.saddr = d.saddr;

select 
   sid, 
   serial#, 
   sofar, 
   totalwork
from 
   v$session_longops;

Sources: Here and here

  • This answer only serves for IMPDB, ie Datapump. Using the IMP command would not be possible

Browser other questions tagged oracle oracle11c

You are not signed in. Login or sign up in order to post.