Hi,
We have exchanged partition of the landing table with the history table using the below statement
ALTER TABLE lnd.hist_tbl EXCHANGE PARTITION v_partname WITH TABLE lnd.curr_tbl WITHOUT VALIDATION;
v_partname - max partition name fetched by using the below query
SELECT MAX(partition_name) KEEP (DENSE_RANK LAST ORDER BY PARTITION_POSITION) partition_name INTO v_partname
FROM user_tab_partitions
WHERE table_name = hist_tbl
GROUP BY table_name
ORDER BY 1;We want to append the current table data to the history table and empty the current table for each run.
But for each run, the partitions are getting swapped between the current table and history table.
In First run the data is getting moved to history partition and new data is getting added to the current table.
In second run the data is moving to the new history partition and current data is getting added to the previous history partition and it become current partition.
ie., if we run the batch 1,2,3,4,5,6, then current table contains 2,4,6 batch data and the history table contains 1,3,5 batch data.
We doesn't want this happen. But few tables in the same schema are updating fine as 6 batch in current table and 1,2,3,4,5 batches data in history table.
Can you please why this is happening and help us resolve this issue.
Thanks in advance.
Exchange partition all the rows in the named partition with all the rows in the other table:
create table hist (
x int
) partition by range (x) (
partition p0 values less than (10)
);
create table curr for exchange with table hist; --12.2 syntax
insert into curr values (1);
alter table hist exchange partition p0 with table curr;
insert into curr values (2);
alter table hist exchange partition p0 with table curr;
insert into curr values (3);
alter table hist exchange partition p0 with table curr;
insert into curr values (4);
alter table hist exchange partition p0 with table curr;
select * from hist;
X
2
4
select * from curr;
X
1
3
Which is almost certainly because your query to get the partition always returns the same value. Assuming you have partitioned by batch # in a way that places each one in its own partition, you can get around this using "partition for" syntax in the exchange. This identifies the partitions by the values that go in them instead of by name:
create table hist (
x int
) partition by range (x) (
partition p0 values less than (1),
partition p1 values less than (2),
partition p2 values less than (3),
partition p3 values less than (4),
partition p4 values less than (5)
);
create table curr (
x int
);
insert into curr values (1);
alter table hist exchange partition for (1) with table curr;
insert into curr values (2);
alter table hist exchange partition for (2) with table curr;
insert into curr values (3);
alter table hist exchange partition for (3) with table curr;
insert into curr values (4);
alter table hist exchange partition for (4) with table curr;
select * from hist;
X
1
2
3
4
select * from curr;
no rows selected