Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Tablespace layout

RE: Tablespace layout

From: <>
Date: Tue, 28 Aug 2001 14:13:02 -0700
Message-ID: <>

I agree completely with Lisa. Several years ago I had a chance to talk with an Oracle tester in IBM's benchmark lab. They had a one table/one tablespace/one datafile layout, mostly for recoverability. Once you document it fully (and name it logically) administration is not that difficult.

For those of you who know Oracle internals, is there any advantage in making the uniform extents a multiple of the db_block_buffers area in the SQA? Or is that just a feeling left over from when I had to align strings on full-words while programming assembler?

                    "Koivu, Lisa"                                                      
                    <lisa.koivu_at_efair        To:     Multiple recipients of list 
          >               <>                    
                    Sent by:                 cc:                                       
                   Subject:     RE: Tablespace layout        
                    08/28/2001 05:07                                                   
                    Please respond to                                                  

Well Satar you didn't even read Cherie's email.

Way back before I took that db to the tablespace layout you refer to
(straight out of HOw to Stop Defragmenting and Start Living white paper) I
had created a separate tablespace for each partition.  It was an awful pain back then and the number of tablespaces I had was ridiculous.  I can see your point with mttr  and keeping your tablespaces fairly small in comparison to what it's become.  I guess it's a tradeoff with pros and cons either way.

However, I suggest you take that paper (above) and throw it their faces.  I disagree about different sized extents in the same tablespace vehemently. I think they should be uniform, period, for the same reason you state - if disk is so precious, then use it wisely !!  Plus, when your partition starts looking for another 100MB extent, you are out of luck.

    -- Or should I just leave them all in the same tablespaces as    non-partitioned tables?

What's your feeling on partitioning?  I think if you are dropping data monthly, use partitions wherever you can.  That way you are more assured of reusing your disk - however, then you are looking at a different tablespace per partition.

I believe at one point I had 2-3 partitions in each tablespace, rotating them (part1 in ts1, part2 in ts2, part3 in ts1, part4 in ts2, etc.)  Maybe that will provide the compromise in functionality, disk space management and recoverability you seek.

I do know your pain.  Good Luck.

Lisa Koivu
Certified Monkey and DBA
Ft. Lauderdale, FL, USA

   -----Original Message-----

   From:   Satar Naghshineh []
   Sent:   Tuesday, August 28, 2001 4:30 PM
   To:     Multiple recipients of list ORACLE-L
   Subject:        RE: Tablespace layout

   Hi Cherie,

   Just shove all your data on a RAID 5 (great for data Warehouses) and    forget about it. If that is not possible, then stick with what your DBA    team has stated about everything being ok as long as the extents are    multiples of one another.

   Satar Naghshineh

   -----Original Message-----

   From: []
   Sent:   Tuesday, August 28, 2001 7:27 PM
   To:     Multiple recipients of list ORACLE-L
   Subject:        Tablespace layout

   I've been thinking a lot about our tablespace layout for our    data warehouse.  Our warehouse is approaching 200Gig    and is almost out of space on disk.  I'm getting another 75 Gig    this weekend and am trying to plan the best use for that 75Gig.

   Our current data tablespace layout is that we have two tablespaces    for small, medium, and large data tables and the same for indexes.    So it's something like this:



   LG_IDX02    I think that theoretically, all of the tables in each of the tablespaces    were supposed to have the same sized extents when they were    originally created.  However, over time, there are multiple sizes    of extents in the large and medium tablespaces.  Even though we    show significant free space, it is fragmented and coalescing can    not put together enough contiguous space to reuse a lot of the available    space.

   Our largest tables are partitioned.  However the partitions are not    split
   out into separate tablespaces but go into the same medium and large    tablespaces as non-partitioned tables.   Theoretically, I suppose that    this
   is not a problem if, when partitions are dropped every month, the    resulting
   space is reused 100%.  I'm not sure if it is.

   Here is my question.  My DBA team members feel that it's fine to have    tables with a variety of extent sizes in the same tablespace as long as    they are all multiples of each other (50, 100, 400, 2000, etc.).   My    concern
   is that this setup is fine when the smaller tables need to extend but    when
   the larger tables need to extend, they can't pull together enough    contiguous
   space and I keep having to add more.   I'd prefer to have only one size    of
   extent in each tablespace and keep it very pure that way.   Then I know    every single extent can be reused.  So I am considering increasing the    number of tablespaces so we have something more like this:


   I would also probably split them out into at least two tablespaces for    each
   Maybe not for the smaller sizes, but for the larger sizes.

   I have several issues I'm trying to keep in mind.  One is the ease of    maintenance
   for initial creation and ongoing upkeep.   I don't want to have too many    tablespaces
   if I don't need to.   Another issue is mean time to recover.   If we    lose a
   single tablespace,
   I'd prefer to have to recover fewer files.  The maximum file size we are    using is 2Gig.
   We need to keep our recovery time under four hours total.

   Probably the biggest issue I'm facing now is the sheer size of the large    tablespaces.
   They are so big and bulky that it's almost impossible to reorg them or    even
   clean them up.   I think that if I had more smaller tablespaces, I would    have more
   options.  This database is still at 8.0.4 and it's going to be a while    before it can be
   upgraded so that limits my options for reorging as well.   All cleanup    has
   to be done
   in a series short Sunday windows.  I don't have the luxury of a tool for    doing this
   reorg so have to do it manually.

   Another issue is partitions.   We are dropping the old partitions on the    main fact
   table once a month.  We are not currently planning on dropping any of    the
   other partitions.
   The tables have a variety of partition names and schemes.  Some are    partitioned yearly,
   monthly, quarterly, half-yearly.   There is no consistency.   I'm    debating
   whether I should
   split each partition out into it's own tablespace.   That would be    almost a
   hundred tablespaces.
   Or just the table that we're dropping partitions on monthly.   That    would
   be about 50 tablespaces.
   Or should I just leave them all in the same tablespaces as    non-partitioned

   We are using Sun Solaris 2.6 on an E10K.   We have EMC disk  and Veritas    file manager.
   Using version 8.0.4 of Oracle, as I said.  Using RMAN and Veritas for    backups.

   Any feedback, ideas, suggestions, things to watch out for, think about,    etc. would be greatly
   appreciated.   This is going to take a lot of time and effort to do and    I
   don't want to get all the
   work done and find out it doesn't work as well as I hoped and have to    redo

   Thanks for your time,

   Cherie Machler
   Oracle DBA
   Gelco Information Network

   Please see the official ORACLE-L FAQ: <>    --

   Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
   San Diego, California        -- Public Internet access / Mailing Lists
   To REMOVE yourself from this mailing list, send an E-Mail message    to: (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).
Please see the official ORACLE-L FAQ:

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: (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).
Received on Tue Aug 28 2001 - 16:13:02 CDT

Original text of this message