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

On Mon, 09 Aug 2004 13:50:01 +0200, Tosta <tosta_at_wtal.de> wrote:

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

Hi Tosta,

Yep, the show file must be edited before use.

Other solutions :

a)
There are a lot of 3rd party tools which can extract DDL's from a database, e.g. TOAD a.o.

b)
Oracle 9i provides the DBMS_METADATA package to extract DDL's from the db. You could thus import the exp dump file to an arbitrary 9i DB, extract DDL's from the desired objects with DBMS_METADATA package and drop the imported objects from the db when done.

• Kenneth Koenraadt