fargo
asked on
HOWTO with Tablespace maintenance and tuning
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
very detailed explaination by both of you. Will come back to you fellas in coming days...(little busy with work..)
ASKER
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??
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??
ASKER
Seems that my last post was not good enough to ask. But it is still open..ok.. :-)
Thx for your help guys.
Thx for your help guys.
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.)
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.)
ASKER
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.