Path: news.easynews.com!core-easynews!newsfeed3.easynews.com!easynews.com!easynews!news.glorb.com!news.tele.dk!not-for-mail
From: Kenneth Koenraadt
Newsgroups: comp.databases.oracle.server,comp.databases.oracle.tools
Subject: Re: Migrate from 8i to 9i with different NLS_LENGTH_SEMANTICS
Date: Sun, 08 Aug 2004 19:59:39 GMT
Message-ID: <41168628.5752750@news.inet.tele.dk>
References: <cf5sk6$8up$1@beech.fernuni-hagen.de>
X-Newsreader: Forte Free Agent 1.11/32.235
Lines: 54
Organization: TDC Totalloesninger
NNTP-Posting-Host: 80.164.80.83
X-Trace: 1091995181 dread11.news.tele.dk 184 80.164.80.83:12854
X-Complaints-To: abuse@post.tele.dk
Xref: core-easynews comp.databases.oracle.server:227817 comp.databases.oracle.tools:65377
X-Received-Date: Sun, 08 Aug 2004 12:58:38 MST (news.easynews.com)

On Sun, 08 Aug 2004 20:49:47 +0200, Tosta <tosta@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@link_to_old_database;"



- Kenneth Koenraadt