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

From: Tosta <tosta_at_wtal.de>
Date: Mon, 09 Aug 2004 13:50:01 +0200
Message-ID: <cf7oda$1g1$1@beech.fernuni-hagen.de>

Hi Kenneth,

thanks a lot for your reply.

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

So I did and received a file with all the SQL statements to create the structure. Unfortunately the code is not quite in an appropriate syntax. Here's an example for the users table:

  "CREATE TABLE "USERS" ("ID" NUMBER(8, 0) NOT NULL ENABLE, "NAME" VARCHAR2(8)"
  " NOT NULL ENABLE, "LOWER_NAME" VARCHAR2(8) NOT NULL ENABLE, "PASSWORD" VARC"
  "HAR2(32) NOT NULL ENABLE, "IP_ADDRESS" VARCHAR2(15), "GROUP_ID" VARCHAR2(4)"
  " NOT NULL ENABLE, "WANTS_TO_ORDER" CHAR(1) NOT NULL ENABLE, "FIRST_NAME" VA"
  "RCHAR2(40), "LAST_NAME" VARCHAR2(40), "SALUTATION_CODE" VARCHAR2(4), "STREE"
  "T_1" VARCHAR2(40), "STREET_2" VARCHAR2(40), "ZIP" VARCHAR2(10), "CITY" VARC"
  "HAR2(40), "COUNTRY_CODE" VARCHAR2(2), "LANGUAGE_SPEC" VARCHAR2(5), "FORMAT_"
  "SPEC" VARCHAR2(5), "PHONE_1" VARCHAR2(25), "PHONE_2" VARCHAR2(25), "FAX" VA"
  "RCHAR2(25), "EMAIL" VARCHAR2(40), "COMPANY_NAME" VARCHAR2(50), "URL" VARCHA"
  "R2(80), "xxxxx_COMPANY_ID" VARCHAR2(4), "xxxxx_CUSTOMER_ID" NUMBER(8, 0), ""
  "CREATION_DATE" DATE NOT NULL ENABLE, "LOGIN_DATE" DATE)  PCTFREE 10 PCTUSED"
  " 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 131072 NEXT 131072 MINE"
  "XTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER"
  "_POOL DEFAULT) TABLESPACE "USERS""
  " ALTER TABLE "USERS" MODIFY ("CREATION_DATE" DEFAULT sysdate )"

. . skip table "USERS"

  "CREATE UNIQUE INDEX "PK_USERS_1" ON "USERS" ("ID" )  PCTFREE 10 INITRANS 2 "
  "MAXTRANS 255 STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 409"
  "6 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPA"
  "CE "USERS" LOGGING"

Considering the more than 8,000 lines in that file, it might get hard to re-format the code into usable SQL to be run on the target DB by SQL+. I could handle the quotes and the "skip table" lines by regular expressions, but I couldn't tell how to fix the broken lines and put the trailing ';' correctly (by a program, of course, I wouldn't do it by hand...). Maybe I'm running the wrong way -- ? See (3) below.

> 2) Edit the DDL file to replace BYTE clauses (if any) with CHAR
> clauses.

Not any, so no problem.

> 3) Run the DDL file against the new db.
>

As mentioned above, it is almost impossible to run imp's output as an SQL script. So what is your intention when you say "run the DDL file against the new db"? Is there an import mode to execute the DDL file?

> 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;"
There may be an easier way: it is possible to import into existing tables (says Oracle9i Database Utilities manual).

So my problem has been reduced to how to import the database _definition_.

Thanks again for your help.

Tosta Received on Mon Aug 09 2004 - 06:50:01 CDT

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