version: 18.7.0.0.0
So I'm testing the CTAS for exchange command but I keep getting in trouble.
Do you see where the issue might be?
# create range-partitioned table
create table aj_log3 (
log_id number constraint aj_log3_id_pk primary key,
log_event varchar2(30),
log_qty number,
log_date date
)
partition by range (log_date)
(
partition aj_log3_date_p01 values less than (to_date('21.11.2019','DD.MM.YYYY')),
partition aj_log3_date_p02 values less than (to_date('22.11.2019','DD.MM.YYYY')),
partition aj_log3_date_p03 values less than (to_date('23.11.2019','DD.MM.YYYY')),
partition aj_log3_date_p04 values less than (to_date('24.11.2019','DD.MM.YYYY')),
partition aj_log3_date_p05 values less than (to_date('25.11.2019','DD.MM.YYYY'))
);
# load partition 1
begin
for i in 1..200000
loop
insert into aj_log3 (log_id, log_event, log_qty, log_date) values (aj_log3_seq.nextval, 'event1', dbms_random.value(1,1000), sysdate);
end loop;
end;
/
# load partition 2
begin
for i in 1..200000
loop
insert into aj_log3 (log_id, log_event, log_qty, log_date) values (aj_log3_seq.nextval, 'event2', dbms_random.value(1,1000), sysdate+1);
end loop;
end;
/
# create table for exchange
create table aj_log3_staging for exchange with table aj_log3;
# insert 1 row in staging table
insert into aj_log3_staging values (aj_log3_seq.nextval, 'event3', 2, sysdate+1);
# exchange partition aj_log3_date_p02 with staging table
alter table aj_log3 exchange partition aj_log3_date_p02 with table aj_log3_staging;
alter table aj_log3 exchange partition aj_log3_date_p02 with table aj_log3_staging
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
Thanks
AJ