5 Replies Latest reply on Jan 5, 2011 1:47 PM by Friend of Oracle

    ORA-02149 Specified partition does not exist

    Friend of Oracle
      Hello All,

      I wanted to rename an existing partition but i'm getting the above error.
      I could list the partition name using "SELECT * FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME IN ()"
      and the partition is listed there. When i try to rename it--error!. When i try to select from the said partition
      i get the same error.

      Does anybody have any idea regarding this? should i run stats?

      Thanks. More power.

      yoy
        • 1. Re: ORA-02149 Specified partition does not exist
          CKPT
          Hi,
          I could list the partition name using "SELECT * FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME IN ()"
          you trying to extract the table_name not the partition_name


          query as
          select * from all_tab_partitions where partition_name='PARTITION';
          then try the command with appropriate output.

          Hope this helps..

          Thanks
          • 2. Re: ORA-02149 Specified partition does not exist
            Friend of Oracle
            I think i wasnt clear on my post.

            I executed the select * from all_tab_partitions where table_name in (table_list) stmt and then
            i copy the partition name from the output and use it in the alter table rename partition
            command as well as in the select * from ..partition(partitioname).
            • 3. Re: ORA-02149 Specified partition does not exist
              CKPT
              Hi,

              if the partition exist in database then it could happen

              can you please post the output here... from the mentioned query, so that it will give clear understanding..

              check for rename partition http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10739/partiti.htm#i1008006

              Thanks
              • 4. Re: ORA-02149 Specified partition does not exist
                Chinar
                Please see below
                SQL> desc all_tab_partitions
                 Name                                      Null?    Type
                 ----------------------------------------- -------- ----------------------------
                
                 TABLE_OWNER                                        VARCHAR2(30)
                 TABLE_NAME                                         VARCHAR2(30)
                 COMPOSITE                                          VARCHAR2(3)
                 PARTITION_NAME                                     VARCHAR2(30)
                 SUBPARTITION_COUNT                                 NUMBER
                 HIGH_VALUE                                         LONG
                 HIGH_VALUE_LENGTH                                  NUMBER
                 PARTITION_POSITION                                 NUMBER
                 TABLESPACE_NAME                                    VARCHAR2(30)
                 PCT_FREE                                           NUMBER
                 PCT_USED                                           NUMBER
                 INI_TRANS                                          NUMBER
                 MAX_TRANS                                          NUMBER
                 INITIAL_EXTENT                                     NUMBER
                 NEXT_EXTENT                                        NUMBER
                 MIN_EXTENT                                         NUMBER
                 MAX_EXTENT                                         NUMBER
                 PCT_INCREASE                                       NUMBER
                 FREELISTS                                          NUMBER
                 FREELIST_GROUPS                                    NUMBER
                 LOGGING                                            VARCHAR2(7)
                 COMPRESSION                                        VARCHAR2(8)
                 NUM_ROWS                                           NUMBER
                 BLOCKS                                             NUMBER
                 EMPTY_BLOCKS                                       NUMBER
                 AVG_SPACE                                          NUMBER
                 CHAIN_CNT                                          NUMBER
                 AVG_ROW_LEN                                        NUMBER
                 SAMPLE_SIZE                                        NUMBER
                 LAST_ANALYZED                                      DATE
                 BUFFER_POOL                                        VARCHAR2(7)
                 GLOBAL_STATS                                       VARCHAR2(3)
                 USER_STATS                                         VARCHAR2(3)
                
                SQL> select partition_name from all_tab_partitions where table_name='ARCHOST';
                
                PARTITION_NAME
                ------------------------------
                ARCHIVALDAY
                RESERVE
                TODAY
                UNKNOWNDAY
                YESTERDAY
                
                SQL> ALTER TABLE archost  RENAME PARTITION TODAY TO curr;
                
                Table altered.
                
                SQL> select partition_name from all_tab_partitions where table_name='ARCHOST';
                
                PARTITION_NAME
                ------------------------------
                ARCHIVALDAY
                CURR
                RESERVE
                UNKNOWNDAY
                YESTERDAY
                
                SQL>
                • 5. Re: ORA-02149 Specified partition does not exist
                  Friend of Oracle
                  Thanks all for your reply.

                  We were able to resolve it, for some reason we needed to wrap it with double quotes "PARTITION NAME".
                  Probably some non-displayable chars got into the name.

                  Thanks and more power.