Search notes:

Oracle: ALTER TABLE EXCHANGE PARTITION

Creating a partitioned table. When I use the check constraint, I cannot execute the alter table … exchange with … stement. It's commented therefore.

create table tq84_data (
   dt            date not null, -- check (dt = trunc(dt) and dt between date '2020-01-01' and date '2024-12-31'),
   val           number(7,2),
   --
   constraint tq84_data_pk primary key (dt)
)
partition by range (dt) (
   partition tq84_data_2019 values less than (date '2020-01-01'),
   partition tq84_data_2020 values less than (date '2021-01-01'),
   partition tq84_data_2021 values less than (date '2022-01-01'),
   partition tq84_data_2022 values less than (date '2023-01-01'),
   partition tq84_data_2023 values less than (date '2024-01-01'),
   partition tq84_data_2024 values less than (date '2025-01-01')
);

begin
   insert into tq84_data
   select
      dt,
      case when dt between date '2022-06-10' and date '2022-09-04' then 0 else dbms_random.value(1000, 99999) end
   from (
      select
         date '2020-01-01' + level - 1 dt
      from
         dual connect by level <= date '2025-01-01' - date '2020-01-01'
   );

   commit;
end;
/

Uh oh, avg shows that data is skewed because of simulated wrong ETL between in a period in the year 2022

select
   to_char(dt, 'yyyy') year_,
   round(avg(val))     avg_val
from
   tq84_data
group by
   to_char(dt, 'yyyy')
order by
   year_;

create table to correct wrong data:

create table tq84_data_2022_correction (
   dt  date not null primary key,
   val number(7,2)
);

I thought, I could create the correction table with the following (now commented) create table … for exchange with table … statement, but this will throw ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION when I execute alter table … exchange partition below…

-- create table tq84_data_2022_correction for exchange with table tq84_data

Populate corrected data:

begin
   insert into tq84_data_2022_correction
   select
      date '2022-01-01' + level - 1 dt,
      dbms_random.value(1000, 99999) val
   from
      dual connect by level <= date '2023-01-01' - date '2022-01-01'
   ;

   commit;
end;
/

alter table tq84_data
   exchange partition tq84_data_2022
   with table tq84_data_2022_correction
   without validation
   update global indexes
;

Cleaning up

drop table tq84_data_2022_correction;
drop table tq84_data purge;

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1761418682, '216.73.216.115', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/Oracle/SQL/statement/nouns/table/alter/exchange-partition(118): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78

Oracle: ALTER TABLE EXCHANGE PARTITION

See also