OraFAQ Forum: Server Administration » Rebuilding indexes and moving tables to different tablespaces

Home » RDBMS Server » Server Administration » Rebuilding indexes and moving tables to different tablespaces

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Rebuilding indexes and moving tables to different tablespaces [message #194779]

Mon, 25 September 2006 04:54

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

Report message to a moderator

Re: Rebuilding indexes and moving tables to different tablespaces [message #194784 is a reply to message #194779]

Mon, 25 September 2006 05:23

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>> I want to rebuild indexes to a different tablespace
May i ask, why?

Report message to a moderator

Re: Rebuilding indexes and moving tables to different tablespaces [message #194820 is a reply to message #194779]

Mon, 25 September 2006 08:57

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.

Report message to a moderator

Re: Rebuilding indexes and moving tables to different tablespaces [message #194886 is a reply to message #194784]

Mon, 25 September 2006 23:16

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

Report message to a moderator

Re: Rebuilding indexes and moving tables to different tablespaces [message #194892 is a reply to message #194886]

Tue, 26 September 2006 00:49

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 06:44:00 CDT 2024