4

I'm on a Windows machine with Oracle Client 11.1.0, and am going to import a database dump. I'm importing from the command line with the "imp" command.

Is there an option to time how long the import takes? I've searched around but haven't found anything for that yet.

Thanks in advance!

1
  • 6
    Are you asking "Is there a way to determine before I start the import approximately how long it will take?" Or are you asking "Is there a way to time how long it actually took to run the import?" Or are you asking something else? Sep 10, 2012 at 18:52

4 Answers 4

3

If you just want to time how long it takes, you could call it via a batch file like this:


echo %DATE% %TIME% >> time.log
imp parfile=./file.par etc etc
echo %DATE% %TIME% >> time.log

Once everything has run through, have a look at the time.log file and work out the difference between the 2 values.

0

Short answer: NO.

The import does several things. One is creating tables and inserting data in it. Next come the indexes, constraints, triggers and other objects. There is no such thing as a prediction as how long it would take. For the reading of data, if the indexes, triggers and constraints don't play a role, you can assume this is a linear process, where normally 1GB takes 10 times the time of reading 100MB. For the other stuff this does not work but for the creation of a specific index - for example - you could check v$session_longops to find the time_remaining for that index creation. You still don't know what is next. To find what is next, you could create an sql file that contains the ddl that is to be executed. This could help in giving an idea what is still left to do.

Don't forget to specify a large buffer for imp.

0

If you use DataPump impdp and expdp commands, you can include the parameter metrics=yes in the impdp command parameter file. The log file will then time-stamp each phase.

0

While metrics=yes is useful, I find using logtime=ALL to be more useful as it prints the actual time each event happened. With metrics=yes you are missing the context of each event in the overall time taken, so ideally you would use both together. See documentation here.

impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp SCHEMAS=hr LOGTIME=ALL METRICS=yes

Output is something like this:

Export: Release 12.1.0.2.0 - Production on Tue Feb 21 12:17:12 2023

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
21-FEB-23 12:17:52.733: Starting "HR"."SYS_EXPORT_SCHEMA_01":  hr/********@mydb directory=DPUMP_DIR1 dumpfile=expdat.dmp schemas=hr metrics=y logtime=ALL
21-FEB-23 12:18:55.853: W-1 Startup took 38 seconds
21-FEB-23 12:23:23.853: W-1 Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
21-FEB-23 12:23:33.869: W-1      Completed 277 PACKAGE_BODY objects in 12 seconds
21-FEB-23 12:23:39.884: W-1 Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
21-FEB-23 12:23:41.868: W-1      Completed 1541 INDEX_STATISTICS objects in 4 seconds
21-FEB-23 12:23:47.884: W-1 Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
21-FEB-23 12:23:49.868: W-1      Completed 54 INDEX_STATISTICS objects in 2 seconds
21-FEB-23 12:23:57.993: W-1 Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
21-FEB-23 12:23:59.993: W-1      Completed 592 TABLE_STATISTICS objects in 1 seconds

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.