Re: Should my extents be bigger?

Jacques Raymond Kilchoer wrote:
-

• Oracle7 Server Release 7.1.3.2.0 - Production Release
• VAX OpenVMS V6.1-1H2
  -
  
• I have a tablespace called TEMP with an initial_extent = 256,000
• a next_extent = 1,048,576
• a min_extents = 2
• a max_extents = 121
• (see below)
  -
  
• The data file for the TEMP table has 524,288,000 bytes
• (see below)
  -
  
• It seems to me that since tablespace TEMP has a max_extents of 121, it
• can use up
• at most 1 * 256,000 (initial_extent) + 120 * 1,048,576 (next_extent)
• = 256,000 + 125,829,120 = 126,085,120
  -
  
• Since 126,085,120 is less than 524,288,000 I would think that the
• datafile for the
• tablespace never gets used to its full capacity.
  -
  
• Is my understanding correct?
• If so, what should I do? Increate the next_extent size or the
• max_extents?
  -
  

It is true that a single user will only be able to use about 120 meg of temporary tablespace before they get a 'maximum number of extents exceeded' message. If that is not enough space, then you should change the NEXT value of the tablespace.

However, if you have multiple users on your system, you need to leave enough room in your temp tablespace to accomodate the sorting needs of all of the users that are logged into the system. So, it might still be appropriate for you to limit your users so that they cannot use the entire temporary tablespace.

Hope this helps.

--

Bob Swisshelm
Eli Lilly and Company
swisshelm_at_lilly.com Received on Tue Sep 24 1996 - 00:00:00 CEST