Migrate from 8i to 9i with different NLS_LENGTH_SEMANTICS

Hi.

I'm desperatly seeking a migration tool or -method or what ever. Your advice and hints are highly appreciated!

I'm migrating a middle-sized 8i database on one machine to a 9i database on another machine. There is no network connection between the two machines.

The 8i DB has WE8ISO8859P1 database character set and *byte* length semantics.

The new 9i DB shall have AL32UTF8 database character set and *char* length semantics (due to the multi-byte character set); i have already created this DB with the appropriate parameters.

The national character sets don't matter.

I now have to get the data structure and the data from the 8i DB to the 9i one. imp/exp does not meet my requirements, for on import, the tables will be created with *byte* length semantics, as in the source DB, although 9i DB has *char* length semantics for newly created tables. Thus, exp/imp leads to a sort of exact clone of the source DB.

Though I have been searching the Oracle docs, the web, and news groups for the last couple of days, I can't find a way to copy the DB according to my needs. My current idea is a tool that creates a big but simple SQL script from an entire DB with schemas, tables, data, indexes, constraints etc to re-create the whole lot somewhere else. In this way, the length semantics would be preserved. I would love to write such a program, but I have to be done very soon.

Does anyone of you knows an SQL-based migrating tool, or knows any other way to do it? Thanks in advance!

Cheers,

Tosta. Received on Sun Aug 08 2004 - 13:49:47 CDT