Full demo here
https://asktom.oracle.com/pls/apex/asktom.search?tag=use-transportable-tablespace-to-archive-old-data
On Fri, Jun 25, 2021 at 7:46 PM Mark W. Farnham <mwf@xxxxxxxx> wrote:
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 ;<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
