SQL Developer

[EDITED on 25 April 2023, in order to restore text formats lost in the migration to the new Forums software.]

Hello,

SQL Developer version: 21.4.2

(But definitely not a new issue: reproduced on 17.4.1)

The Database Diff tool seems to ignore differences in subpartitions and/or subpartition templates, making it appear as if the compared table had the same subpartition structure on both the source and destination databases, whereas there are actual differences in subpartitions and/or in the subpartition template.

Test case: the test table, TSTWITHSUBPART, with range-list composite subpartitioning, is maintained in the SCOTT schema on 2 distinct databases. A new subpartition is created on the source DB but not yet on the destination.

----vvv-- Step A: run this on both the source AND the destination --vvv--
--
create table scott.tstwithsubpart (
    c1  number          not null,
    c2  varchar2(10)    not null,
    c3  varchar2(50)
)
partition by range (c1)
subpartition by list (c2)
subpartition template (
    subpartition aa     values ('AA'),
    subpartition bb     values ('BB'),
    subpartition other  values (default)
) (
    partition p1k       values less than (1000),
    partition p2k       values less than (2000),
    partition pdefault  values less than (maxvalue)
);

----vvv-- Step B: on the source only --vvv--
--
alter table scott.tstwithsubpart
set subpartition template (
    subpartition aa     values ('AA'),
    subpartition bb     values ('BB'),
    subpartition cc     values ('CC'),
    subpartition other  values (default)
);

----vvv-- Step C: on the source only --vvv--
--
alter table scott.tstwithsubpart split subpartition p1k_other values ('CC') into ( subpartition p1k_cc, subpartition p1k_other);
alter table scott.tstwithsubpart split subpartition p2k_other values ('CC') into ( subpartition p2k_cc, subpartition p2k_other);
alter table scott.tstwithsubpart split subpartition pdefault_other values ('CC') into ( subpartition pdefault_cc, subpartition pdefault_other);

On the source connection, the above script was run entirely; on the destination, only the step A was run. We expect the Database Diff tool to detect the new subpartitions and the changed subpartition template.

Diff settings:

Diff Report: the table is shown as identical on both connections.

This is obviously wrong, as there is no subpartition for 'CC' in the destination, nor the corresponding entry in the subpartition template.

(Hence the DDL on the right side is merely a copy of the DDL on the left side, not the actual DDL: SQL Developer does not bother generating the DDL twice once it has determined that the table metadata are identical on both sides—unfortunately, in this case the metadata comparison failed to correctly take subpartitions into account.)

Tracing the sessions (or simply reviewing the SQL statements in the Statements - Log panel) shows that SQL Developer retrieves the "raw" metadata in XML format from both sides by calling the DBMS_METADATA.GET_XML function (and then compares the metadata structures). Manually diffing the XML readout of that function confirms that there are actual differences in <SUBPARTS_ITEM> and <TSUBPARTS_ITEM> elements, which seem to have been ignored.

Remark: above we had differences in both the subpartition template and the actual subpartitions. Doing the comparison with only either one results in the same outcome.

Regards,