Exchange Partition issue [message #521766] |
Thu, 01 September 2011 20:43 |
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 #571374 is a reply to message #571244] |
Sun, 25 November 2012 01:14 |
|
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 |
|
Michel Cadot
Messages: 68716 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|