Re: Unable to extend Table by 315

In article <spnv2tk5kb38jatrk03tospeiuoud2s1dr_at_4ax.com>,   Harry <a_harrison_uk_at_yahoo.co.uk> wrote:
> I have a tablespace with autoextend set to on - I'm running an "insert
 into ... select from" query -
> I know the query (well I'm pretty sure), will only generate 400
 records but I keep getting the above
> error
>
> Never seen it before - can someone please explain why & how to get
 around it?
>
> Many thanks
>
> harry
>

You need to determine the current setting for the table and the tablespace that it is associated with.

Oracle's default setting for storage when creating a table are generally ugly.

First is to consider where your tables are being built. The default is user's default tablespace. If you do not specifically set it, it is set to user (I think). User is generally a very small tablespace and should not be used for real data.

Second consider the actual storage of a table. Generally it is a good idea not to allow the table to grow by a percentage of the previous size. The default is 50%. For instance, if the table initial extent is 1M and full and one adds 1 more row, the system would allocate an extent of 1.5M. The next extent is 50% bigger, etc. Even if you start small this will grow.

Third is how our you tables interacting. If all of the tables are in the same tablespace and the tablespace is not autoextending ( IMHO a good thing ), all tables are vying for the same space.If there is either no more room, or not enough room for that extent or not enough contiguous space for that extent, you cannot add any more to that table space.

Generally you want all tables within a single tablespace to have the same size extents -- you set the storage ( init x next x ) and pctincrease 0 for all tables in that tablespace. If you do a lot of adds and deletes you may want to coalasce the tablespace.

Finally, you could just be out of space

All these parameters are viewable as the dba account.

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rosetta.org
              Ususual disclaimers


Sent via Deja.com http://www.deja.com/
Before you buy.