Hi All,
I am just trying to learn more tips and tricks on managing-maintaining-tunin
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 google..so just don't make a plain search and post the links. I would expect a detailed explaination here.
Thx.
fargo
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
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.
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.
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)
2. GBS_WHO_DATAS
3. GBS_DMT_DATAS
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.
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??
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.)
Business Accounts
Answer for Membership
by: markgeerPosted on 2006-04-17 at 06:19:44ID: 16468807
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.