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

Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote in message news:<b2v7muoes9fo0rd3n884g5lbip2abk2h24_at_4ax.com>...
> On 21 Aug 2002 11:57:11 -0700, mercadop_at_expertx.com (Pedro Mercado)
> wrote:
>
> >I am not a certified DBA so please bear with me. I have built a USERS
> >tablespace using the following syntax:
> >
> >/*
> >create tablespace users datafile 'l:\oracle\oradata\dbm\users01.dbf'
> >size 2000M reuse
> >autoextend off
> >minimum extent 512k
> >default storage ( initial 5120k next 5120k minextents 1 maxextents
> >4096 pctincrease 0);
> >*/
> >
> >I then proceeded to alter the tablespace by adding datafiles of 2000M.
> > I should have plenty of space and the tablespace should be empty.
> >However, when I import from a dump file with about 10 smaller tables
> >(500-1000mb) and 3 larger ones (1-2GB), I get ORA-01658: unable to
> >create INITIAL extent ...
> >
> >Why am I getting this problem if I have enough space available? I
> >also noticed that only 3 of the datafiles are being used while the
> >others remain unused.
> >
> >Please help, and thank you in advance.
>
> You must have run the export with compress=Y (the default)
> This adds up all extents currently used, and generates a create table
> statement in the export with the INITIAL extent = space in use.
> The problem you are having is that the calculated space doesn't exist
> *contiguously*
> The usual solution to this kind of problem, if you can't rerun the
> export, is to pre-create the table.
> You can get the definition out of the export by running
> imp show=y file=<your export> log=<any filename>
> Just edit <any filename>, isolate the create tabe statement, edit it,
> run it on the target database and run your import.
>
> Hth
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Thu Aug 22 2002 - 15:51:53 CDT