Hi,

This is the scenario:
Consider Parent_table that is list partitioned based on dept_no - with partition_name specified as 'P_Dept_no'.
Parent_table -> Child_table_1 (reference_partitioned based on Parent_Table) -> Child_table_2 (reference_partitioned based on Child_Table_1) -> Child_Table_3 (reference_partitioned based on Child_Table_2)

The problem occurs at Child_table_3. For a few partitions, the partition_name in Child_table_3 is getting created as 'SYS_P' partitions.
Since we are doing some operations based on partition_names, we tried to correct this :

Tried dropping and re-creating the Child_table_3 :-
1.either the same set of child partitions get re-created as 'SYS_P' or
2.child partitions which were earlier 'SYS_P' gets re-created with proper partition_name (corresponding to 'P_Dept_no'), while a different set of child partitions which were properly named earlier gets re-created as 'SYS_P' .
Any idea why this happens?

Tried to rename the partition of Child_table_3:
The concern here was that we were using partition_position of Parent_table to identify the correct partition_name. Can this be guaranteed to remain same across Parent and Child tables?
Also, when we rename the partition of Child_table_3, the 'partition_name' in '*_ind_partitions', '*_ind_statistics' etc still remained as 'SYS_P'. Is this the expected result? will it affect any operations performed on the table if we don't update the metadata tables as well?

or is there any solution for this scenario?

Update: we came across the scenario where in if the parent table partition is split, then the corresponding partition in child table will get created as 'SYS_P', but we have not explicitly split parent partition anytime. can this happen as an internal oracle action? Also, if this is the case, wouldn't the drop and recreate strategy create with proper name?

Update: Found this issue as Bug 15996848 in Oracle which is fixed in 12C. So please ignore the earlier question.

To fix this issue, we will alter table and index to rename partition. The concern here was that we were using partition_position of Parent_table to identify the correct partition_name. Can this be guaranteed to remain same across Parent and Child tables?
Also, the field parent_table_partition in ALL_TAB_PARTITIONS is empty for all the referenced child partitions. I thought this will be populated with corresponding parent table partition. Is this not the case?

SQL> @drop child

Y1                      Y2
----------------------- -------------------------
TABLE                   cascade constraints purge

1 row selected.


Table dropped.

SQL> @drop parent

Y1                      Y2
----------------------- -------------------------
TABLE                   cascade constraints purge

1 row selected.


Table dropped.

SQL>
SQL> create table parent (
  2    id           number not null,
  3    constraint pk  primary key (id)
  4  )
  5  partition by range (id)
  6  (
  7     partition p1 values less than (10),
  8     partition p2 values less than (20),
  9     partition p3 values less than (30),
 10     partition p4 values less than (40)
 11  );

Table created.

SQL>
SQL> insert into parent select rownum from dual connect by level < 40;

39 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> create table child (
  2    id             number not null,
  3    parent_id  number not null,
  4    constraint fk foreign key (parent_id)
  5                                 references parent (id)
  6  )
  7  partition by reference (fk);

Table created.

SQL>
SQL> insert into child select rownum, rownum
  2  from dual
  3  connect by level <40;

39 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> alter table child drop partition p3;
alter table child drop partition p3
                                 *
ERROR at line 1:
ORA-14255: table is not partitioned by range, list, composite range, or composite list method


SQL>
SQL> alter table parent drop partition p3;

Table altered.

SQL>