You Asked

My first post, but my millionth read on this great site!

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);

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

 expdp sYSTEM/xxxx#@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/********@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

and Connor said...

The rule with partitioned tables is:

From the docs: https://docs.oracle.com/en/database/oracle/oracle-database/18/admin/transporting-data.html#GUID-1901E9C3-8FCE-4D4E-AB65-34D703474E52

The tablespace set you want to copy must contain either all partitions of a partitioned table, or none of the partitions of a partitioned table. If you want to transport a subset of a partition table, you must exchange the partitions into tables.

I will start with your position and work from there (I took the liberty of making your insert script more efficient)

SQL> CREATE TABLESPACE ttsdat1 DATAFILE 'X:\ORACLE\ORADATA\DB19\PDB1\D1.DBF'  SIZE 1M AUTOEXTEND ON MAXSIZE 50M;

Tablespace created.

SQL> CREATE TABLESPACE ttsdat2 DATAFILE 'X:\ORACLE\ORADATA\DB19\PDB1\D2.DBF'  SIZE 1M AUTOEXTEND ON MAXSIZE 50M;

Tablespace created.

SQL> CREATE TABLESPACE ttsdat3 DATAFILE 'X:\ORACLE\ORADATA\DB19\PDB1\D3.DBF'  SIZE 1M AUTOEXTEND ON MAXSIZE 50M;

Tablespace created.

SQL> CREATE TABLESPACE ttsdat4 DATAFILE 'X:\ORACLE\ORADATA\DB19\PDB1\D4.DBF'  SIZE 1M AUTOEXTEND ON MAXSIZE 50M;

Tablespace created.

SQL> CREATE TABLESPACE ttsdat5 DATAFILE 'X:\ORACLE\ORADATA\DB19\PDB1\D5.DBF'  SIZE 1M AUTOEXTEND ON MAXSIZE 50M;

Tablespace created.

SQL> CREATE SEQUENCE trans_id_seq CACHE 1000;

Sequence created.

SQL> CREATE TABLE txns (
  2     trans_id  NUMBER(12),
  3     trans_dt  DATE,
  4     from_acct CHAR(10),
  5     to_acct   CHAR(10),
  6     amount    NUMBER(12,2))
  7     tablespace ttsdat1
  8     PARTITION BY RANGE (trans_dt)
  9        ( PARTITION fy2017 VALUES LESS THAN (to_date('2018-01-01','yyyy-mm-dd') )
 10             TABLESPACE ttsdat1,
 11          PARTITION fy2018 VALUES LESS THAN (to_date('2019-01-01','yyyy-mm-dd') )
 12             TABLESPACE ttsdat2,
 13          PARTITION fy2019 VALUES LESS THAN (to_date('2020-01-01','yyyy-mm-dd') )
 14             TABLESPACE ttsdat3,
 15          PARTITION fy2020 VALUES LESS THAN (to_date('2021-01-01','yyyy-mm-dd') )
 16             TABLESPACE ttsdat4,
 17          PARTITION fy2021 VALUES LESS THAN (to_date('2022-01-01','yyyy-mm-dd') )
 18             TABLESPACE ttsdat5 );

Table created.

SQL>    INSERT INTO txns SELECT
  2        trans_id_seq.nextval,
  3        SYSDATE-ABS(dbms_random.random)/power(2,31)*365*4,
  4        SUBSTR(TO_CHAR(dbms_random.random,'000000000000'),-10,10),
  5        SUBSTR(TO_CHAR(dbms_random.random,'000000000000'),-10,10),
  6        TO_CHAR(ABS(dbms_random.random)/100000,'999999999.00') FROM dual
  7        connect by level <= 25000;

25000 rows created.

SQL>       COMMIT;

Commit complete.

SQL>
SQL> exec dbms_stats.gather_table_stats('','TXNS',cascade=> True);

PL/SQL procedure successfully completed.

SQL>
SQL> EXECUTE sys.DBMS_TTS.TRANSPORT_SET_CHECK('ttsdat1', TRUE);

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT * FROM sys.transport_set_violations order by 1;

VIOLATIONS
------------------------------------------------------------------------------------------------------------------------
ORA-39901: Partitioned table MCDONAC.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.

So a couple of things to sort out here

1) Get the archive partition into its own table

SQL> create table archive_txns_2017 tablespace ttsdat1
  2  for exchange with table txns;

Table created.

SQL> alter table txns exchange partition fy2017 with table archive_txns_2017;

Table altered.

SQL> EXECUTE sys.DBMS_TTS.TRANSPORT_SET_CHECK('ttsdat1', TRUE);

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT * FROM sys.transport_set_violations order by 1;

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

but there still seems to be a problem. See below

2) Fix the default attributes

As well as the partition definitions, there is a default tablespace setting for the table, which still points to TTSDATA1. We need to fix that.

SQL> alter table txns modify default attributes tablespace ttsdat2;

Table altered.

SQL> EXECUTE sys.DBMS_TTS.TRANSPORT_SET_CHECK('ttsdat1', TRUE);

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT * FROM sys.transport_set_violations order by 1;

no rows selected

and we're done.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database

Breadcrumb

Question and Answer

You Asked

and Connor said...

More to Explore

Administration

Use Transportable Tablespace to Archive old data

Breadcrumb

Question and Answer

You Asked

and Connor said...

More to Explore

Administration