Re: export data from 10g, import into 9i

On Jan 16, 4:36 am, steph <stepha..._at_yahoo.de> wrote:
> On 15 Jan., 22:13, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On Jan 15, 1:42 pm, joel garry <joel-ga..._at_home.com> wrote:
>
> > > On Jan 15, 7:56 am, steph <stepha..._at_yahoo.de> wrote:
>
> > > > Hi Group,
>
> > > > I need to import a schema from a 10.2 database into a 9.2. database.
> > > > What's the best way to do this? Exporting with 9i's exp and importing
> > > > with 9i's imp was my first guess - but i'm not completely sure if
> > > > exporting data from a 10g database with a 9i tool is a good idea. Any
> > > > hints would be appreciated!
>
> > > > Thanks,
> > > > Stephan
>
> > > Well, you've managed to hit a bit of the documentation that is
> > > obviously not completely updated for 10.2, but the basic idea is
> > > correct:  http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_im...
>
> > > Your guess is good, but of course if you've used any 10-specific
> > > objects, they won't come across.
>
> > > More detail is found in metalink Note:132904.1, including things like
> > > minimum patch levels necessary.  Details like that is why we ask you
> > > post such things.  In particular, you don't want to use different
> > > patch sets on the 9 exp, imp and db, an easy mistake to make if client
> > > and server are installed on different machines.  You don't have to run
> > > anything like catexp, but you do need to go through sql*net (as
> > > opposed to a local connection) for accessing 10 from 9.
>
> > > jg
> > > --
> > > @home.com is bogus.
> > > "You've never been lost until you've been lost at Mach 3." - Paul F.
> > > Crickmore (test pilot)
>
> > Steph, whenever it was necessary to exp/imp from a higher version into
> > a lower version the process has been to use the lower version exp
> > utility.  With some past versions of Oracle you had to run an Oracle
> > provided export script to create the necessary lower version export
> > utility dictionary views in the source database for this to work.  You
> > could not export tables that used new features that the target
> > database did not understand such as a version 8 table that used a new
> > LOB datatype into version 7.3 database that did not know what a LOB
> > was.
>
> > See the metalink article Joel suggested and check your source database
> > for use of unsupported features.
>
> > An altername is to extract the data into delimited text files via
> > SQLPlus and to use sqlldr to reload the data after precreating the
> > target tables.
>
> > Also database links, CTAS, Insert/select is another approach depending
> > on how much data you have, how many tables, etc ...
>
> > But exp/imp will probably work just fine and is likely to be easier.
>
> > HTH -- Mark D Powell --
>
> Thanks all for the valuable information, that help s a lot. Actuallly
> we've tried already with exp/imp of 9i, but unfortunately primary keys
> were lost in the process, as it seems. But maybe the provided metalink-
> article will give me a clue to this ...
>
> regards,
> stephan- Hide quoted text -
>
> - Show quoted text -

You should not have a problem with PK being lost via exp/imp. Check that the export and import did not specify constraints=n. The default is to export table PK, UK, and FK constraints and to import them along with indexes and grants but you can tell the utilities not to do so.

The log= feature can be very useful on larger or long running imports to see what went wrong.

HTH -- Mark D Powell -- Received on Wed Jan 16 2008 - 14:43:58 CST