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@xxxxxxxx>
Sent: Wednesday, June 23, 2021 9:54 AM
To: Hall, Liz <Liz.Hall@xxxxxxxxxxxxxxxxxxxxxxxxx>; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Hall, Liz
Sent: Wednesday, June 23, 2021 11:31 AM
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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@us3-qa-icmt-xgldb-01 ~]$ expdp SYSTEM/xG@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
