Forum Stats

  • 3,768,303 Users
  • 2,252,772 Discussions
  • 7,874,521 Comments

Discussions

Categories

Move tablespace for a partitioned index

user8686720
user8686720 Member Posts: 96
edited Apr 22, 2013 2:08AM in General Database Discussions
Hi,

We have a table with subpartition (hash) and partition (range). There are some composite indexes defined on the table. We are working on a project to move objects to some newly created tablespaces. As of now we have successfully moved tablespaces of subpartition, partition and tables.

Index subpartitions also have been moved but while moving index partitions we are facing issues. Please suggest how to move tablespace for index partition as well as the index. We need to move index partitions from APPLSYSX to APPS_TS_TX_IDX.

DB Version: 10.2.0.4

SQL> alter index APPLSYS.WF_ITEMS_PK REBUILD PARTITION WF_ITEM1 TABLESPACE APPS_TS_TX_IDX;
alter index APPLSYS.WF_ITEMS_PK REBUILD PARTITION WF_ITEM1 TABLESPACE APPS_TS_TX_IDX
*
ERROR at line 1:
ORA-14287: cannot REBUILD a partition of a Composite Range partitioned index

Index Definition:

CREATE UNIQUE INDEX "APPLSYS"."WF_ITEMS_PK" ON "APPLSYS"."WF_ITEMS" ("ITEM_TYPE", "ITEM_KEY")
PCTFREE 10 INITRANS 11 MAXTRANS 255 LOGGING
STORAGE(INITIAL 40960 NEXT 1048576
PCTINCREASE 0 FREELISTS 32 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
TABLESPACE "*APPLSYSX*" LOCAL
(PARTITION "WF_ITEM1"
PCTFREE 10 INITRANS 11 MAXTRANS 255
STORAGE(INITIAL 40960 NEXT 1048576
PCTINCREASE 0 FREELISTS 32 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
TABLESPACE "APPLSYSX" LOGGING
( SUBPARTITION "SYS_SUBP4861"
TABLESPACE "*APPS_TS_TX_IDX*",
......
......
......


Thanks for your help.

Joy
Tagged:
· Share on TwitterShare on Facebook

Answers

  • Unknown
    Unknown
    edited Apr 22, 2013 12:54AM
    Rebuild the subpartitions - that is where the data is.

    You need to rebuild them one at a time.
    http://www.dbmotive.com/ora-14287-cannot-rebuild-a-partition-of-a-composite-range-partitioned-index/
    · Share on TwitterShare on Facebook
  • user8686720
    user8686720 Member Posts: 96
    Thanks rp0428 for your reply. As you can see in the index definition the subpartitions already moved to the new tablespace. We now need to move index and index partitions to the new tablespace (APPS_TS_TX_IDX). Old tablespace is APPLSYSX.

    Thanks,
    Joy
    · Share on TwitterShare on Facebook
  • Unknown
    Unknown
    edited Apr 22, 2013 1:11AM
    You are confused.

    The data for a table is stored in segments. For a partitioned table the data is contained in partitions: the table HAS NO DATA. Each partitions data is stored in its own segment which can be in its own tablespace.

    For a subpartitioned table the data is contained in subpartitions: the table HAS NO DATA, the partitions HAVE NO DATA. ALL DATA IS IN THE SUBPARTITIONS. Each subpartitions data can be in its own tablespace. There is NO index data to be moved. There is NO index partition data to be moved. There is NO data except the data in the index subpartitions.

    If you want to modify the tablespace the NEW partitions or subpartitions will use (as opposed to moving existing data to a new tablespace) then you need to use the 'modify_index_default_attrs' clause of the ALTER INDEX statement.

    See ALTER INDEX in the SQL Reference
    http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_1008.htm#BGECEJHE
    >
    modify_index_default_attrs

    Specify new values for the default attributes of a partitioned index.

    Restriction on Modifying Partition Default Attributes The only attribute you can specify for a hash-partitioned global index or for an index on a hash-partitioned or composite-partitioned table is TABLESPACE.

    TABLESPACE Specify the default tablespace for new partitions of an index or subpartitions of an index partition.
    >
    See the example in the doc
    >
    Modifying Default Attributes: Example The following statement alters the default attributes of local partitioned index prod_idx, which was created in "Creating an Index on a Hash-Partitioned Table: Example". Partitions added in the future will use 5 initial transaction entries and an incremental extent of 100K:

    ALTER INDEX prod_idx
    MODIFY DEFAULT ATTRIBUTES INITRANS 5 STORAGE (NEXT 100K);
    · Share on TwitterShare on Facebook
  • user8686720
    user8686720 Member Posts: 96
    edited Apr 22, 2013 1:15AM
    I am aware of this. If I want to drop the old tablespace (APPLSYSX), can I do so? Hopefully Oracle will not allow me to do so. For data segments tablespace of partitions and table have been moved/altered using MODIFY ATTRIBUTE clause. Similarly we want to alter the definition for index partition and the index itself.

    One more thing, one way we can achieve is by recreating the index with new tablespace name. But I am wondering if there is any syntax using which tablespace of index, and index partition of a subpartitioned index can be moved/altered, just as it is possible for data segments.

    Thanks,
    Joy

    Edited by: user8686720 on Apr 21, 2013 10:13 PM
    · Share on TwitterShare on Facebook
  • user8686720
    user8686720 Member Posts: 96
    Below syntaxes used to alter/move tablespace of partition index and index of a subpartitioned index.

    SQL> alter index APPLSYS.WF_ITEMS_PK MODIFY DEFAULT ATTRIBUTES FOR PARTITION WF_ITEM1 TABLESPACE APPS_TS_TX_IDX;

    Index altered.

    SQL> alter index APPLSYS.WF_ITEMS_PK MODIFY DEFAULT ATTRIBUTES TABLESPACE APPS_TS_TX_IDX;

    Index altered.

    Thanks,
    Joy
    · Share on TwitterShare on Facebook
  • Unknown
    Unknown
    >
    One more thing, one way we can achieve is by recreating the index with new tablespace name. But I am wondering if there is any syntax using which tablespace of index, and index partition of a subpartitioned index can be moved/altered, just as it is possible for data segments.
    >
    Now I'm confused. Didn't I just provide you the doc link above that shows how to do this?

    Please clarify what issue you are still having trouble with.
    · Share on TwitterShare on Facebook
  • user8686720
    user8686720 Member Posts: 96
    The link you provided was helpful. The issue has been resolved. Thank you once again for you help. Please see my last update where I've pasted the exact query used to achieve the requirement.

    Regards,
    Joy
    · Share on TwitterShare on Facebook
This discussion has been closed.