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

You're welcome. Two minor things:  

1. I wrote "full list of partitions" when I should have written "full list of datafiles for the tablespaces for the partitions". (You can move a set, but you have to move the whole set. There does not exist a syntax to transport things that are self-contained at the partition level.
2. IF you know you are going to do a time based archival purge before you create a given partition, if you put individual partitions each in their own tablespace, the exchange still must be done but the data does not need to be "moved," and if your partition granularity is, for example, daily, but your archive-purge cycle is annually (often after 7 years in practice), if you put all the daily partitions "days" for a year into one partition for the year or 12 "monthly" partitions (still for the year), you can do those exchanges and the single datafile tablespace or multiple datafile tablespace by exchange in place using the single datafile or datafile comma list syntax. You can switch to that strategy for the future, keeping in mind the "death by inches" problem of slicing datafiles and tablespaces too small.

You knew that your time based partitions were logically internally complete, but for Oracle to allow that in the general case is annoyingly complex.  

IF you have to "move" all the data anyway, it is unclear whether transportable tablespaces or "UNLOAD" into a format that can't be loaded elsewhere (presumably your archive database) via loader or inserting from an external table is faster when all your columns are legal to unload and suck back in. This is less work on the "production" database. If the initial image of the archive is on the same campus with a high speed connection and you need to avoid the insert work on "production" insert from across the link may also work.  

Good luck,  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hall, Liz
Sent: Thursday, June 24, 2021 6:18 PM
To: Mark W. Farnham; oracle-l_at_freelists.org Subject: RE: Transportable Tablespaces for Data Archival ORA-39901 ORA-39921  

Mark ,  

Thank you for your reply. I did not understand the whole process. Your comment about partition exchanged made me rethink the process and my work.  

Solution:

To transport the old tablespace/partition you MUST move it to a discrete table and do a partition exchange with the original table in order to transport it.  

In hindsight its obvious. Thank you for nudging me along to the solution!  

Liz          

From: Mark W. Farnham <mwf_at_rsiz.com> Sent: Wednesday, June 23, 2021 9:54 AM
To: Hall, Liz <Liz.Hall_at_imaginecommunications.com>; oracle-l_at_freelists.org Subject: 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