Re: Alternative to EXP/IMP

rshea_at_my-deja.com (Richard Shea) wrote in message news:<43160f6f.0109260043.61adffb2_at_posting.google.com>...
> Hi - I'm looking at alternatives to EXP/IMP when moving a schema/data
> from one box to another (this is because I've got the schema/data on a
> higher version of Oracle then the target machine has, 8.1.7 is trying
> to go to 8.1.6).
>
> My current plan is :
>
> SCHEMA:
> 1. EXP rows=N
> 2. IMP INDEXFILE="something.sql"
> 3. Take the REM lines out of something.sql
> 4. run the something.sql on the target machine
>
> DATA:
> 1. Dump data to comma delimited files
> 2. Use SQLLDR to load comma delimited files to target database
>
> Haven't tried any of it yet, but I'll have to in the morning. Any
> Oracle gurus want to suggest a better idea ?
>
> regards
>

You can perform the task as you listed but why not perform a remote export using the 8.1.6 version to unload the 8.1.7 database and then just run the import. We have multiple databases and they are usually at different levels so this is my first resort for moving data. Use of the Oracle provided set of imp/exp scripts that adjust the dictionary to allow use of the new version of the rdbms with prior versions of the imp/exp utilities are the next option we employ.

You may want to use a different delimiter rather than a comma if any of your target tables have comment or description fields. I have found that '|' works very well. Allow, delimited output does not work for tables with long or LOB columns. You can see the cooperative FAQ for SQL to generate your unload scripts if you go that route.

  http://www.jlcomp.demon.co.uk/faq/flatfile.html

Good luck.

• Mark D Powell --