I tried what you suggested with my own example, but for some reason I'm missing a child row.

create table parent
(
parent# number primary key,
data varchar2(3)
)
enable row movement
partition by range(parent#)
(partition p100 values less than (100),
partition p200 values less than (200),
partition p300 values less than (300),
partition p400 values less than (400),
partition pmax values less than (maxvalue)
)
/

insert into parent values(50,'ABC');
insert into parent values(150,'DEF');
insert into parent values(250,'GHI');
insert into parent values(350,'JKL');
insert into parent values(450,'JKL');

create table child
( parent# number not null,
child# number,
data varchar2(3),
constraint c1_pk
primary key(parent#,child#),
constraint c1_fk_p
foreign key(parent#)
references parent
on delete cascade
)
enable row movement
partition by reference(c1_fk_p)
/

insert into child values( 50,1,'A');
insert into child values(150,1,'A');
insert into child values(350,1,'A');
insert into child values(450,1,'A');

exec dbms_stats.gather_schema_stats('BBLADES');

col table_name format a10
col partition_name format a14
col partition_position format 9999 head "Partition|Position"
select table_name, partition_name, partition_position,
num_rows
from dba_tab_partitions
where table_owner='BBLADES'
and table_name in ('PARENT','CHILD')
order by table_name desc, partition_name;

Partition
TABLE_NAME PARTITION_NAME Position NUM_ROWS
---------- -------------- --------- ----------
PARENT P100 1 1
PARENT P200 2 1
PARENT P300 3 1
PARENT P400 4 1
PARENT PMAX 5 1

CHILD P100 1 1
CHILD P200 2 1
CHILD P300 3 0
CHILD P400 4 1
CHILD PMAX 5 1

select * from parent
order by parent#;

PARENT# DAT
---------- ---
50 ABC
150 DEF
250 GHI
350 JKL
450 JKL

select * from child
order by parent#, child#;

PARENT# CHILD# DAT
---------- ---------- ---
50 1 A
150 1 A
350 1 A
450 1 A

select c.*, u.subobject_name
from child c
join parent p
on c.parent# = p.parent#
join user_objects u
on dbms_rowid.rowid_object(p.rowid) = u.object_id
order by c.parent#;

PARENT# CHILD# DAT SUBOBJECT_NAME
---------- ---------- --- --------------
150 1 A P200
350 1 A P400
450 1 A PMAX

Where is parent# 50 ?

I think I see why the row is missing in my example.

The documentation for ROWID_OBJECT Function reads as follows:

This function returns the data object number for an extended ROWID.
The function returns zero if the input ROWID is a restricted ROWID.

In your example, you joined the value returned from rowid_object, which according to the documentation is the data object number (I assume they mean the DATA_OBJECT_ID), with the u.object_id.

select c.*, u.subobject_name
from t_chd c
join t_par p
on c.par_id = p.par_id
join user_objects u
on dbms_rowid.rowid_object(p.rowid) = u.object_id;

shouldn't it have been the u.data_object_id ?

SQL> create table t_par (
  2    par_id    int constraint pk primary key,
  3    ins_date  date not null)
  4  partition by range (ins_date) (
  5    partition p1 values less than (to_date('01-mar-2016', 'dd-mon-yyyy')),
  6    partition p2 values less than (to_date('01-apr-2016', 'dd-mon-yyyy')),
  7    partition p3 values less than (to_date('01-may-2016', 'dd-mon-yyyy')),
  8    partition p4 values less than (to_date('01-jun-2016', 'dd-mon-yyyy'))
  9    );

Table created.

SQL>
SQL> create table t_chd (
  2    par_id     int not null,
  3      x                     int not null,
  4    constraint fk foreign key (par_id) references t_par)
  5  partition by reference (fk);

Table created.

SQL>
SQL> select table_name, partition_name
  2  from user_tab_partitions
  3  where table_name in ('T_PAR','T_CHD')
  4  order by 1,2;

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
T_CHD                          P1
T_CHD                          P2
T_CHD                          P3
T_CHD                          P4
T_PAR                          P1
T_PAR                          P2
T_PAR                          P3
T_PAR                          P4

Connor, you are correct. In may case, the "parent" table RANGE partitioned and the 2 "child" tables are REFERENCE partitioned to it. When I create new "future" partitions by doing a partition SPLIT of the parent, the child partitions get the same name. In the general case, that might not be true, and someone can always rename the partitions and break the correspondence. I'd prefer to find the corresponding partitions using a connection that cannot be broken as was proposed in the prior suggestion.

The only thing that I don't think was correct in the original solution was the value returned from the dbms_rowid.rowid_object function. The documentation (PL/SQL packages 11g) for this function says it returns the "data object number". I assume this refers to either the OBJECT_ID or DATA_OBJECT_ID column in the ALL_OBJECTS / USER_OBJECTS view. When I tried the original solution using the OBJECT_ID in my example, a row was missing. When I modified the code and assumed the DATA_OBJECT_ID was returned by the function, it worked correctly. This was the SQL I used.

ANSI SQL

select c.*, u.subobject_name
from child c
join parent p
on c.parent# = p.parent#
join user_objects u
on dbms_rowid.rowid_object(p.rowid) = u.data_object_id
order by c.parent#;

Oracle SQL

select up.object_name, up.subobject_name,
uc.object_name, uc.subobject_name
from child c,
user_objects uc,
parent p,
user_objects up
where rownum < 2
and dbms_rowid.rowid_object(c.rowid) = uc.data_object_id
and dbms_rowid.rowid_object(p.rowid) = up.data_object_id
and c.parent#=p.parent#
and up.subobject_name = 'P200';

To finish this subject, I would like to get a confirmation on which value is returned by the rowid_object function, the OBJECT_ID or the DATA_OBJECT_ID.

I wonder if reference partitioning works within a rely novalidate fk ?
I have no computer right now to test it but just want to know if could works .

Team,

Is it possible to bring a composite partition technique from parent to child table using reference partition concepts?

in the below demo, T1 was composite partition got subpartitions there, but when T2 got reference partition implemented - all subpartitions from T1 got implemented as partitions in T2.

is it possible to get the same composite partition technique from T1 to T2 using reference partition concepts ?

demo@ATP19C> create table t1(x number primary key, y date )
  2  partition by range(y)
  3  subpartition by hash(x)
  4  subpartition template(
  5     subpartition sp1, subpartition sp2 )
  6  ( partition p2020 values less than
  7             ( to_date('01-jan-2020','dd-mon-yyyy')) ) ;

Table created.

demo@ATP19C> create table t2 (x number not null,
  2                     constraint t2_fk foreign key(x)
  3                     references t1 on delete cascade )
  4  partition by reference(t2_fk);

Table created.

demo@ATP19C> col table_name for a10
demo@ATP19C> col partition_name for a10
demo@ATP19C> col subpartition_name for a10
demo@ATP19C> select table_name,composite,partition_name
  2  from user_tab_partitions
  3  where table_name ='T1';

TABLE_NAME COM PARTITION_
---------- --- ----------
T1         YES P2020

demo@ATP19C> select table_name,partition_name, subpartition_name
  2  from user_tab_subpartitions
  3  where table_name ='T1';

TABLE_NAME PARTITION_ SUBPARTITI
---------- ---------- ----------
T1         P2020      P2020_SP1
T1         P2020      P2020_SP2

demo@ATP19C> select table_name,composite,partition_name
  2  from user_tab_partitions
  3  where table_name ='T2';

TABLE_NAME COM PARTITION_
---------- --- ----------
T2         NO  P2020_SP1
T2         NO  P2020_SP2

demo@ATP19C> select table_name,partition_name, subpartition_name
  2  from user_tab_subpartitions
  3  where table_name ='T2';

no rows selected

demo@ATP19C>

Thanks for getting back with those details. Appreciate that. it helps.