10 Replies Latest reply: Nov 28, 2012 3:17 AM by 973776 RSS

    all rows in table do not qualify for specified partition

    973776
      SQL> Alter Table ABC
      2 Exchange Partition P1 With Table XYZ;

      Table altered.

      SQL> Alter Table ABC
      2 Exchange Partition P2 With Table XYZ;


      Exchange Partition P2 With Table XYZ
      *
      ERROR at line 2:
      ORA-14099: all rows in table do not qualify for specified partition

      The exchange partition works correct for the first time. However if we try to exchange 2nd partition it gives the error.
      How do i solve this error?
      How do i find rows which are not qualified for the specified portion. is there a query to find out the same?
        • 1. Re: all rows in table do not qualify for specified partition
          908002
          Ensure that all the rows in the segment qualify for the partition. Perform the alter table operation with the NO CHECKING option

          OR Run ANALYZE table VALIDATE on that partition to find out the invalid rows and delete them
          • 2. Re: all rows in table do not qualify for specified partition
            Manik
            simple demonstration:
            CREATE TABLE TMP3
            (
               id      INTEGER,
               num     INTEGER,
               name1   VARCHAR (2)
            )
            PARTITION BY LIST (name1)
               (PARTITION P1
                   VALUES ('A', 'B', 'C'),
                PARTITION P2
                   VALUES ('D', 'E', 'F'));
            
            
            
            
            CREATE TABLE swap
            (
               id      INTEGER,
               num     INTEGER,
               name1   VARCHAR (2)
            );
            
            
            
            INSERT INTO swap (name1)
                 VALUES ('A');
            
            
            commit;
            
            alter table TMP3 exchange partition P2 with table swap;
            
            ERROR at line 1: 
            ORA-14099: all rows in table do not qualify for specified partition 
            
            
            Because P2 does not have 'A' in its list..
            
            this will succeed :
            
            alter table TMP3 exchange partition P1 with table swap;
            Hope this helps. Check your data based on this....

            Cheers,
            Manik.
            • 3. Re: all rows in table do not qualify for specified partition
              973776
              Hi Manik,

              Thanks for the reply.
              This is wht i m doing.

              create table FLX_TIME1
              (
              ACCOUNT_CODE VARCHAR2(50) not null,
              POSTING_DATE DATE not null
              ) partition by range(POSTING_DATE) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
              ( partition day0 values less than (TO_DATE('01-12-2012', 'DD-MM-YYYY') ) )
              /
              create index FLX_TIME1_N1 on FLX_TIME1 (POSTING_DATE)
              /

              create table FLX_TIME2
              (
              ACCOUNT_CODE VARCHAR2(50) not null,
              POSTING_DATE DATE not null
              )

              Insert 50 records in db for FLX_TIME1


              Declare
              days Number;
              Begin
              FOR days IN 1..50
              Loop
              insert into FLX_TIME1 values (days,sysdate+days);
              End Loop;
              commit;
              END;

              select * from user_tab_partitions ie where ie.table_name='FLX_TIME1';

              3 rows selected
              TABLE_NAME     COMPOSITE     PARTITION_NAME     
              FLX_TIME1     NO               DAY0     
              FLX_TIME1     NO               SYS_P3707     
              FLX_TIME1 NO               SYS_P3708


              alter table FLX_TIME1 exchange partition SYS_P3707 with table FLX_TIME2;

              this works

              now i m trying to exchange 2nd partition
              alter table FLX_TIME1 exchange partition SYS_P3708 with table FLX_TIME2;     
              it gives the error :all rows in table do not qualify for specified partition
              • 4. Re: all rows in table do not qualify for specified partition
                Solomon Yakobson
                Well, exchange syntax and logic is not that clear as it should (IMHO). There is no syntax element that tells Oracle do we want to exchange partition data into a non-partitioned table or non-partitioned table data into partition. It is decided by Oracle checking non-partitioned table. If non-partitioned table is empty then it means you want to exchange partition data into a non-partitioned table. And if non-partitioned table is not empty then it means you want to exchange non-partitioned table data into partition. So when you issue:
                alter table FLX_TIME1 exchange partition SYS_P3707 with table FLX_TIME2;
                non-partitioned table is empty and partition SYS_P3707 data is moved inro FLX_TIME2. Now FLX_TIME2 is not empty and wher you issue:
                alter table FLX_TIME1 exchange partition SYS_P3708 with table FLX_TIME2;
                Oracle assumes you want to move FLX_TIME2 into partition SYS_P3708 and since FLX_TIME2 contains SYS_P3707 data which is not suitable for partition SYS_P3708 it raises an error. Truncate table FLX_TIME2 (or delete FLX_TIME2 data) and you will be fine.

                SY.
                • 5. Re: all rows in table do not qualify for specified partition
                  973776
                  Hi Solomon,

                  actually i want the data as an archive. This data will be used for inquiry sorts of. So its needed.
                  So cannot delete or truncate it.
                  Is there another way?

                  Regards
                  Stephen
                  • 6. Re: all rows in table do not qualify for specified partition
                    John Spencer
                    If you process is exactly as you descrubed it, then ther is no possible way that the second exchange could possibly work. The exchange partiton clause does exactly what its name implies, it "renames" the partition to the unpartitioned table name, then attaches the old table as a partiton of the partitioned table. Extending the sample from Manik:
                    SQL> CREATE TABLE TMP3
                      2  (
                      3     id      INTEGER,
                      4     num     INTEGER,
                      5     name1   VARCHAR (2)
                      6  )
                      7  PARTITION BY LIST (name1)
                      8     (PARTITION P1
                      9         VALUES ('A', 'B', 'C'),
                     10      PARTITION P2
                     11         VALUES ('D', 'E', 'F'));
                    
                    Table created.
                    
                    SQL> CREATE TABLE swap
                      2  (
                      3     id      INTEGER,
                      4     num     INTEGER,
                      5     name1   VARCHAR (2)
                      6  );
                    
                    Table created.
                    
                    SQL> insert into swap values (1, 1, 'A');
                    
                    1 row created.
                    
                    SQL> insert into swap values (2, 2, 'B');
                    
                    1 row created.
                    
                    SQL> insert into tmp3 values (3, 3, 'C');
                    
                    1 row created.
                    
                    SQL> commit;
                    
                    Commit complete.
                    
                    SQL> alter table TMP3 exchange partition P1 with table swap;
                    
                    Table altered.
                    
                    SQL> select * from swap;
                    
                            ID        NUM NA
                    ---------- ---------- --
                             3          3 C
                    
                    SQL> select * from tmp3;
                    
                            ID        NUM NA
                    ---------- ---------- --
                             1          1 A
                             2          2 B
                    So, after your first exchange partition statement, xyz has whatever contents were in partiton p1 and partition p1 has whatever was in xyz. Your second statement attempts to put what used to be the contents of p1 into p2. Clearly, at least to me, that will not work.

                    John
                    • 7. Re: all rows in table do not qualify for specified partition
                      Solomon Yakobson
                      stephen.b.fernandes wrote:
                      Is there another way?
                      First of all, exchange is physical operation. It is not possible to append exchanged data. So solution would be to create archive table as partitioned and use non-partitioned intermediate table for exchange:
                      SQL> create table FLX_TIME1
                        2  (
                        3  ACCOUNT_CODE VARCHAR2(50) not null,
                        4  POSTING_DATE DATE not null
                        5  ) partition by range(POSTING_DATE) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
                        6  ( partition day0 values less than (TO_DATE('01-12-2012', 'DD-MM-YYYY') ) ) 
                        7  /
                      
                      Table created.
                      
                      SQL> create index FLX_TIME1_N1 on FLX_TIME1 (POSTING_DATE)
                        2  /
                      
                      Index created.
                      
                      SQL> create table FLX_TIME1_ARCHIVE
                        2  (
                        3  ACCOUNT_CODE VARCHAR2(50) not null,
                        4  POSTING_DATE DATE not null
                        5  ) partition by range(POSTING_DATE) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
                        6  ( partition day0 values less than (TO_DATE('01-12-2012', 'DD-MM-YYYY') ) ) 
                        7  /
                      
                      Table created.
                      
                      SQL> create table FLX_TIME2
                        2  (
                        3  ACCOUNT_CODE VARCHAR2(50) not null,
                        4  POSTING_DATE DATE not null
                        5  )
                        6  /
                      
                      Table created.
                      
                      SQL> Declare
                        2  days Number;
                        3  Begin
                        4  FOR days IN 1..50
                        5  Loop
                        6  insert into FLX_TIME1 values (days,sysdate+days);
                        7  End Loop;
                        8  commit;
                        9  END; 
                       10  /
                      
                      PL/SQL procedure successfully completed.
                      
                      SQL> set linesize 132
                      SQL> select partition_name,high_value from user_tab_partitions where table_name='FLX_TIME1';
                      
                      PARTITION_NAME                 HIGH_VALUE
                      ------------------------------ ----------------------------------------------------------------------------------------------------
                      DAY0                           TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
                      SYS_P119                       TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
                      SYS_P120                       TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
                       
                      Now we need to echange partition SYS_P119 to FLX_TIME2 and then echange FLX_TIME2 into FLX_TIME1_ARCHIVE:

                      To exchange it with FLX_TIME2:
                      SQL> truncate table FLX_TIME2;
                      
                      Table truncated.
                      
                      SQL> alter table FLX_TIME1 exchange partition SYS_P119 with table FLX_TIME2;
                      
                      Table altered.
                      To exchange FLX_TIME2 with FLX_TIME1_ARCHIVE we need to create corresponding partition in FLX_TIME1_ARCHIVE. To do than we use LOCK TABLE PARTITION FOR syntax supplying proper date value HIGH_VALUE - 1 (partition partitioning column is less than HIGH_VALUE so we subtract 1) and then use ALTER TABLE EXCHANGE PARTITION FOR syntax:
                      SQL> lock table FLX_TIME1_ARCHIVE
                        2    partition for(TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') - 1)
                        3    in share mode;
                      
                      Table(s) Locked.
                      
                      SQL> alter table FLX_TIME1_ARCHIVE exchange partition
                        2    for(TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') - 1)
                        3    with table FLX_TIME2;
                      
                      Table altered.
                      
                      SQL> 
                      Same way we exchange partition SYS_P120:
                      SQL> truncate table FLX_TIME2;
                      
                      Table truncated.
                      
                      SQL> alter table FLX_TIME1 exchange partition SYS_P120 with table FLX_TIME2;
                      
                      Table altered.
                      
                      SQL> lock table FLX_TIME1_ARCHIVE
                        2    partition for(TO_DATE(' 2013-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') - 1)
                        3    in share mode;
                      
                      Table(s) Locked.
                      
                      SQL> alter table FLX_TIME1_ARCHIVE exchange partition
                        2    for(TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') - 1)
                        3    with table FLX_TIME2;
                      
                      Table altered.
                      
                      SQL> 
                      Now:
                      SQL> select  count(*)
                        2    from  FLX_TIME1 partition(day0)
                        3  /
                      
                        COUNT(*)
                      ----------
                               8
                      
                      SQL> select  count(*)
                        2    from  FLX_TIME1 partition(sys_p119)
                        3  /
                      
                        COUNT(*)
                      ----------
                               0
                      
                      SQL> select  count(*)
                        2    from  FLX_TIME1 partition(sys_p120)
                        3  /
                      
                        COUNT(*)
                      ----------
                               0
                      
                      SQL> select partition_name from user_tab_partitions where table_name='FLX_TIME1_ARCHIVE';
                      
                      PARTITION_NAME
                      ------------------------------
                      DAY0
                      SYS_P121
                      SYS_P122
                      
                      SQL> select  count(*)
                        2    from  FLX_TIME1_ARCHIVE partition(day0)
                        3  /
                      
                        COUNT(*)
                      ----------
                               0
                      
                      SQL> select  count(*)
                        2    from  FLX_TIME1_ARCHIVE partition(sys_p121)
                        3  /
                      
                        COUNT(*)
                      ----------
                              31
                      
                      SQL> select  count(*)
                        2    from  FLX_TIME1_ARCHIVE partition(sys_p122)
                        3  /
                      
                        COUNT(*)
                      ----------
                              11
                      
                      SQL> 
                      SY.
                      • 8. Re: all rows in table do not qualify for specified partition
                        rp0428
                        >
                        Well, exchange syntax and logic is not that clear as it should (IMHO). There is no syntax element that tells Oracle do we want to exchange partition data into a non-partitioned table or non-partitioned table data into partition.
                        >
                        What? Not sure where you got that but maybe it was before you had you first cup of coffee? ;)

                        An 'exchange' is just that; an exchange. The partition data ends up in the non-partitioned table and vice versa. It is meaningless as well as totally irrevelant whether 'we want to exchange partition data into a non-partitioned table or non-partitioned table data into partition'.
                        >
                        It is decided by Oracle checking non-partitioned table. If non-partitioned table is empty then it means you want to exchange partition data into a non-partitioned table. And if non-partitioned table is not empty then it means you want to exchange non-partitioned table data into partition.
                        >
                        There is no decision to make. The segments are swapped. Oracle doesn't check, know, or even care if either or both segments are empty. The only 'checking' that is done (by default unless NO CHECK is used) is to ensure that any data in the segment destined for the partition belongs to that partition.

                        That is what John's example is showing. This can NEVER work if table1 is empty unless p1 is empty at the start:
                        1. exchange p1 with table1
                        2. exchange p2 with table1

                        As John explained assuming no other operations take place that tries to put the p1 data into p2 which is not possible if p1 actually contains data.

                        You might want to save this one for next April 1!
                        • 10. Re: all rows in table do not qualify for specified partition
                          973776
                          Thanks Solomon for the solution.
                          It worked and the data results are proper as expected