My Oracle Support Banner

How to use Partition Names for ref partitioning tables when Split is used (Doc ID 1304370.1)

Last updated on MARCH 11, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.

Goal

How to provide user create names for the partitions created by the split partition in a reference partitioned tables configuration?

For example:

create table <TABLE_NAME> (calldt date not null, mandt number not null, seqid number not null)
partition by range (calldt) subpartition by list (mandt)
SUBPARTITION TEMPLATE
(SUBPARTITION "100" values (100),
SUBPARTITION "540" values (540)
)
(
partition "SEP_2008" values less than (to_date('01102008','DDMMYYYY'))
);


ALTER TABLE sr_test add CONSTRAINT pk_sr_test PRIMARY KEY (mandt, seqid);

create table <TABLE_NAME2> (mandt number not null, seqid number not null, nix varchar2(5),
CONSTRAINT FK_sr_test2 FOREIGN KEY (MANDT, seqid)
REFERENCES SR_test (MANDT, seqid)
)
partition by reference (FK_sr_test2);

ALTER TABLE <TABLE_NAME> split PARTITION SEP_2008 AT (TO_DATE('01.09.2008','DD.MM.YYYY')) INTO (PARTITION AUG_2008, PARTITION SEP_2008) update global indexes;



This way system generated names will be used for new partitions in sr_test2:

See:

select partition_name,table_name from all_tab_partitions where table_name like 'SR_TEST%';
PARTITION_NAME TABLE_NAME
------------------------------ ------------------------------
AUG_2008_100 <TABLE_NAME2>
AUG_2008_540 <TABLE_NAME2>
SYS_P101 <TABLE_NAME2>
SYS_P102 <TABLE_NAME2>
AUG_2008 <TABLE_NAME>
SEP_2008 <TABLE_NAME>



Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.