Re: Data migration from informix to oracle 10g R2

On Dec 28, 3:16 pm, chijar <chi..._at_gmail.com> wrote:

snip

>
> Thanks for your answers my dear friends...
> ...but it looks that i don't explain well...
> Let see...
> Enterprise 1: send me 23 plain text files monthly and this files
> contains a lot of rows, sometimes this files could contain millions of
> rows. Each file corresponding to one table. This files have 30 Gb of
> total size, that means that one file could have 2Gb or 1Gb of size.
> I don't have any type of access to source database (informix).
>
> Enterprise 2: (my office): after i receive files, i have to upload
> this data to my database (with workbench, sqlloader, or ¿external
> files?)

Are these files going to add into existing contents of the tables or are they going to be complete replacements of all the data in each table?

If the files completely replace everything then one way to approach something like this is to proceed like this ...

1. drop the schema owner from the oracle database
2. re-create the schema owner in the oracle database
3. define the tables and ( perhaps primary key indexes )
4. use sqlldr to populate the tables ( or use external tables )
5. define any extra indexes needed for your application at "Enterprise 2 aka your office". ( Yes I think you need to understand the SQL you will be running and look at what additional columns might need indexing besides primary keys ).
6. generate statistics for the tables or default to letting oracle decide when and how ( long topic here )
7. define any referential integrity constraints between the tables
8. get a backup of the oracle database

If you will adding in additional info to existing tables then maybe you want something that drops indexes, does the loads, then puts indexes back on. Somewhat dependent on how ref integrity comes into play or not, etc.

>
> The purpose of this oracle database (target), is only, and only
> selects, neither DML. i didnt' have the point of indexes and i don't
> think that enterprise 1 send me index. Or do you recommend me that i
> should ask for indexes DDL scripts? and what another things i should
> to ask ?

Depends on how you will be querying it but yeah you probably need to think about indexing.

>
> The data type is only char, varchar, and integer data, all tables will
> belonging to one schema.

Then migrating memo fields into blobs in oracle is something you won't have to worry about.

>
> I hope it was clearly. and i appreciate a lot your help.
> cesar.

Often in posting in cdos the more details you can supply the more you will receive good answers. Received on Sat Dec 29 2007 - 13:50:16 CST