With the advent of 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.
CREATE TABLE EMP_PART (
EMPNO NUMBER NOT NULL
,ENAME VARCHAR2(11) NOT NULL
,JOB VARCHAR2(10) NOT NULL
,MGR NUMBER
,HIREDATE DATE NOT NULL
,SAL NUMBER
,COMM NUMBER
,DEPTNO NUMBER NOT NULL
,CONSTRAINT chk_empno CHECK (empno between 1 and 208)
)
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 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 employee_id ,Last_name ,job_id, manager_id,HIRE_DATE, Salary, commission_pct, department_id
from employees WHERE department_id != 20;
COMMIT;
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 TABLE emp_part_exchange
FOR EXCHANGE WITH TABLE emp_part;
-- ==============================================================================================================================================INSERT INTO emp_part_exchange(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
SELECT employee_id ,Last_name ,job_id, manager_id,HIRE_DATE, Salary, commission_pct, department_id
from employees WHERE department_id = 20;
COMMIT;
Exchange Partition
ALTER TABLE emp_part
EXCHANGE PARTITION p_research
WITH TABLE emp_part_exchange;
-----------------------------------------------------------------------
We would get a check constraint mismatch to avoid this some manual work is required
-----------------------------------------------------------------------
ALTER TABLE emp_part_exchange
ADD CONSTRAINT chk_empno_ep
CHECK (empno between 1 and 208);
-----------------------------------------------------------------------
ALTER TABLE emp_part_exchange
ADD CONSTRAINT emp_part_pk_ep
PRIMARY KEY (empno);
---------------------------------------------------------------------
Retry it now, it should work.
ALTER TABLE emp_part
EXCHANGE PARTITION p_research
WITH TABLE emp_part_exchange;
-----------------------------------------------------------------------
Check the output
-----------------------------------------------------------------------
select * from emp_part;
Online conversion of non partitioned table to non-partitioned table
ORACLE 12.2 allows the conversion of a non partitioned table to a partitioned table using the ALTER TABLE command. The whole conversion can be done “online” meaning, that during the conversion DML operations against the table are allowed.
CREATE TABLE emp_dept
AS
SELECT e.employee_id ,e.last_name ,e.job_id ,e.manager_id, e.department_id
,e.hire_date ,e.salary ,d.department_name
FROM employees e
JOIN departments d ON (e.department_id = d.department_id);
---------------------------------------------------------------------
ALTER TABLE MODIFY
ALTER TABLE emp_dept MODIFY
PARTITION BY LIST (department_id) (
PARTITION P_DEPT10 VALUES (10)
,PARTITION P_DEPT20 VALUES (20)
,PARTITION P_DEPT30 VALUES (30)
,PARTITION P_DEPT40 VALUES (40)
,PARTITION P_DEFAULT VALUES (DEFAULT)
)