Import and CLOBS : a question

Hello everybody

We have run here into a problem - for which we have found a solution but no explanation: Maybe one of you would be able to explain what is hapenning to us ?

The problem:
We have 2 instances. A is 9.1.0.3 on a Tru64 machine

                      B is 9.2.0.3  on Linux x86

We have the same schemas (ie as database models) on both instances, but not the same amount of data in each.
I ran export with the same parameters on both, A export is a 64Gb file, B is 19Gb file
Now I ran the imports , with the same parameters on both instances: A imports in about 23 hours
B imports in about 86 hours

We find it very strange that B is so slow compared to A . (About a year ago we upgraded to 9.2 on our Tru64 machine, but since we had this kind of behaviour we came immediatly back to 9.1 . Now we have to be on 9.2 on Linux, so the problem becomes more .. problematic )

After a lot of checks, we found out that a few of our tables that had CLOBS where the ones which slowed everything. We had the following create table statement: Create TABLE TOTO1 as (
id1 number NOT NULL,
name varchar2(4000) NULL,
lob_yn number(1) NOT NULL,
the_clob CLOB NULL);

What we did is part TOTO1 in 2 tables:
Create TABLE TOTO1_1 as (
id1 number NOT NULL,
name varchar2(4000)NULL,
lob_yn number(1) NOT NULL);

Create TABLE TOTO1_2 as (
id1 number NOT NULL,
the_clob CLOB NOT NULL);

Now we import again : B imports in 5h19mns Which is much more reasonable (at least we like that timing better). But why do we have this behaviour ? Why in 9.2 and not in 9.1 ? Have we missed something in our upgrade ? Some new parameter (or different value) in the init.ora ?
We are now looking at another instance /schema that has the same kind of problem

    but that one has no *LOBs at all, so any help, hint, suggestion, "look at that man page all is there" would be very welcome and appreciated.

Thanks Received on Thu Nov 27 2003 - 08:36:05 CST