8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Create Table for Exchange With a Partitioned Table in Oracle Database 12c Release 2 (12.2)

Make sure a table is compatible for an EXCHANGE PARTITION operation by using CREATE TABLE ... FOR EXCHANGE WITH TABLE in Oracle Database 12c Release 2 (12.2).

Partition Exchange

The Problem
CREATE TABLE ... FOR EXCHANGE WITH TABLE

The Problem

We have a partitioned table defined as follows.

drop table t1 purge;

create table t1 (
  id            number,
  code          varchar2(10),
  description   varchar2(50),
  created_date  date,
  data_1        varchar2(1000),
  data_2        varchar2(1000),
  constraint t1_pk primary key (id)
)
partition by range (created_date)
(
  partition t1_2017 values less than (date '2018-01-01'),
  partition t1_2018 values less than (date '2019-01-01')
);

At some point, unknown to us, the table is modified to hide the DATA_1 and DATA_2 columns. This could be achieved by marking the columns as unused or invisible. In this case we do one of each.

alter table t1 set unused (data_1);
alter table t1 modify data_2 invisible;

desc t1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 CODE                                               VARCHAR2(10)
 DESCRIPTION                                        VARCHAR2(50)
 CREATED_DATE                                       DATE

SQL>

Remember, functionality like in-database archiving automatically generates invisible columns, so you may not even realise you have them.

Now we want to perform an EXCHANGE PARTITION operation, so we create a new empty table as a copy of the destination table, populate it and attempt an exchange.

drop table t1_temp purge;

create table t1_temp as
select * from t1
where 1=2;

alter table t1_temp add constraint t1_temp_pk primary key (id);

begin
  insert into t1_temp values
    (1, 'one',   'description for one', date '2017-09-02');
  insert into t1_temp values
    (2, 'two',   'description for one', date '2017-09-02');
  insert into t1_temp values
    (3, 'three', 'description for one', date '2017-09-02');
  insert into t1_temp values
    (4, 'four',  'description for one', date '2017-09-02');
  commit;
end;
/

alter table t1
  exchange partition t1_2017
  with table t1_temp
  without validation
  update global indexes;
alter table t1
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION


SQL>

Notice the partition exchange fails because the table segment doesn't match the partition segment structure, even though is was created using a CREATE TABLE ... AS SELECT or (CTAS).

The only way to be sure the exchange will work is to make the segment structures match exactly.

CREATE TABLE ... FOR EXCHANGE WITH TABLE

Oracle 12.2 makes it easy to create the new empty table with the correct structure, thanks to the FOR EXCHANGE WITH TABLE clause. This clause duplicates a number internal settings and attributes including unusable columns, invisible columns, virtual columns etc.

drop table t1_temp purge;

create table t1_temp 
  tablespace users
  for exchange with table t1;

alter table t1_temp add constraint t1_temp_pk primary key (id);

begin
  insert into t1_temp values
    (1, 'one',   'description for one', date '2017-09-02');
  insert into t1_temp values
    (2, 'two',   'description for one', date '2017-09-02');
  insert into t1_temp values
    (3, 'three', 'description for one', date '2017-09-02');
  insert into t1_temp values
    (4, 'four',  'description for one', date '2017-09-02');
  commit;
end;
/

alter table t1
  exchange partition t1_2017
  with table t1_temp
  without validation
  update global indexes;

Table altered.

SQL>

Notice the partition exchange is now successful.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.