Re: Migrate from 8i to 9i with different NLS_LENGTH_SEMANTICS

On Sun, 08 Aug 2004 20:49:47 +0200, Tosta <tosta_at_wtal.de> wrote:

>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.
>

Hi Tosta,

Here's one approach among many:

1. Use the exp/imp to create a DDL file for all tables/indexes etc. in the old db (imp with show=y)
2. Edit the DDL file to replace BYTE clauses (if any) with CHAR clauses.
3. Run the DDL file against the new db.
4. Create a db link from new db to old db.
5. Fetch data into new db tables with :

"insert into mytab select * from mytab_at_link_to_old_database;"

• Kenneth Koenraadt