3 Datapump Parameters That Will Change Your Life

Did I get your attention with the title? Well maybe these parameters are not really life changing, but at least very helpful! When I learned about them, my reaction was really? How come I didn’t know about these earlier…as they are very helpful! Actually one of the parameters is one that I use a lot, however not in this fashion as I will show you.

Let me show you the 3 parameters:

1) REMAP_TABLESPACE

Of course we know what this parameter does. It is used on the import to remap the source tablespace to the target tablespace. Usually, I use it this way:

REMAP_TABLESPACE=HRPRD_DATA1:HRTST_DATA, HRPRD_DATA2:HRTST_DATA

In the example above, I am remaping 2 tablespaces to the same target tablespace. This is pretty easy and fast to write, but when you have 10 tablespaces that you want to remap to only one, it’s becoming more inconvenient.

There is a better way that I discovered! The above remap, can be simplified to:

REMAP_TABLESPACE=%:HRTST_DATA

This means that I remap all the source tablespaces to HRTST_DATA. This is amazing, all in one line!

2) LOGTIME

This parameter can be use for export or import datapump, and specifies that the messages that are displayed during the export or import be timestamped. The values for this parameter could be NONE (the default), STATUS, LOGFILE or ALL. I would use ALL for LOGTIME. If you are using older versions of Oracle, ie 11g, this parameter will not be available for you, as it was introduced with 12c.

If you set LOGTIME=ALL, the output will be similar to:

13-DEC-21 19:37:09.920: . . imported "HR_COPY"."EMPLOYEES"                       17.08 KB     107 rows
13-DEC-21 19:37:10.154: . . imported "HR_COPY"."EMPLOYEES_COPY"                  17.09 KB     107 rows
13-DEC-21 19:37:10.404: . . imported "HR_COPY"."EMPLOYEES_COPY1"                 17.09 KB     107 rows
13-DEC-21 19:37:10.716: . . imported "HR_COPY"."LOCATIONS"                       8.437 KB      23 rows

You can see the timestamp at the left side, which is very useful!

3) METRICS

This parameter can be used to get additional information on the export or import. The values that you can set are YES or NO (default). The extra information you will receive is the number of objects and elapsed time during the import or export.

If you set METRICS=YES, the output will be similar to:

W-1 . . imported "HR_COPY"."EMPLOYEES"                       17.08 KB     107 rows in 0 seconds using direct_path
W-1 . . imported "HR_COPY"."EMPLOYEES_COPY"                  17.09 KB     107 rows in 1 seconds using direct_path
W-1 . . imported "HR_COPY"."EMPLOYEES_COPY1"                 17.09 KB     107 rows in 0 seconds using direct_path
W-1 . . imported "HR_COPY"."LOCATIONS"                       8.437 KB      23 rows in 1 seconds using direct_path
...
W-1 Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
W-1      Completed 2 TRIGGER objects in 1 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
W-1      Completed 15 TABLE_STATISTICS objects in 0 seconds

On the next datapump operation, give it a try to these 3 parameters and let me know what you think! Are they useful to you? Have you used them before?

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!

If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get the December Special, only $13.99 CAD !