to change the storage parameter of hash partitioned tables/indexes

From: zhu chao <zhuchao_at_gmail.com>
Date: Wed, 19 Apr 2006 07:25:43 -0700
Message-ID: <962cf44b0604190725p3a618233g833518edbdcc40f@mail.gmail.com>

hi, all,

    I am having some difficulty rebuilding the table partition/index partitions with different storage clause.  say table search_attr_01 is hash partitioned tables and the storage for each table/partition is 10M. And it turned out to be too big and now I want to rebuild the index partition to make it smaller, to another smaller tablespace.

   Without specifying the storage(initial 1m next 1m) , rebuild index partition works, but storage not released.

    Is there any solution to this problem? we can't afford drop and recreate the index.

07:19:50 SQL> select count(*) from SEARCH_ATTR_01 partition (SYS_P674);

  COUNT(*)

---

    177338
07:19:16 SQL> ALTER INDEX SEARCH_ATTR_01_AA_IDX REBUILD PARTITION SYS_P674 TABLESPACE SMALL_INDEX ONLINE storage(initial 1m next 1m); ALTER INDEX SEARCH_ATTR_01_AA_IDX REBUILD PARTITION SYS_P674 TABLESPACE SMALL_INDEX ONLINE storage(initial 1m next 1m)

                                                             *

ERROR at line 1:
ORA-14185: incorrect physical attribute specified for this index partition

07:17:40 SQL> ALTER INDEX SEARCH_ATTR_01_AA_IDX REBUILD PARTITION SYS_P673 TABLESPACE SMALL_INDEX ONLINE; Index altered.

Elapsed: 00:00:02.48
--

Regards
Zhu Chao
www.cnoug.org
--

http://www.freelists.org/webpage/oracle-l Received on Wed Apr 19 2006 - 09:25:43 CDT