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

Ian Bainbridge (bainbridge_i_at_perc03_at_bgers.co.uk) wrote:

: 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 |
: +--------------------------+-------------------------+-----------------------+
  Received on Mon Apr 03 1995 - 00:00:00 CEST