Using the split command for Oracle export files

[Morris Makuch]

Because Oracle does not support export files greater than 2 GB, I am trying to split up my export in 2000M chunks using pipes and the split command. But I would like to specify more meaningful output names instead of the default xaa, xab, xac from the split command. Does anyone know how to do this? I talked with HP Support and was told that it cannot be done because of limitations of the split command. But I see in the man pages for split that there is a name option there but I have been unable to get it to work. Thanks

[harry d brown jr]

Morris,

Did you try this:

whatever | split -l 5000 -a 6 - expfile

the -l (el) 5000 tells it to split on every 5000 lines

The -a 6 tells it to use a 6 digit suffix appended to "expfile" name, ie: expfile.000001, expfile.000002, ...

the dash "-" says sepect input from STDIN

and the "expfile" is the output file name PREFIX, ie expfile.000001


live free or die
harry

[harry d brown jr]

Morris,

Have you tried to export to a pipe and then dd it to a tape???


live free or die
harry

[Joseph C. Denman]

As Harry stated you could use a pipe. As well as dd to a tape, you could also gzip the file through the pipe.

...jcd...

[S.K. Chan]

The "name" actually refers to the the filename before the appended extension. For example :-

# split -l 500 myfile newfile

will get files as such "newfileaa , newfileab, nefileac .. and so on".

Take note , the limit of after processed files = 676 files

[Urbain Xavier]

Morris,

If you use the Oracle 8i, you can in the command exp specify more than one file in the paramater file.

e.g. exp user/password file=file1.exp,file2.exp,... full=y

Of course this functionality is only available on the version 8.1.x or later

Regards,

Xavier.

[Andreas D. Skjervold]

Hi

From 8.1.x onwards the 2GB limit shouldn't be an issue.

On 8.0.5 and 8.0.6 you have to apply an Oracle patch to fix this.

Check the following matrix:
If you have problems with files >2GB see the following matrix:
Limits apply to HPUX 10.20 and 11.0 except where noted otherwise.

Max Datafile Size
File Raw Async Export/ SQL*Loader
Release System Device I/O Import** (see **)
===========================================================================
8.1.7.x 64Gb 64Gb raw only >2Gb >2Gb
8.1.6.x 64Gb 64Gb raw only >2Gb >2Gb
8.0.6.x 64Gb 64Gb raw only See*1 See*2
8.0.5.x 64Gb 64Gb raw only See*1 <2Gb
8.0.4.x 64Gb 64Gb raw only SIL HPUX10.20 > 2Gb
HPUX-11 <2Gb
8.0.3.x <2Gb <2Gb raw only 2Gb 2Gb
7.3.4.x 64Gb 64Gb raw only SIL 2Gb
>=7.3.3.4.1 64Gb 64Gb raw only SIL 2Gb
7.3.2.3 <2Gb <2Gb raw only 2Gb 2Gb
7.1.6 <2Gb <2Gb raw only 2Gb 2Gb


SIL = System Imposed Limit
Always test on the actual system to ensure large files can be
read / written.

*1 = HPUX 10.20 requires a patch to create >2Gb export files:
8.0.5.0 to 8.0.5.2 inclusive - Get the patch for Bug:872947
8.0.6.0 to 8.0.6.1 inclusive - Get the patch for Bug:1330994
HPUX 11 can export >2Gb on these RDBMS releases.

*2 = HPUX 10.20 requires a patch for > 2Gb SQL Loader files:
8.0.6 - Get the patch for Bug:1344224
HPUX 11 can SQLLoader can read >2Gb files as standard.

** Exp/Imp/Loader
The above matrix indicates the maximum file size
which can be directly handled by exp/imp/loader.
However, it is possible to read/write files >2Gb
using the OS commands.

Andreas

[Andreas D. Skjervold]

Ooops, what gibberish...
Have attached the matrix instead.

Andreas

[Morris Makuch]

Thanks for the replies, but I am using Oracle7 and am trying to incorporate this in the following:

mknod exportpipe p
split -b2000m < exportpipe &
exp system/manager file=exportpipe full=y

So somehow in the split command, I am trying to specify meanigful file names instead of the default xaa, xab, xac, etc., that come out.

[Tom Dawson]

Morris,

I've taken your fifo and integrated it into what Harry suggested above...

split -l 5000 -a 6 - exportpipe &

HTH,
Tom

[Joseph C. Denman]

Something like this for the gzip example.

mknod orapipe p
/usr/contrib/bin/gzip < orapipe > oracle_export.gz &
exp username/password file=./orapipe full=y log=export.log
rm orapipe


Hope this helps


...jcd...

[harry d brown jr · Accepted solution]

Morris,

Sorry about the first posts, I was confusing csplit with split - I need a drink!


mknod exportpipe p
split -b2000m -a4 - expfiles_ < exportpipe &
exp system/manager file=exportpipe full=y


THe output file names will be

expfiles_aaaa
expfiles_aaab
expfiles_aaac
...
expfiles_zzzz

I tested the above and it worked for me - although I didn't test an export, but I did use the pipe and split command as shown.

live free or die
harry

[Morris Makuch]

Harry, thanks a lot. That is precisely what I wanted to do. It tested it and it works great. Thanks again for your help.

Morris