You Asked

I've been tasked with migrating a very large warehouse database (9TB) from hardware in one data center to new hardware in a different data center. For various reasons, the method I've selected for the migration is datapump. I'm breaking up the datapump work into groups of schemas to allow for further parallelism. There are over 2500 schemas to be migrated. For some of the larger schemas, I'm breaking it down further, and performing their migrations table-by-table.

I've already tested the method by migrating development and test versions of the database and it works quite well (except for those pesky tables which include lobs), but those databases were MUCH smaller than the production database migration which is looming on the near horizon.

So, my question is whether there is a limit to the number of schemas (or other objects like tables) which I can include in one group and list in one par file. The last thing I want to happen at the start of the migration is to have it fail because I've exceeded some limit (like too many values for an array or something like that).

My guess is that there is some limit, but it's probably much higher than the number of schemas or tables I'll include in any given parfile. Still, this is one of those things for which it's impossible to find documentation. I would very much like to confirm that I won't exceed any such limits. It looks like the maximum number I'll include in a list is a bit over 2400 (tables).

Thanks in advance for any reassurance you can provide.

and Connor said...

You can specify multiple occurrences of a parameter name in a parfile, eg

directory=TEMP 
dumpfile=EMP_DEPT.dmp 
logfile=EMP_DEPT.log
tables=EMP   <======
tables=DEPT  <======

which works just like as if it was a concatenated list

C:\>expdp userid=scott/tiger@db18_pdb1 parfile=c:\temp\dp.par

Export: Release 18.0.0.0.0 - Production on Fri Sep 27 12:59:51 2019
Version 18.6.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  userid=scott/********@db18_pdb1 parfile=c:\temp\dp.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."EMP"                               8.781 KB      14 rows
. . exported "SCOTT"."DEPT"                              6.031 KB       4 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  C:\TEMP\EMP_DEPT.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Fri Sep 27 13:00:06 2019 elapsed 0 00:00:13

I'm not aware of any limit when parameters are used in this sense. I tried this :-)

SQL> begin
  2  for i in 1 .. 10000 loop
  3    execute immediate 'create table xx'||i||'( x int)';
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

with the following parfile

directory=TEMP 
dumpfile=lots.dmp 
logfile=lots.log
tables=xx1
tables=xx2
tables=xx3
tables=xx4
tables=xx5
...
...
...
tables=xx9998
tables=xx9999
tables=xx10000

and it ran just fine :-)

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Dan Grotjan, September 27, 2019 - 1:20 pm UTC

Thanks for the feedback. I'm using other SQL and shell scripts to generate the parfiles which contain long lists, and I'm using slightly different syntax.

Instead of using (e.g.);

tables=owner.table_1
tables=owner.table_2
tables=owner.table_3
....
tables=owner.table_n

I'm producing lists like this:

tables=('owner.table_1',
'owner.table_2',
'owner.table_3'
...
'owner.table_n')

I'm assuming that the datapump programs load a list of objects into an array (or some other structure) for processing, and my fear is that there might be a hard limit on the number of elements that can be placed into whatever structure is used.

I suppose I can test that by producing a parfile with a list of 25,000 objects that don't actually exist, and then run it and let them all error off, just to confirm that I'm not going to hit any such hard limits.

I just thought that you guys might have access to documentation that would specify any limits that datapump can't exceed.

Thanks again, and also, thanks for all the work you guys do. AskTom is very valuable resource for many people.

October 01, 2019 - 1:02 am UTC

I'm suggesting that you do NOT use

tables=(xxx,yyy,...)

Why Not?

A reader, October 10, 2019 - 8:09 pm UTC

Connor,

In your last update you suggest that I not use the:

tables=('xxxx',xxxx','xxxx','xxxx') syntax.

Why is that? Is there something inherently better about the following syntax?:

tables='xxxx'
tables='xxxx'
tables='xxxx'

Thanks in advance.

October 21, 2019 - 11:38 am UTC

I had problems with the *length* of a string when using the former version. In my case, it was listing files in a transportable tablespace, but I imagine there are limited for all parameters.

Also, the latter is typically easier to generate in SQL

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Why Not?

More to Explore

Utilities

Is there a maximum number of schemas that can be included in a datapump par file?

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Why Not?

More to Explore

Utilities