Re: Transportable Tablespaces for Data Archival ORA-39901 ORA-39921
From: Mark Burgess <mark_at_burgess-consulting.com.au>
Date: Fri, 25 Jun 2021 05:29:57 +1200
Message-ID: <f11d8615-f463-4e2e-a2a3-c53e8ebbdace_at_Spark>
On 24 Jun 2021, 3:31 AM +1200, Hall, Liz <Liz.Hall_at_imaginecommunications.com>, 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_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 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/********_at_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
>
>
Date: Fri, 25 Jun 2021 05:29:57 +1200
Message-ID: <f11d8615-f463-4e2e-a2a3-c53e8ebbdace_at_Spark>
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_at_imaginecommunications.com>, 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_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 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/********_at_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
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 24 2021 - 19:29:57 CEST