HOWTO with Tablespace maintenance and tuning

Posted on 2006-04-17
Last Modified: 2012-05-05
Hi All,

I am just trying to learn more tips and tricks on managing-maintaining-tuning the tablespaces in Oracle.
Best way for me is to get the feedback from the experts regarding the above. Any step by step way to create a tablespace and further maintain it....tuning etc would be great.

I can search for it in just don't make a plain search and post the links. I would expect a detailed explaination here.

Question by:fargo
    LVL 34

    Assisted Solution

    Usually no maintenance or tuning is required on tablespaces *IF* they were initially created based on a plan to use tablespaces wisely and efficiently.  This planning work must be done before the tablespaces are created, and before the tables and indexes are assigned to tablespaces.

    I'm assuming you are talking about "locally-managed" tablespaces, with equally-sized extents in each tablespace, and not the older "dictionary-managed" tablespaces tablespaces.  I don't recommend them at all.

    In a simple system, you will need just three manually-created tablespaces in addition to the default ones in Oracle, and you can name them: small, medium and large.  They will have different extent sizes, and you then place your tables and indexes into an appropriate tablespace based on the size of the table/index.  Many DBAs like to have tables and indexes separate from each other, so you may want six tablespaces for your application, with three for indexes and three for tables.

    What exact sizes should you use for extents in your "small", "medium" and "large" tablespaces, and what should the size of the datafiles be?  That will vary depending on your numbers of tables and indexes, your disk system and your OS.  For example, your OS and/or disk system may or may not be able to support individual datafiles over 2GB in size, so that may be a constraint you have to work around.  If that is an issue, you may need multiple separate datafiles per tablespace, or you may want multiple tablespaces of each size.

    We use a slightly more complex system for tablespaces, with five different sizes: small, medium-small, medium, medium-large, and large.  We also have separate tablespaces for tables and indexes.  Because of the number of tables in our application, and our desire to have just one datafile per tablepace and to keep the individual datafiles under 2GB, we have multiple tablespaces for some of the sizes.  Just FYI, we using the following extent sizes, but you may want other sizes depending on your data volumes and/or your block sizes:

                           Tables         Indexes                      
    small                      8k                 8k
    medium-small       40k                20k
    medium              400k              200k
    medium-large    4000k            2000k
    large               40000k          20000k

    If your application has any tables that are used for large amounts of transactions, then the tables are deleted (or truncated) before the next interface or batch job, you will want to separate these tables from the more permanent tables, probably into a "work" or "scratch" tablespace, so these objects don't cause excessive fragmentation among your more-permanent objects.
    LVL 11

    Author Comment

    Hi markgeer,

    Thx for your detailed reply. Some doubts

    1) why do one wish to seperate the table and index tablespaces? OR even something like small, big, medium..??
    2) why not define tablespace for each oracle dbuser? By having tablespace for each oracle dbuser, one can clearly identify which tablespace is bundled with which dbuser.

    LVL 34

    Expert Comment

    1a.  Why separate tables and indexes?  For performance and recovery.  These separate tablespaces (datafiles) can then be on separate disks, so the same read/write heads do not have to first read the index blocks, then jump to read the corresponding table blocks.  Also, you may want the tables on a RAID5 device, but the indexes on RAID0, RAID1 or non-RAID.  If you have a disk failure someday, for recovery the data is more important than the indexes (since they can be rebuilt from the data) and if the tables and indexes are on separate disks, you have more recovery options.

    1b. Why multiple sizes?  All applications will have tables of different sizes: some small code or lookup tables, some with moderate amounts of data, and some with large amounts of data.  If you don't separate them into different tablespaces, then someday when your tablespace gets full, or if you choose to drop or move a table or index, the amount of free space left may not be easily or efficiently re-used.

    2. Why not define a separate tablespace for each user?  In most application that I have worked with, all of the tables are owned by just one schema owner, and all other users share these tables.  If your application is designed differently, it may make sense for you to have the tables for each owner in their own tablespace.
    LVL 2

    Accepted Solution

    As you asked in detail, will try to reply appropriately:

    Tablesspace > Segments > Extents > Data Blocks (Oracle)
    In Oracle we have several different types of Segments: Data, Index, Lob, Cache, IOT, Undo, Temp....etc
    And each n every has different attributes and properties, that is the reason that it is HIGHLY recommended (basically ordered) to put them, in there respective tablespaces, don't MIX & MINGLE, as they have different properties.

    So 1st thing to remember, should have atleast 6 tablespaces (minimum) for a small type of db, where as in actual env., there are a lot more then this...

    2nd: As now u must have understand the concept of different tbsps (tablespaces), according to req. of segments, lets take one segment, the most important one, the DATA segment, where our actual DATA is residing: eg., SOCTT.EMP.ENAME --- 'KING'

    Just to make things understandable:
    EMP         ------ has one million rows      >   there average row size is also very important, having different sizes
    DEPT       ------ has 1 thousand rows
    BONUS    ------ has 500 rows

    Now if we put all these in one tbsp, you can see there maintenance is different from each other, the number of extent allocation will be  entirely different too, and u must be aware that allocation and de-allocation of extents are the most expensive task for Oracle Server,
    so at the time of creation of tbsps, need to assign extent sizes keeping in mind that the table can stay within this tbsp for the whole of coming year, this should be ur space assignment strategy, should have atleast 3 different tbsps.

    And yes it should be according to applicaton/owner, eg, in our case we have so many applications are running, so when I create there env., this'll be my strategy, as we are a warehous shop, for each application we have 3 envs. and 3 sizes, accordingly:

    GBS is  application name, & 3 phases; staging area, warehouse, marts, and 3 sizes small, medium & large

    1. GBS_STG_DATAS (for small tables), ....DATAM (for medium sized tables), ...DATAL (large tables, having million of rows)

    S extent size is 64k, M extent size is 128kb, L extent size is 4mb,
    obviously the sizing is according to ur needs and availability of resources (storage).

    The biggest benefit u get, after having multiple tbsps, that if one goes currupt, other data will still be available, where as if u put all in ONE, if anythng goes wrong....nothing will be available.

    In order to understand this concept, you need to understand ur data, from core:
    EMP, DEPT, BONUS -------- what is the growth of each of ur object/tables and then only u can assign them to appropriate tbsps.

    Inform, if u need any more info. or clarification on this.
    LVL 11

    Author Comment

    very detailed explaination by both of you. Will come back to you fellas in coming days...(little busy with work..)
    LVL 11

    Author Comment

    i agree with both of you to keep different tablespaces for data and indexs etc. But may be a question of a newbie..
    For ex: If i m adding some indexes on a table, is it that i have to define the tablespace used for index explicitely?
    Is it possible to say that all present indexes for a db user, should go to small_idx tablespace and whatever index will be created should by default use the tablespace defined for indexs??
    LVL 11

    Author Comment

    Seems that my last post was not good enough to ask. But it is still open..ok.. :-)
    Thx for your help guys.

    LVL 34

    Expert Comment

    When you add an index to a table, it is best to explicitly define the tablespace you want the index to be created in.  If you don't, it will be created in the "default tablespace" for that user.

    You can rebuild all present indexes (if you want) for a user to a particular tablespace, but you have to execute a command like this for each index:
    alter index [index_name] rebuild [new_tablespace_name];

    You can set the default tablespace for a user, but remember that both tables and indexes will be created here, unless you explicitly provide a different tablespace for either a table or index when you create one.

    (I was at the annual IOUG Conference in Nashville, TN for most of this week, and I was quite busy there, so this is my first day on this site this week.)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now