RE: Transportable Tablespaces for Data Archival ORA-39901 ORA-39921

If memory serves you just need to give the check set the full list of partitions with commas. You cain't do them or check them one piece at a time if there are multiple tablespaces involved.  

Now if you are directly trying to "swap out and archive" an individual partition at a time, the best way I know of is to use partition exchange
(unless you have global indexes, this is a huuge win) and slap the
resulting table (which going forward would already be born in the tablespace you want to transport later) into the tts.      

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hall, Liz
Sent: Wednesday, June 23, 2021 11:31 AM
To: oracle-l_at_freelists.org
Subject: Transportable Tablespaces for Data Archival ORA-39901 ORA-39921  

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.1

Below 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

BEGIN FOR 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_at_us3-qa-icmt-xgldb-01 ~]$ expdp SYSTEM/xG_at_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 2021

Version 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 - Production

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": sYSTEM/********_at_XGLDB DUMPFILE=ttsfy1.dmp DIRECTORY=trans_dir TRANSPORT_TABLESPACES=ttsdat1

ORA-39396: Warning: exporting encrypted data using transportable option without password  

Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER

ORA-39123: Data Pump transportable tablespace job aborted

ORA-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        

--
http://www.freelists.org/webpage/oracle-l