Move index to different tablespace in oracle

How can I move index from one tablespace to another in Oracle 11g ?
Sam OZAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Why do you want to change the schema owner of an index?  That doesn't make sense.

Just create the index in the new schema.

I'm also not sure why you want a schema owning an index on a table in a different schema.
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
alter index <the index name> rebuild tablespace <new tablespace name> online;

Open in new window

0
 
Sam OZAuthor Commented:
Thanks Alex. The problem is the new tablespace is with a new user. So It doesn't let me move index
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Alexander Eßer [Alex140181]Software DeveloperCommented:
the new tablespace is with a new user
What does this mean??!
0
 
sdstuberConnect With a Mentor Commented:
tablespaces don't have users.

do you mean the index owner doesn't have a quota on the new tablespace  or the quota is too small?

if so, talk to your dba to grant sufficient quota.

If it's simply a company policy to not mix schemas within a tablespace, then that's something different.
You'll either need to get an exemption for your policy or have the dba create a new tablespace that you are allowed to use for that schema.
0
 
Sam OZAuthor Commented:
Sorry, I mean just the otherway.. The new user is using the new tablespace...

It is somethng like this
     Olduser with OldTS(Tablespace)....    Index is now in OldTS
     New  User with NewTS
      The Index need to move from OldTS to NewTS

   When the  trying the Alter Tablespace ,  It is not letting the move of Index
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
So you mean the default tablespace of the users?!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.