Move local index to different tablespace

[shrinika]

Tim, I am using oracle11g. I have couple of question on local partition.

I need to add partition with local index on one of the table in production. Couple of questions.

1. My database is OLTP system. When i use ALTER TABLE command to add partition, does it lock the table for a moment? I am only adding partition.
I suspect that there will be micro second lock on the table. Do you agree?

2. The table has local index. When i add partition, the local index will be created automatically for new partition. Can i move the local index(for that new partition) to separate tablespace while
adding partition? I am not able to find such a thing in oracle documentation? Another option is, we can move the local index to different tablespace
after adding partition. Just checking if there is a way..

[Tim...]

Hi.

1) Pretty much all DDL on a table will lock it in some way. As you suggested, the lock will be very short lived, so it is safe to do.

2) The syntax doesn't allow you to control the location of the index directly. You could reset the default tablespace before doing this operation though. Either way, moving an index on an empty partition is going to be really quick.

Cheers

Tim...

[shrinika]

Thanks Tim!!

[Tim...]