ORA-39921 & ORA-39919 DURING TTS

(Doc ID 2286906.1)

Last updated on OCTOBER 23, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.7 and later
Information in this document applies to any platform.

Symptoms

SQL> SELECT segment_name,partition_name,segment_type,tablespace_name
2 FROM dba_segments
3 WHERE segment_name IN ((SELECT table_name
4 FROM dba_tables
5 WHERE iot_name = 'T_METADATA_OLD' and owner='WCA'));
.
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
---------------- --- ------------------------------ ------------------ ------------------------------
SYS_IOT_OVER_102623 SYS_P2005 TABLE PARTITION WCA_DATA01
SYS_IOT_OVER_102623 SYS_P2006 TABLE PARTITION WCA_DATA01
SYS_IOT_OVER_102623 SYS_P2068 TABLE PARTITION WCA_DATA01

SQL> EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'WCA_DATA01', incl_constraints => TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM sys.transport_set_violations;

VIOLATIONS
------------------------------------------------------------------------------
------------------------------------------------------------------------------
--------------------------------------------
ORA-39921: Default Partition (Table) Tablespace USERS for SYS_IOT_OVER_102623
not contained in transportable set.
ORA-39919: Object WCA.SYS_IOT_OVER_102623 in tablespace USERS not contained
within transportable set.

All the three overflow tables are not residing on Tablespace SYSTEM, but TRANSPORT_SET_VIOLATIONS reporting it resides on SYSTEM Tablespace.

OVERFLOW TABLE with TABLESPACE_NAME
-----------------------------------------------------
SQL> select distinct segment_name,tablespace_name from dba_segments where segment_name in ('SYS_IOT_OVER_81761','SYS_IOT_OVER_84144','SYS_IOT_OVER_81359');

SEGMENT_NAME TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------
SYS_IOT_OVER_81359 WCA_DATA01
SYS_IOT_OVER_84144 WCA_DATA01
SYS_IOT_OVER_81761 WCA_DATA01

OVERFLOW TABLE with Original IOT Table name
-----------------------------------------------------------
SQL> Select table_name,iot_name from dba_tables where table_name in ('SYS_IOT_OVER_81761','SYS_IOT_OVER_84144','SYS_IOT_OVER_81359');

TABLE_NAME IOT_NAME
------------------------------ ------------------------------
SYS_IOT_OVER_81359 T_ATTRIBUTE
SYS_IOT_OVER_81761 T_AUTH_ATTRIBUTE
SYS_IOT_OVER_84144 T_METADATA_OLD


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