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

Mr. Dunbar,  

I appreciate your thorough evaluation of my problem. I would greatly appreciate the code you are using. I think it will be very insightful for me. I will attempt to implement your suggestions--It sounds like it might provide a fix.  

Sincerely,
Pedro Mercado
Expert Database Marketing Systems, Inc.
949-768-5775 x19
Mercadop_at_expertx.com  

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 Mon Aug 26 2002 - 11:56:24 CDT