RE: Tablespace management.

So what is wrong with having the SLOTS table occupy several hundred extents? If it grows to 500MB it will occupy 1000 extents, so what. If it were to grow into GB I'd probably make the extents 1MB and swallow the wasted .5M in the CELL extent - what is half a meg when you're in the GB.

As for Peoplesoft, I manage Peoplesoft systems as well and I have separated the tables into tiny (extent size 16K, tables do not have more than 1 block - ~90%-95% of all tables in the system, most of them even empty), small (extent size 64K), medium, large, and XXL plus one for the active _TMP, _WRK, and _TAO tables, and then the same for the indexes. Works like a charm. The only tablespaces I have to worry about are the large and xxl table and index tablespaces. Everything else is pretty much static.

At 07:59 AM 5/30/2003 -0800, you wrote:
>Richard,
>
> My troubles come mainly form PeopleSoft and some in-house created
> applications. I'll use the in-house applications as the example since
> their simpler.
>
> Our CIM system has tables that contain very few rows of data,
> like the identification information for each robot(CELLS). Now there are
> only 30 robots on the longest/most complex line we have (BTW: due to the
> duhvelopers of this application each line needs it's own instance on it's
> own server, don't ask why). Now this table NEVER grows beyond 512KB is
> size. But each robot can have up to 1024 component slots (512 on each
> side) that need to be defined with what is in them (SLOTS). This table
> easily gets into a couple of MB but then sits there since we do tons of
> updates but no more inserts. If we're doing LMT's then to optimize the
> storage on this mess I either need 2 tablespace or else set the uniform
> extent size to 512K and allow the SLOTS table to have several extents.
>
> This example is one of the simpler ones, there are a lot more
> that get even more problematic, like those for our test data. If 10i has
> bad news on this front it may well become the "straw that breaks the
> camel's back" for Oracle around here. We're already toying around with DB2.
>
>Dick Goulet
>Senior Oracle DBA
>Oracle Certified 8i DBA

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).