Move local index to different tablespace

All posts relating to Oracle database administration.

Moderator: Tim...

Move local index to different tablespace

Postby shrinika » Thu Jan 03, 2013 4:06 am

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..
shrinika
Advisor
 
Posts: 255
Joined: Fri Jun 13, 2008 3:18 pm

Re: Move local index to different tablespace

Postby Tim... » Thu Jan 03, 2013 9:26 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Move local index to different tablespace

Postby shrinika » Thu Jan 03, 2013 9:05 pm

Thanks Tim!!
shrinika
Advisor
 
Posts: 255
Joined: Fri Jun 13, 2008 3:18 pm

Re: Move local index to different tablespace

Postby Tim... » Thu Jan 03, 2013 9:32 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 1 guest

cron