I am executing below command in oracle sql developer version 20.4
create table tmp for exchange with table src;
But facing issue missing or invalid option
Not sure if we can run exchange for command in oracle sql developer.
Can someone help or provide any insight if the syntax is wrong or is it possible to run this command
Your syntax looks fine, assuming that's a partitioned table, on 12.2 or higher of the database.
You don't mention your version of Oracle Database - that's very important as this is a relatively new feature.
Tim AKA Oracle-Base has a complete demo here.
Reproducing Tim's example on a 19c db (think of that as 12.2.X, the last update for the 12c product line) -
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')
);
create table t1_temp
/* tablespace users */
for exchange with table t1;
Last command as run in SQL Dev -
