RE: Transportable Tablespaces for Data Archival ORA-39901 ORA-39921
Date: Thu, 24 Jun 2021 22:17:46 +0000
Message-ID: <CY4PR17MB2037B9742C391010732DD5D19E079_at_CY4PR17MB2037.namprd17.prod.outlook.com>
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> [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<mailto: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;
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 Fri Jun 25 2021 - 00:17:46 CEST