This content has been marked as final. Show 3 replies
For renaming purpose you first need to find out the name of all the partiion from the table as
after completing above then rebuild the index.
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';
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
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
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;
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