Home » RDBMS Server » Server Administration » Rebuilding indexes and moving tables to different tablespaces
Rebuilding indexes and moving tables to different tablespaces [message #194779] Mon, 25 September 2006 04:54 Go to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
I want to rebuild indexes to a different tablespace
Although we use
alter index index_name rebuild tablespace ts1 online;
users are making transaction and using indexes
is there any harm involved rebuilding indexes and moving tables to a different tablespaces
should we perform this activity when there are no transactions or we can perform at any time?
i have some confusion about it so can u please help me in this regard
Re: Rebuilding indexes and moving tables to different tablespaces [message #194784 is a reply to message #194779] Mon, 25 September 2006 05:23 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> I want to rebuild indexes to a different tablespace
May i ask, why?
Re: Rebuilding indexes and moving tables to different tablespaces [message #194820 is a reply to message #194779] Mon, 25 September 2006 08:57 Go to previous messageGo to next message
ramakris
Messages: 21
Registered: July 2006
Location: Delhi
Junior Member
Hi-

First of all, whenever you move the table to different tablespace its corresponding indexes will get into unusable state. And you will have to rebuild the indexes at the end, it may affect your transactions. I would rather recommend you to perform these activities during off time.

Thanks,
Rama.
Re: Rebuilding indexes and moving tables to different tablespaces [message #194886 is a reply to message #194784] Mon, 25 September 2006 23:16 Go to previous messageGo to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
>> I want to rebuild indexes to a different tablespace
May i ask, why?

Because tables exists in a system tablespace so causing the performance degradation so thats why it is needed
after moving tables ,now it is required to rebuild indexes also
Re: Rebuilding indexes and moving tables to different tablespaces [message #194892 is a reply to message #194886] Tue, 26 September 2006 00:49 Go to previous message
SDA_09_2006
Messages: 1
Registered: September 2006
Junior Member
Hi,
you need to do this:
alter index <index_name> rebuild tablespace <new_tablespace>;

you can generate all such sql statements using:

select 'alter index ' || index_name || ' rebuild tablespace xyz;' from user_indexes where tablespace_name='your_old_tablespace_name';

you should this only in off hours because of following reason:

When you are moving tables that time the index become invalid this may cause problem for the insert/update statements. Insert/update will fail unless you drop the index before moving and recreate it.

SDA_90_2006

[Updated on: Tue, 26 September 2006 00:53]

Report message to a moderator

Previous Topic: Mandatory background process
Next Topic: Invalid Objects
Goto Forum:
  


Current Time: Mon Dec 05 21:04:42 CST 2016

Total time taken to generate the page: 0.11112 seconds