Messed Up and Duplicated Info in TRANSPORT_SET_VIOLATIONS

(Doc ID 1269833.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.4 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.

Symptoms

When analyzing if a tablespace is self contained by running dbms_tts.transport_set_check and there are some constraints involved, the output reported in transport_set_violations table shows entries that make no sense. Looks like messages reported is a cartesian product of constraints violating self-contained restriction.

This testcase shows the wrong behavior:

create tablespace test_a DATAFILE '/opt/oracle/TESTDB/oradata1/TESTDB_test_a_01.dbf' size 10M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

create tablespace test_b DATAFILE '/opt/oracle/TESTDB/oradata1/TESTDB_test_b_01.dbf' size 10M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

create user test identified by test quota unlimited on test_a, test_b;
connect test/test

create table testcase_table( id1 number, id2 number ) tablespace test_a;
create table fk_table_1(id number, constraint PK_fk_table_1 primary key (id)) tablespace test_b;
create table fk_table_2(id number, constraint PK_fk_table_2 primary key (id)) tablespace test_b;

alter table testcase_table add constraint pk_testcase_table primary key (id1,id2) using index
tablespace test_a;

alter table testcase_table add constraint fk_id1 foreign key (id1) references fk_table_1(id) ON DELETE CASCADE;
alter table testcase_table add constraint fk_id2 foreign key (id2) references fk_table_2(id) ON DELETE CASCADE;

connect / as sysdba
exec dbms_tts.transport_set_check( 'TEST_A', true, false );

select * from transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
Constraint FK_ID2 between table TEST.FK_TABLE_1 in tablespace TEST_B and table TEST.TESTCASE_TABLE in tablespace TEST_A
Constraint FK_ID1 between table TEST.FK_TABLE_1 in tablespace TEST_B and table TEST.TESTCASE_TABLE in tablespace TEST_A
Constraint PK_TESTCASE_TABLE between table TEST.FK_TABLE_1 in tablespace TEST_B and table TEST.TESTCASE_TABLE in tablespace TEST_A
Constraint FK_ID2 between table TEST.FK_TABLE_2 in tablespace TEST_B and table TEST.TESTCASE_TABLE in tablespace TEST_A
Constraint FK_ID1 between table TEST.FK_TABLE_2 in tablespace TEST_B and table TEST.TESTCASE_TABLE in tablespace TEST_A
Constraint PK_TESTCASE_TABLE between table TEST.FK_TABLE_2 in tablespace TEST_B and table TEST.TESTCASE_TABLE in tablespace TEST_A


Any set of constraints (even not null ones) will be reported as restriction violated

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms