Kenneth wrote:
> 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
>
How about this (untested by me):
set the following parameter on your new, empty database.
- "NLS_LENGTH_SEMANTICS enables you to create CHAR and VARCHAR2 columns
using either byte or character length semantics. Existing columns are
not affected.". (I assume you already have this set in the new database).
- "exp ... rows=n", then import. This will create all objects,
constraints, grants, etc, but zero rows.
Hopefully the CHAR and VARCHAR2 columns will be what you want at this
point (use the CHAR_USED column of the ALL_, USER_, or DBA_TAB_COLUMNS
data to confirm).
3. if still not what you want, then modify the columns of the existing
tables using ALTER TABLE to change the datatype of the CHAR and VARCHAR2
columns to the desired semantics. (You could generate all the required
SQL by querying the data dictionary). Then do another export including
the row data this time, and import with "ignore=y" to get the data loaded.
In fact, I don't see in the documentation where you can't just alter the
column datatype even with rows already present.
--Mark Bole
Received on Mon Aug 09 2004 - 18:36:46 CDT
