Re: newbie Q: Oracle 6.x; percent_increase and next_extent settings

In article <PJBONDI.95Mar9013100_at_agate.spctrm.com>, pjbondi_at_agate.spctrm.com (Philip Bondi; 416.368.7979 x224) writes:
>From: pjbondi_at_agate.spctrm.com (Philip Bondi; 416.368.7979 x224)
>Newsgroups: comp.databases.oracle
>Subject: newbie Q: Oracle 6.x; percent_increase and next_extent settings
>
>Hello to all:
>
>Thank you very much for reading this posting.
>
>One of our client's recently had a disasterous out of space situation.

 I wouldn't really call it disasterous - just one of those things  which happens and doesn't really take much fixing.

>The most active table in our database assumedly started off with the
>standard 10k next_extent size and 50% percent_increase setting.
>However, the next_extent size finally became larger than the largest
>available extent.
>
>What is the standard method of protecting against such situations?
>Under Sybase SQL Server extent sizes are fixed at 16k. Does anyone
>find a flaw in standardizing all Oracle tables to have a 16k
>extent_size and 0% percent_increase?
>

 One known flaw of this method is the maximum number of extents  (this is operating system specific - consult your manual. On VMS  it is 121) can easily be exceeded using 16K static chunks  for the table and/or any index. This would only allow for the table  to grow to 1936K - not very big.

 Another gotcha on V6 under VMS was the default number of extents was  99.  

 I always try to size the tables so that they don't need to extend but  I have the luxury of a system which ends each year and is started afresh  the next year, meaning that I can use the previous years sizes for the new  database. I size them according to the user thoughts and then add a lot.  This does of course waste disk space during the year but this is not a  concern for us. I typically size the tables and use NEXT EXTENT of 25% of  the overall size with 0% increase. This works well for me but your mileage  will vary.

 There used to also be a school of thought (I seem to remember this from  an ORACLE V5/6 course) that many extents for an object made for poorer  performance and LOTS of people spent (and probably still do) lots of  time and effort ensuring they had as few (1) extents as possible for this  very reason. I think that this is deemed by some not to be a great  issue these days but force of habit rules for me.

 Even after all this, you can still be unable to extend some object because  you either have NO space left in the tablespace or the space is not  contiguous.

 The only suggestions on this are to

• monitor your freespace and increase as required
• if your freespace is really fragmented perform export/imports

 Hope this has been of some help - feel free to mail me for more details  if I have been unclear.

>Please help a couple of newbies! Reply to via a posting and via
>
>Thank you in advance.
>
>.....Philip.

 <snip>

>Philip Bondi Spectra Securities Software, Inc.
>Database Administrator 150 York St., #700, Toronto, Ontario, M5H 3S5, Canada
>pjbondi_at_spctra.com voice: (416)368-7979 x224 fax: (416)368-7886
>

-- 
+--------------------------+-------------------------+-----------------------+
| Ian Bainbridge           | bainbridge.i_at_bgers.co.uk|Phone: (44)191-216-0202|
| British Gas ERS          | Newcastle Upon Tyne, UK |            x2790      |
+--------------------------+-------------------------+-----------------------+