With ORACLE 12.2 the create table statement has been enriched by the new “for exchange with” clause. This enables us to create a exchange table that is a clone of the partitioned table which is the target of a later exchange table command

The new clause takes care of datatypes, data scale and precision, NOT NULL constraints, hidden column, virtual columns, unused columns,  etc.

Indexes, constraints (other than NOT NULL) are not cloned.

Preparation:

-- =============================================================================
-- Create Partitioned Table
-- =============================================================================
CREATE TABLE EMP_PART ( 
   EMPNO    NUMBER(4,0)       NOT NULL
  ,ENAME    VARCHAR2(10 CHAR) NOT NULL
  ,JOB      VARCHAR2(9 CHAR)  NOT NULL
  ,MGR      NUMBER(4,0)
  ,HIREDATE DATE              NOT NULL
  ,SAL      NUMBER(7,2)
  ,COMM     NUMBER(7,2)
  ,DEPTNO   NUMBER(2,0)       NOT NULL
  ,CONSTRAINT chk_sal_comm_less_1000 CHECK (NVL(sal,0) + NVL(comm,0) < 10000)
  ,CONSTRAINT chk_empno CHECK (empno between 1000 and 9999)
) 
PARTITION BY LIST (deptno) (
   PARTITION p_accounting VALUES (10)
  ,PARTITION p_research   VALUES (20)
  ,PARTITION p_sales      VALUES (30)
  ,PARTITION p_default    VALUES (DEFAULT)
);

-- =============================================================================
-- Create Local Index
-- =============================================================================
CREATE INDEX emp_part_idx ON EMP_PART (hiredate)
   LOCAL (
      PARTITION p_accounting_hiredate_idx
     ,PARTITION p_research_hiredate_idx
     ,PARTITION p_sales_hiredate_idx
     ,PARTITION p_default_hiredate_idx
);

-- =============================================================================
-- Add a Primary Key
-- =============================================================================
ALTER TABLE emp_part 
   ADD CONSTRAINT emp_part_pk
   PRIMARY KEY (empno);

-- =============================================================================
-- Fill Data
-- =============================================================================
INSERT INTO emp_part (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
  FROM emp
 WHERE deptno != 20;
 
COMMIT;

-- =============================================================================
-- add a virtual column
-- =============================================================================
ALTER TABLE emp_part
   ADD total_income GENERATED ALWAYS AS (NVL(SAL,0) + NVL(COMM,0));

-- =============================================================================
-- set a column to be hidden
-- =============================================================================
ALTER TABLE emp_part MODIFY deptno INVISIBLE;

-- =============================================================================
-- set a column unused
-- =============================================================================
ALTER TABLE emp_part SET UNUSED COLUMN job;

If we wanted to load the data of department 20 using an exchange partition command with an existing table holding all those rows we somehow had to make sure that the table used for the exchange has the exact same structure, indexes, constraints, etc. as the partitioned table. The new clause in the create table command help us solving this (at least some of the issues).

Create exchange table:

-- =============================================================================
-- Create exchange table
-- =============================================================================
CREATE TABLE emp_part_exchange
  FOR EXCHANGE WITH TABLE emp_part;

-- =============================================================================
-- Fill exchange table w/o job as it is unused
-- =============================================================================
INSERT INTO emp_part_exchange(EMPNO,ENAME,MGR,HIREDATE,SAL,COMM,DEPTNO)
SELECT EMPNO,ENAME,MGR,HIREDATE,SAL,COMM,DEPTNO
 FROM emp
 WHERE deptno = 20;

-- =============================================================================
-- Check the table
-- =============================================================================
SELECT column_name
      ,data_type || 
       CASE data_type
          WHEN 'VARCHAR2' THEN '(' || data_length || ')'
          WHEN 'NUMBER' THEN NULLIF('(' || data_precision || ',' || data_scale || ')','(,)')
          WHEN 'DATE' THEN NULL
       END AS data_type
      ,nullable
      ,hidden_column
      ,virtual_column
  FROM user_tab_cols
 WHERE table_name = 'EMP_PART_EXCHANGE'
 ORDER BY segment_column_id; 

COLUMN_NAME                    DATA_TYPE       NULL HIDDEN  VIRTUAL
------------------------------ --------------- ---- ------- --------
EMPNO                          NUMBER(4,0)     N    NO      NO 
ENAME                          VARCHAR2(10)    N    NO      NO 
SYS_C00003_16123011:26:29$     VARCHAR2(9)     Y    YES     NO 
MGR                            NUMBER(4,0)     Y    NO      NO 
HIREDATE                       DATE            N    NO      NO 
SAL                            NUMBER (7,2)    Y    NO      NO 
COMM                           NUMBER (7,2)    Y    NO      NO 
DEPTNO                         NUMBER (2,0)    N    YES     NO 
TOTAL_INCOME                   NUMBER (,)      Y    NO      YES 


SELECT constraint_name, constraint_type, search_condition_vc
  FROM user_constraints
 WHERE table_name = 'EMP_PART_EXCHANGE';

CONSTRAINT_NAME                TYPE SEARCH_CONDITION_VC 
------------------------------ ---- --------------------------------------------------
SYS_C008877                    C    "DEPTNO" IS NOT NULL 
SYS_C008878                    C    "EMPNO" IS NOT NULL 
SYS_C008879                    C    "ENAME" IS NOT NULL 
SYS_C008880                    C    "HIREDATE" IS NOT NULL 

SELECT index_name, index_type, partitioned
  FROM user_indexes
 WHERE table_name = 'EMP_PART_EXCHANGE';

no rows selected

Exchange Partition?

So if we did an exchange partition at this point we would fail if the exchange table is not empty…

-- =============================================================================
-- Exchange Partition
-- =============================================================================
ALTER TABLE emp_part 
   EXCHANGE PARTITION p_research 
   WITH TABLE emp_part_exchange; 

Error starting at line : 1 in command -
ALTER TABLE emp_part 
 EXCHANGE PARTITION p_research 
 WITH TABLE emp_part_exchange
Error report -
SQL Error: ORA-14118: CHECK constraint mismatch in ALTER TABLE EXCHANGE PARTITION
14118. 00000 - "CHECK constraint mismatch in ALTER TABLE EXCHANGE PARTITION"
*Cause: The corresponding columns in the tables specified in the
 ALTER TABLE EXCHANGE PARTITION statement have CHECK constraint
 defined on them.
*Action: Ensure that the two tables do not have CHECK constraint
 defined on any column

Additional work to be done:

We still need to create constraints on the exchange table manually…

-- =============================================================================
-- Check constraint are not created but needed when exchanging the partition
-- =============================================================================
ALTER TABLE emp_part_exchange 
   ADD CONSTRAINT chk_sal_comm_less_1000_ep 
   CHECK (NVL(sal,0) + NVL(comm,0) < 10000);
 
ALTER TABLE emp_part_exchange 
   ADD CONSTRAINT chk_empno_ep 
   CHECK (empno between 1000 and 9999);

-- =============================================================================
-- Add primary key constraint as it is not created 
-- =============================================================================
ALTER TABLE emp_part_exchange 
   ADD CONSTRAINT emp_part_pk_ep
   PRIMARY KEY (empno);

After this is done the exchange partition is working.

-- =============================================================================
-- Exchange Partition
-- =============================================================================
ALTER TABLE emp_part 
   EXCHANGE PARTITION p_research 
   WITH TABLE emp_part_exchange; 

Table EMP_PART altered.

Conclusion:

The create table for exchange clause is nice but if would be even better if constraints would also be created on the clone. Furthermore the possibility to do a CTAS in combination with the for exchange clause would be on my wishlist.