Try setting your default tablespace ttsdat1 to be different to your fy2017
partitions tablespace. This will require an additional tablespace. For example:
CREATE TABLE txns (
trans_id NUMBER(12),
trans_dt DATE,
from_acct CHAR(10),
to_acct CHAR(10),
amount NUMBER(12,2))
tablespace ttsdef
PARTITION BY RANGE (trans_dt)
( PARTITION fy2017 VALUES LESS THAN (to_date('2018-01-01','yyyy-mm-dd') )
TABLESPACE ttsdat1,
On 24 Jun 2021, 3:31 AM +1200, Hall, Liz <Liz.Hall@xxxxxxxxxxxxxxxxxxxxxxxxx>,
wrote:
Esteemed Listers,I am looking to use TTS to remove old data partitions from a
table. I have tried a test case so that I can understand how it works. Found
this example in Oracle Support and cannot get it to work. Doc ID
731559.1Below code, creates a date range partitioned table. The goal is to
move *the oldest* partition (FY2017 stored in tablespace ttsdat1) to a new
database. The transportable set check fails and I do not know why. See bottom
for the failures.What am I doing wrong? Why does the transport check fail?
I'm on 19c, ASM and Enterprise Edition. CREATE TABLESPACE ttsdat1 DATAFILE
'+data' SIZE 1M AUTOEXTEND ON MAXSIZE 50M;CREATE TABLESPACE ttsdat2 DATAFILE
'+data' SIZE 1M AUTOEXTEND ON MAXSIZE 50M;CREATE TABLESPACE ttsdat3 DATAFILE
'+data' SIZE 1M AUTOEXTEND ON MAXSIZE 50M;CREATE TABLESPACE ttsdat4 DATAFILE
'+data' SIZE 1M AUTOEXTEND ON MAXSIZE 50M;CREATE TABLESPACE ttsdat5 DATAFILE
'+data' SIZE 1M AUTOEXTEND ON MAXSIZE 50M;CREATE SEQUENCE trans_id_seq;
--drop table txns;
CREATE TABLE txns ( trans_id NUMBER(12), trans_dt DATE, from_acct
CHAR(10), to_acct CHAR(10), amount NUMBER(12,2)) tablespace
ttsdat1 PARTITION BY RANGE (trans_dt) ( PARTITION fy2017 VALUES LESS
THAN (to_date('2018-01-01','yyyy-mm-dd') ) TABLESPACE ttsdat1,
PARTITION fy2018 VALUES LESS THAN (to_date('2019-01-01','yyyy-mm-dd') )
TABLESPACE ttsdat2, PARTITION fy2019 VALUES LESS THAN
(to_date('2020-01-01','yyyy-mm-dd') ) TABLESPACE ttsdat3,
PARTITION fy2020 VALUES LESS THAN (to_date('2021-01-01','yyyy-mm-dd') )
TABLESPACE ttsdat4, PARTITION fy2021 VALUES LESS THAN
(to_date('2022-01-01','yyyy-mm-dd') ) TABLESPACE ttsdat5 );
--Load data
BEGINFOR i IN 1..25000 LOOP begin INSERT INTO txns SELECT
trans_id_seq.nextval, SYSDATE-ABS(dbms_random.random)/power(2,31)*365*4,
SUBSTR(TO_CHAR(dbms_random.random,'000000000000'),-10,10),
SUBSTR(TO_CHAR(dbms_random.random,'000000000000'),-10,10),
TO_CHAR(ABS(dbms_random.random)/100000,'999999999.00') FROM dual;
COMMIT; END LOOP;END;/ exec
dbms_stats.gather_table_stats('&yourschema','TXNS',cascade=> True); EXECUTE
sys.DBMS_TTS.TRANSPORT_SET_CHECK('ttsdat1', TRUE); SQL> SELECT * FROM
sys.transport_set_violations order by 1;
VIOLATIONS------------------------------------------------------------------------------------------------------------------------ORA-39901:
Partitioned table ECLIPSE.TXNS is partially contained in the transportable
set.ORA-39921: Default Partition (Table) Tablespace TTSDAT2 for TXNS not
contained in transportable set.ORA-39921: Default Partition (Table)
Tablespace TTSDAT3 for TXNS not contained in transportable set.ORA-39921:
Default Partition (Table) Tablespace TTSDAT4 for TXNS not contained in
transportable set.ORA-39921: Default Partition (Table) Tablespace TTSDAT5 for
TXNS not contained in transportable set. • Export fails same error
[oracle@us3-qa-icmt-xgldb-01 ~]$ expdp SYSTEM/xG@XGLDB DUMPFILE=ttsfy1.dmp
DIRECTORY=trans_dir TRANSPORT_TABLESPACES = ttsdat1 Export: Release
19.0.0.0.0 - Production on Mon Jun 21 15:56:02 2021Version 19.9.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -
ProductionStarting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":
sYSTEM/********@XGLDB DUMPFILE=ttsfy1.dmp DIRECTORY=trans_dir
TRANSPORT_TABLESPACES=ttsdat1ORA-39396: Warning: exporting encrypted data
using transportable option without password Processing object type
TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICSProcessing object type
TRANSPORTABLE_EXPORT/STATISTICS/MARKERORA-39123: Data Pump transportable
tablespace job abortedORA-39187: The transportable set is not self-contained,
violation list is ORA-39901: Partitioned table ECLIPSE.TXNS is partially
contained in the transportable set.ORA-39921: Default Partition (Table)
Tablespace TTSDAT2 for TXNS not contained in transportable set.ORA-39921:
Default Partition (Table) Tablespace TTSDAT3 for TXNS not contained in
transportable set.ORA-39921: Default Partition (Table) Tablespace TTSDAT4 for
TXNS not contained in transportable set.ORA-39921: Default Partition (Table)
Tablespace TTSDAT5 for TXNS not contained in transportable set.Job
"SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at Mon Jun
21 15:56:40 2021 elapsed 0 00:00:35
