Re: Transportable Tablespaces for Data Archival ORA-39901 ORA-39921

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_at_rsiz.com> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Hall, Liz
> *Sent:* Thursday, June 24, 2021 6:18 PM
> *To:* Mark W. Farnham; oracle-l_at_freelists.org
> *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_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 <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
> *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_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
>
>
>
>
>
>
>
>
>

-- 
Connor McDonald
===========================
blog:   connormcdonald.wordpress.com
twitter: _at_connor_mc_d

"If you are not living on the edge, you are taking up too much room."
- Jayne Howard

*Fine print: Views expressed here are my own and not necessarily that of my
employer*


--
http://www.freelists.org/webpage/oracle-l