Oracle Database Free

DB: 23c FREE in Virtual Box (HR schema)

BANNER_FULL

"Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09"

I was attempting to migrate a partition of an Internal Data Partition to an External Table Partition. (After finally reading the fine documentation, this does not seem to be possible. )

One of the methods I've tried was to move an auto-created interval partition to an external table.

• If the command succeeds, the data is lost and that partition becomes RO (and it is now in the system tablespace)
• otherwise, “ORA-14354: operation not supported for a hybrid-partitioned table” is thrown

note make sure you remove the .dmp file before retry.

clear screen;
drop table if exists rn_7;
drop table if exists random_numbers;

prompt SETUP
create table random_numbers (
 r number(2) not null,
 s varchar2(10)
)
External partition attributes (
  type oracle_datapump
  default directory EXT_TABLE_DIR
)
partition by range(r) interval(1) (
 partition p0 values less than (0)
 )
;

insert into random_numbers
   select cast( dbms_random.value( 0, 10) as number(2)) r
       , cast( dbms_random.string('X',10) as varchar2(10) ) s
   from dual
   connect by level < 100
;
commit;

prompt if this runs, it works here but fails on the 2nd.
ALTER TABLE RANDOM_NUMBERS move partition for (7) external location ('test_exp_7.dmp');

Prompt step x - Creating External Table
create table rn_7
organization external
  (type oracle_datapump
   default directory EXT_TABLE_DIR location ('test_exp_7.dmp')
  )
as
  select *
  from random_numbers PARTITION for (7);

prompt SHOW DATA
select floor(r) f, count(*)
from random_numbers
group by f
order by f;

select * from rn_7;

PROMPT BEFORE ALTER
select * from random_numbers partition for (7);
select partition_name, tablespace_name, read_only
from user_tab_partitions p
where table_name = 'RANDOM_NUMBERS'
  and p.high_value_json.high_value = 8
;

ALTER TABLE RANDOM_NUMBERS move partition for (7) external location ('test_exp_7.dmp');

PROMPT AFTER ALTER
select * from random_numbers partition for (7);
select partition_name, tablespace_name, read_only
from user_tab_partitions p
where table_name = 'RANDOM_NUMBERS'
  and p.high_value_json.high_value = 8
;

insert into random_numbers values (7, 'hello');
commit;


PROMPT FIXUP
ALTER TABLE RANDOM_NUMBERS drop partition for (7);
insert into random_numbers values (7, 'hello');
commit;
select * from random_numbers partition for (7);