Transportable Tablespaces for Data Archival ORA-39901 ORA-39921

From: Hall, Liz <Liz.Hall_at_imaginecommunications.com>
Date: Wed, 23 Jun 2021 15:30:57 +0000
Message-ID: <CY4PR17MB2037313CBA35A0F3A200BE039E089_at_CY4PR17MB2037.namprd17.prod.outlook.com>

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 Received on Wed Jun 23 2021 - 17:30:57 CEST