Hi,
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
We have a non partitioned table T1 in a default tablespace 'A' along with huge data.
I was trying to partition this table where the new table partitions will be in a different tablespace 'B' .
When I tried exchange partition, I found I cannot change the existing data tablespace from 'A' to 'B'.
Do we have any other options to change this exiting data from tablespace 'A' to 'B' other than migrating the data?
CREATE TABLE MSH_TEST TABLESPACE T_DATA AS
SELECT * FROM ORIG_TAB WHERE ROWNUM<10;
CREATE TABLE MSH_AUXILIARY_TABLE_P
PARTITION BY RANGE
( CAL_DATE_KEY )
(
PARTITION P_TEST_999901 VALUES LESS THAN (99990101) TABLESPACE T_DATA_2018
)
AS
SELECT * FROM MSH_TEST where 1=2;
select segment_name, partition_name,
tablespace_name, extent_id
from user_extents
where segment_name in ( 'MSH_TEST','MSH_AUXILIARY_TABLE_P' );
alter table MSH_AUXILIARY_TABLE_P
exchange partition P_TEST_999901
with table MSH_TEST;
Regards
Hesh