Re: Help! ORA-01658 on import, and with plenty of space

I have employed the suggestions made by Mr. Dunbar, and it appears to creating the tables more efficiently. That is, the data is being spread evenly across the datafiles. However, the speed has dramatically slowed down. Is there a reason for this?

Thanks,
Pedro Mercado

Norman Dunbar <Norman.Dunbar_at_lfs.co.uk> wrote in message news:<E2F6A70FE45242488C865C3BC1245DA7028C2CC7_at_lnewton.leeds.lfs.co.uk>...
> Pedro,
>
> I think your problem is still one of insufficient contiguous space to
> create an INITIAL or NEXT extent for the problem table. This can be
> caused by the COMPRESS=Y as mentioned by Sybrand in his reply.
>
> I too suffer from this problem when importing customer dumps and I've
> traced it doen to the following - all of which could be affecting you :
>
> 1. COMPRESS=Y and not enough free space in one chunk for the INITIAL or
> NEXT extent to be created.
>
> 2. Customer has, at some point, done an export with compress, dropped
> all thair tables and re-imported. This is in a misguided attempt to
> increase performanec by having all the data in one extent. The problem
> now is that any subsequent exportes, even with COMPRESS=N will create a
> script in the dump file which has the original INITIAL and NEXT extents
> as defined by the customers last attempt at defragmenting his tables and
> indexes.
>
> What I do is run a dummy import where I specify INDEXFILE=crud.sql and
> this creates a file with lots of create table and create index commands.
> I then edit this file to remove the STORAGE parameters. I have a C++
> program to do this which runs on my HP servers, but I can let you have
> the source code if you want. It isn't perfect, and there seems to be
> many ways that Oracle place the STORAGE parameters in the file, but I
> think I have most of them covered :o)
>
> (I've had 'STORAGE ( INITIAL...', or 'STORAGE (INITIAL...' or
> 'STORAGE(INITIAL ...' and so on - they seem to be pretty random with
> where they put spaces and things.)
>
> Once I've created a new script, I drop all the curent tables completely,
> coalesce the tablespace and run my script to create the objects with
> default storage. Then I run the import with IGNORE=Y and usually (!) it
> works, however, there might be a problem if the export dile contains
> tables or indexes with columns which are Oracle reserved words. These
> are usually pretty easily fixed.
>
> Email me if you wish to have a copy of the C++ source code.
>
> Regards,
> Norman.
>
> -------------------------------------
> Norman Dunbar
> Database/Unix administrator
> Lynx Financial Systems Ltd.
> mailto:Norman.Dunbar_at_LFS.co.uk
> Tel: 0113 289 6265
> Fax: 0113 289 3146
> URL: http://www.Lynx-FS.com
> -------------------------------------
>
>
>
>
> -----Original Message-----
> From: mercadop_at_expertx.com (Pedro Mercado) [mailto:mercadop_at_expertx.com]
> Posted At: Thursday, August 22, 2002 9:52 PM
> Posted To: server
> Conversation: Help! ORA-01658 on import, and with plenty of space
> Subject: Re: Help! ORA-01658 on import, and with plenty of space
>
>
> Thank you Mr. Bakker for your reply. I tried what you suggested, and
> it did run more efficiently with compress=n on the export. However,
> when I hit the largest of the tables (3GB, ~30mil records, 19 fields)
> it gave the error again. Why did the strategy not work with this
> table, and what can I do to correct this problem in the future?
>
> Thanks,
> Pedro Mercado
Received on Tue Aug 27 2002 - 11:11:37 CDT