My Oracle Support Banner

Messed Up and Duplicated Info in TRANSPORT_SET_VIOLATIONS (Doc ID 1269833.1)

Last updated on JUNE 05, 2019

Applies to:

Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

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

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
Symptoms
Cause
Solution
References


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