How I can to exchange partition in ORACLE. ORA-14095: ALTER TABLE EXCHANGE requires a non-partitioned, non-clustered table

user_18361041 0
Viewed 97
create table TEST_TABLE_2
(
  report_month DATE,
  name varchar(128)
)
partition by list (REPORT_MONTH)
(
  partition TEST_PART_2022_05_31 values (TO_DATE(' 2022-05-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TEST_TABLESPACE,
  partition MONTH_UNKNOWN values (default)
    tablespace TEST_TABLESPACE
);



create table TEST_TABLE_1
(
  report_month DATE,
  name varchar(128)
)
partition by list (REPORT_MONTH)
(
  partition TEST_PART_2022_05_31 values (TO_DATE(' 2022-05-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TEST_TABLESPACE,
  partition MONTH_UNKNOWN values (default)
    tablespace TEST_TABLESPACE
  );

Advise me please, How I can to exchange partition TEST_PART_2022_05_31 from TEST_TABLE_2 with partition TEST_PART_2022_05_31 in TEST_TABLE_1?

WHen I exec this script

ALTER TABLE ADS.test_table_1
  EXCHANGE PARTITION TEST_PART_2022_05_31
  WITH TABLE ADS.test_table_2

I get Error: ORA-14095: ALTER TABLE EXCHANGE requires a non-partitioned, non-clustered table

partition partitioning optimization oracle sql
1 Answers

Are you looking for something like this?


create table t ( 
  c1, c2, c3
) partition by range ( c2 ) 
  interval ( interval '1' month ) (
    partition p0 values less than ( date'2022-02-01' )
  )  
as 
  select level, date'2022-01-01' + level, 'remove'
  from   dual
  connect by level <= 100;

create table temp 
  for exchange with table t;
  
select count(*) from temp;
0

alter table t 
  exchange partition p0 
  with table temp;

select count(*) from temp;
100
user_167713770
Related