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

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 Wed Aug 21 2002 - 15:50:39 CDT