Re: Alter tablespace add datafile file size error

Hi Frank,

Are you telling me that if I fail to provide a size argument in the statement below, it defaults to infinite size?

Thanks for your help,

Vaughan.

In article <8s1htc$o66$1_at_porthos.nl.uu.net>,   "Frank" <frankbo_at_interaccess.nl> wrote:
> 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.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 13 2000 - 13:24:15 CDT