3 Replies Latest reply: Dec 7, 2012 6:01 AM by OraFighter RSS

    Auto partition  rebuilding error

    OraFighter
      I created a table with auto partition in 11g. Insert data to it so it automatically created 15 days partition.

      then I try to create index on each partition I execute the following command.

      CREATE INDEX table1 ON table1(A_ID) local NOLOGGING PARALLEL TABLESPACE TABLESPACENAME1 UNUSABLE;

      above command worked fine.

      but I try to rebuild the index on each partition with following command .

      alter index table1 rebuild partition SYS_P161 NOLOGGING PARALLEL ;

      it says 'ORA-02149: Specified partition does not exist'

      then I rename the partition SYS_P161 to JUN01
      alter table call_group1 rename partition SYS_P161 to jun01 ;

      Still same error is coming .... what is the correct syntax please....?
        • 1. Re: Auto partition  rebuilding error
          Chanchal Wankhade
          Hi,

          For renaming purpose you first need to find out the name of all the partiion from the table as
          SQL> select partition_name from all_tab_partitions where table_name='your_table_name';
           
          -- above query will show you name of all the partiion
           
          SQL> ALTER TABLE archost  RENAME PARTITION Partition_you_want_to_rename TO new_name;
           
          
           
          SQL> select partition_name from all_tab_partitions where table_name='your_table_name';
           
          after completing above then rebuild the index.
          • 2. Re: Auto partition  rebuilding error
            rp0428
            >
            I created a table with auto partition in 11g. Insert data to it so it automatically created 15 days partition.

            then I try to create index on each partition I execute the following command.

            CREATE INDEX table1 ON table1(A_ID) local NOLOGGING PARALLEL TABLESPACE TABLESPACENAME1 UNUSABLE;

            above command worked fine.
            >
            If that worked fine then your table name is 'TABLE1' but this partition rename is used on a different table
            >
            alter table call_group1 rename partition SYS_P161 to jun01 ;
            >
            And your index rebuild is back to using TABLE1
            {quote{
            but I try to rebuild the index on each partition with following command .

            alter index table1 rebuild partition SYS_P161 NOLOGGING PARALLEL ;

            it says 'ORA-02149: Specified partition does not exist'
            >
            We have no way of knowing if you have two tables, TABLE1 and CALL_GROUP1, or if you have made a syntax error.
            Please edit your post and FIX the problem with the code so that you are using the same table name for all operations.

            The ALTER syntax you used is correct. Make sure your table and index meet the 'Restrictions on Rebuilding Indexes' in the ALTER INDEX chapter of the SQL Language doc
            http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_1009.htm
            >
            Rebuilding Unusable Index Partitions: Example The following statements rebuild partitions p2 and p3 of the cost_ix index, making the index once more usable: The rebuilding of partition p3 will not be logged:
            ALTER INDEX cost_ix
            REBUILD PARTITION p2;
            ALTER INDEX cost_ix
            REBUILD PARTITION p3 NOLOGGING;
            • 3. Re: Auto partition  rebuilding error
              OraFighter
              problem was I used the same name for TABLE NAME and INDEX NAME by mistake .
              I changed the index name , then the problem solved

              Edited by: OraFighter on Dec 7, 2012 5:31 PM