How to efficiently export and import an Oracle table?

anat0lius 0
Viewed 79

What is the best way to do this so that we waste the least time possible in both exporting and importing?

Taking into account that we are talking about a huge table with data from more than a decade.

What I've been planning so far:

directory=dumps
dumpfile=foo.dmp
parallel=8
logfile=foo_exp.log
tables=FOO
query=FOO:"WHERE TSP <= sysdate"
content=DATA_ONLY

The import part:

directory=dumps
dumpfile=foo.dmp
parallel=8
logfile=foo_imp.log
remap_table=FOO:FOO_REPARTITIONED
table_exists_action=REPLACE

Both scripts are going to be run like this:

nohup expdp USER/PWD@sid parfile=export.par &
nohup impdp USER/PWD@sid parfile=import.par &

Is the parallel parameter going to work as expected? Do I need to take anything else into account?

oracle
1 Answers

You need to consider some things

The parallel parameter from Datapump will not work unless you specify multiple dump files using the option %U. So in your case:

 directory=dumps
 dumpfile=foo_%U.dmp
 parallel=8
 logfile=foo_exp.log
 tables=FOO
 query=FOO:"WHERE TSP <= sysdate"
 content=DATA_ONLY

From the documentation

The value that you specify for integer should be less than, or equal to, the number of files in the dump file set (or you should specify either the %U or %L substitution variables in the dump file specifications).

Also, take in consideration the following restrictions':

  1. This parameter is valid only in the Enterprise Edition of Oracle Database 11g or later.

  2. To export a table or table partition in parallel (using parallel query, or PQ, worker processes), you must have the DATAPUMP_EXP_FULL_DATABASE role.

  3. Transportable tablespace metadata cannot be exported in parallel.

  4. Metadata cannot be exported in parallel when the NETWORK_LINK parameter is also used. The following objects cannot be exported in parallel: TRIGGER, VIEW OBJECT_GRANT, SEQUENCE, CONSTRAINT REF_CONSTRAINT.

So in your case set the parameter to a value adequate for the Hardware you have available in your server.

Update

Sorry for taking so much time to answer, but I was kind of busy. You were mentioning issues during the import. Well, if the structure of the tables is not the same ( for example, the partition key ) that might have an effect in the import operation. Normally in this case, I would suggest to be smart and speed up the import by splitting the operation in two steps:

First Step - Import Datapump into normal table

directory=dumps
dumpfile=foo_%U.dmp
parallel=8
logfile=foo_imp.log
remap_table=FOO:TMP_FOO
table_exists_action=TRUNCATE
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
ACCESS_METHOD=DIRECT_PATH
content=DATA_ONLY

Be sure to have the table TMP_FOO created before starting the operation. The first step is to import the datapump file ( only data ) into a non partitoned table using direct path and without logging.

Second Step - Direct Path Insert from TMP_FOO into your final table

alter session enable parallel dml ;
alter session force parallel query;
insert /*+append parallel(a,8) */ into your_partitioned_table a 
select /*+parallel(b,8) */ * from tmp_foo b ;
commit;

I think this would make the time go down.

Roberto Hernandez0
Related