This discussion is archived
10 Replies Latest reply: Nov 28, 2012 1:17 AM by 973776 RSS

all rows in table do not qualify for specified partition

973776 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    Thanks Solomon for the solution.
    It worked and the data results are proper as expected

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points