Home » RDBMS Server » Server Administration » Exchange Partition issue (11.2.0.1.0 Windos XP)
Exchange Partition issue [message #521766] Thu, 01 September 2011 20:43 Go to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Dear all,
The table tb_hxl_hash have two partitions,when i exchange the second partition,it raise error,why?

SQL> Alter Table tb_hxl_hash
  2  Exchange Partition P_HS1 With Table tb_hxl_test;

Table altered.

SQL> Alter Table tb_hxl_hash
  2  Exchange Partition P_HS2 With Table tb_hxl_test;
Exchange Partition P_HS2 With Table tb_hxl_test
                                    *
ERROR at line 2:
ORA-14099: all rows in table do not qualify for specified partition
Re: Exchange Partition issue [message #521767 is a reply to message #521766] Thu, 01 September 2011 20:48 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
14099, 00000, "all rows in table do not qualify for specified partition"
// *Cause:  There is at least one row in the non partitioned table which
//          does not qualify for the partition specified in the ALTER TABLE
//          EXCHANGE PARTITION
// *Action: Ensure that all the rows in the segment qualify for the partition.
//          Perform the alter table operation with the NO CHECKING option.
//          Run ANALYZE table VALIDATE on that partition to find out the
//          invalid rows and delete them.
Re: Exchange Partition issue [message #521769 is a reply to message #521767] Thu, 01 September 2011 21:35 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
Would you tell me how to slove it,? Thanks!
Re: Exchange Partition issue [message #521770 is a reply to message #521769] Thu, 01 September 2011 22:32 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
>Would you tell me how to slove it,? Thanks!
Your problem is very important to us.
All available operators are busy assisting other customers.
So please stand by while we research a custom solution for you.
Re: Exchange Partition issue [message #521776 is a reply to message #521769] Fri, 02 September 2011 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 59425
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
andy huang wrote on Fri, 02 September 2011 04:35
hi,
Would you tell me how to slove it,? Thanks!


Maybe you can follow what is in the "action" part of BlackSwan's post?

That is, remove from the table the rows that have nothing to do in the target partition.

Regards
Michel

Re: Exchange Partition issue [message #571211 is a reply to message #521776] Thu, 22 November 2012 03:30 Go to previous messageGo to next message
armneh
Messages: 4
Registered: November 2012
Location: India
Junior Member
Hi Michel,


How do i find the rows which are not eligible
"remove from the table the rows that have nothing to do in the target partition."
is there a way to find the rows ??
Re: Exchange Partition issue [message #571218 is a reply to message #571211] Thu, 22 November 2012 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 59425
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have the definition of the partitions then you should be able to know what are the allowed values and then you should be able to find what are the not-allowed values.

Regards
Michel
Re: Exchange Partition issue [message #571226 is a reply to message #571218] Thu, 22 November 2012 05:36 Go to previous messageGo to next message
armneh
Messages: 4
Registered: November 2012
Location: India
Junior Member
Thanks Michel ,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
Re: Exchange Partition issue [message #571232 is a reply to message #571226] Thu, 22 November 2012 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 59425
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course, it can't work, the table contains the data of your previous first partition and so can't go to the second partition.

Regards
Michel
Re: Exchange Partition issue [message #571235 is a reply to message #571232] Thu, 22 November 2012 06:05 Go to previous messageGo to next message
armneh
Messages: 4
Registered: November 2012
Location: India
Junior Member
This means every time i need to exchange new partition i need to create a new table.
Is there no other way, that every partition can be moved to a single table?
Creating a new table every time to move the old partitions , i think would not be a good solution.

Is there a way to achieve this in some other way?
Re: Exchange Partition issue [message #571244 is a reply to message #571235] Thu, 22 November 2012 07:03 Go to previous messageGo to next message
Michel Cadot
Messages: 59425
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This means every time i need to exchange new partition i need to create a new table.


It depends on what you want to do with the data of the first partition.

Quote:
Is there a way to achieve this in some other way?


What do you want to achieve? You didn't tell us.

Regards
Michel
Re: Exchange Partition issue [message #571374 is a reply to message #571244] Sun, 25 November 2012 01:14 Go to previous messageGo to next message
armneh
Messages: 4
Registered: November 2012
Location: India
Junior Member
Thanks Michel

The thing which i need to achieve is the data archiving.
We have created the partitions on the table(range interval) on a date condition as mentioned in my example above.
As the data gets old, the data needs to be moved to different database.
For this we would be exchanging partitions as they are classified as old data partitions.
But all the partitions would be moved to a single table on different database.
because the data would be required for inquiry sort of. So this should not be truncated.

[Updated on: Sun, 25 November 2012 01:15]

Report message to a moderator

Re: Exchange Partition issue [message #571376 is a reply to message #571374] Sun, 25 November 2012 01:26 Go to previous message
Michel Cadot
Messages: 59425
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But all the partitions would be moved to a single table on different database.


The solution is to have 2 tables, a partitioned one to hold the archived data and an intermediate table to exchange: you exchange partitions between them as you did for the first table:
- exchange partition 1 of table 1 with intermdeiate table, exchange intermediate table with partition X of table 2;
- exchange partition 2 of table 1 with intermediate table...

Regards
Michel

[Updated on: Sun, 25 November 2012 01:33]

Report message to a moderator

Previous Topic: the status of the online logfile
Next Topic: Difference between bytes & user_bytes in dba_data_files
Goto Forum:
  


Current Time: Fri Oct 24 07:51:47 CDT 2014

Total time taken to generate the page: 0.11109 seconds