Re: Alter tablespace add datafile file size error

You must specify the size of your datafile:

SQL> alter tablespace IMETABLES
> 2 add datafile '/data/OracleDatafiles/imetables2.dbf'
 size 1000M
> 3 autoextend
> 4 on next 1M
> 5 maxsize 1500M
> 6 /
>

I would *never* use a on next 1M - this may happen too often and kill perofmance.
If your start size would be 1G, and maxsize 1.5G, try to end there in 5 to 20 steps max:
that would result in on next 100M (down to on next 25M). Similary, 500 - 1500M: next would be in the 200M .. 50M range.

--
Frank
Vaughan Jackson <vaughan_jackson_at_my-deja.com> schreef in berichtnieuws
8rt07e$a79$1_at_nnrp1.deja.com...


> Hi,

>

> I have been trying to add a second datafile to a tablespace using the

> following syntax:

>

> SQL> alter tablespace IMETABLES

>   2  add datafile '/data/OracleDatafiles/imetables2.dbf'

>   3  autoextend on

>   4  next 1M

>   5  maxsize 1500M

>   6  /

>

> However, I find that no matter what maxsize value I provide (even 100M),

> the datafile created keeps growing (at moment of creation), until

> it takes up ALL of the available diskspace. At this point, an error

> message appears, basically saying that no more space is left:

>

> alter tablespace IMETABLES

> *

> ERROR at line 1:

> ORA-19510: failed to set size of 578816 blocks for file

> "/data/OracleDatafiles/imetables2.dbf" (blocksize=4096)

> ORA-27059: skgfrsz: could not reduce file size

> SVR4 Error: 28: No space left on device

> Additional information: 2

> ORA-19502: write error on file "/data/OracleDatafiles/imetables2.dbf",

> blockno

> 578561 (blocksize=4096)

> ORA-27072: skgfdisp: I/O error

> SVR4 Error: 25: Inappropriate ioctl for device

> Additional information: 578561

>

> This message does not make sense for a number of reasons, including:

>

> 1. there is over 2GB available, but I have limited the maxsize to 1500M;

> 2. it says it has failed to set the size of a 578816 block file,

> whereas the OS is telling me that we have over 4M blocks available

> (different blocks?):

>

> [VJ]<vjackson> scorpius:/data/OracleDatafiles df .

> /data              (/dev/dsk/c0t0d0s7 ): 4747446 blocks   469300 files

>

> Other details: Oracle 8i, Solaris 2.7.

>

> Am I doing something wrong here?

>

> Thanks,

>

> Vaughan Jackson

>

>

> Sent via Deja.com http://www.deja.com/

> Before you buy.