HOWTO with Tablespace maintenance and tuning

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.

LVL 11
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark GeerlingsDatabase AdministratorCommented:
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.
fargoAuthor Commented:
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.

Mark GeerlingsDatabase AdministratorCommented:
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fargoAuthor Commented:
very detailed explaination by both of you. Will come back to you fellas in coming days...(little busy with work..)
fargoAuthor Commented:
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??
fargoAuthor Commented:
Seems that my last post was not good enough to ask. But it is still open..ok.. :-)
Thx for your help guys.

Mark GeerlingsDatabase AdministratorCommented:
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.)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.