Wednesday, 8 November 2017

Create Table for Exchange With a Partitioned Table in Oracle Database 12c

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).
Oracle Database Tutorials and Materials, Oracle Database Certifications

◉ 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 (TO_DATE('01-JAN-2018','DD-MON-YYYY')),
  PARTITION t1_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019','DD-MON-YYYY'))
);

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);

INSERT INTO t1_temp VALUES ( 1, 'ONE',   'Description for ONE', TO_DATE('02-SEP-2017','DD-MON-YYYY'));
INSERT INTO t1_temp VALUES ( 2, 'TWO',   'Description for ONE', TO_DATE('02-SEP-2017','DD-MON-YYYY'));
INSERT INTO t1_temp VALUES ( 3, 'THREE', 'Description for ONE', TO_DATE('02-SEP-2017','DD-MON-YYYY'));
INSERT INTO t1_temp VALUES ( 4, 'FOUR',  'Description for ONE', TO_DATE('02-SEP-2017','DD-MON-YYYY'));
COMMIT;

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);

INSERT INTO t1_temp VALUES ( 1, 'ONE',   'Description for ONE', TO_DATE('02-SEP-2017','DD-MON-YYYY'));
INSERT INTO t1_temp VALUES ( 2, 'TWO',   'Description for ONE', TO_DATE('02-SEP-2017','DD-MON-YYYY'));
INSERT INTO t1_temp VALUES ( 3, 'THREE', 'Description for ONE', TO_DATE('02-SEP-2017','DD-MON-YYYY'));
INSERT INTO t1_temp VALUES ( 4, 'FOUR',  'Description for ONE', TO_DATE('02-SEP-2017','DD-MON-YYYY'));
COMMIT;

ALTER TABLE t1
  EXCHANGE PARTITION t1_2017
  WITH TABLE t1_temp
  WITHOUT VALIDATION
  UPDATE GLOBAL INDEXES;

Table altered.

SQL>