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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Hall, Liz
Sent: Thursday, June 24, 2021 6:18 PM
To: Mark W. Farnham; oracle-l@xxxxxxxxxxxxx
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@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]
On Behalf Of Hall, Liz
Sent: Wednesday, June 23, 2021 11:31 AM
To: 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
