Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from smtp-aa.freelists.org (smtp-aa.freelists.org [23.23.80.81]) by malta2546.startdedicated.com (Postfix) with ESMTPS id 79E5E1003405EF for ; Wed, 7 Jul 2021 07:32:35 +0200 (CEST) Received: from turing.freelists.org (ip-10-0-0-164.ec2.internal [10.0.0.164]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by smtp-aa.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 5F3984101D; Wed, 7 Jul 2021 05:32:33 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 4FF113F97D; Wed, 7 Jul 2021 05:32:33 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1625635953; bh=SVTJ9wXmrHYnlBn5geVyA7tMfSRYFGLVt+PhnDPKy7I=; h=From:Sender:Sender:From; b=HaXpraZAIk8AqqVyqwh/8L1VAQ63L85QGrAMZXRHJhwj7XGiFQCcsqedV1dzPCIl1 eyABe/pRMuIqui+oO8lc5guJyGar111G5mZ8OSEqX8hUe2aaQrFvJQ/VbemwO6mw3e YUvhunzkL5WK9LJf7vptbVtS3DjLTOIPtXUya68s= X-Virus-Scanned: by FreeLists at turing2.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id kyrxmTCGN64M; Wed, 7 Jul 2021 05:32:33 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id E34BD3FDAE; Wed, 7 Jul 2021 05:32:30 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1625635951; bh=SVTJ9wXmrHYnlBn5geVyA7tMfSRYFGLVt+PhnDPKy7I=; h=From:Sender:Sender:From; b=FivPuonWuuASL9NXWiVdmSYKf1/482M3Fc+jtgp0l7van6wqsUff3mGR+d2M1XZ2B 64CKNrQ0m59StXU/GUmt3PlmGNsPcpThiKDNpjseS1H7NFi1dqyIrogrpmv7MjEpe3 MN1OP6veMbDUa+n0WI1xGm2OXNN4xvIZ7AR68txo= Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 07 Jul 2021 05:32:29 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 6520F3F97D for ; Wed, 7 Jul 2021 05:32:29 +0000 (UTC) Authentication-Results: turing.freelists.org; dkim=pass (2048-bit key; unprotected) header.d=gmail.com header.i=@gmail.com header.a=rsa-sha256 header.s=20161025 header.b=Dp2weYCp; dkim-atps=neutral Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id zzkHe76ZEc2C for ; Wed, 7 Jul 2021 05:32:29 +0000 (UTC) Received: from mail-yb1-f171.google.com (mail-yb1-f171.google.com [209.85.219.171]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by turing.freelists.org (Postfix) with ESMTPS id 4999C3F7E6 for ; Wed, 7 Jul 2021 05:32:29 +0000 (UTC) Received: by mail-yb1-f171.google.com with SMTP id i18so1261829yba.13 for ; Tue, 06 Jul 2021 22:32:29 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:cc; bh=bem7eEoenv+GNnlzEJTN5sF+/GVRssPyPd3STHGZsGU=; b=OAnvmicEi3P29f5t/0UKH4MdWGHAFdpks/dlvQayoV3OvhlfO3P0g0e/q5mgsx31RY knnmtwSalqAR9Y72dSQpR7+OGQxCft8gYb7dtAoiTXAhGUr6+SxFzhu/0tJ4RHa59tR8 Gwpdcdp2a/0ytHECUUYcI+8OJ+NE2wKgcuMcw0oJ6rZ8ocVLF/yCposLOgfEEpDiwyGe s5cte++py9iyMD+1Ap3ZgpQOYPM20qXu3b3uqXXTrXV6dkNi/QLpaCeyHcaRUV/VsMb0 Dl9b6Is4qThMwa12a5cLJzi2y0nLc4axDf635qdaLeiVl/EKfqofR+aAUEXQOCEfK7Xx IKlQ== X-Gm-Message-State: AOAM530s4/J6DxCAC7JozQ5RlIUplQge/DBeOhGPCsfePy0D4CkL1rsy Sru21AfjMUVbKGFcuxqaSK+co9GOGYah4WnciPa3/cluGZ8= X-Google-Smtp-Source: ABdhPJw3EUFhtcqv13K3Fqc08lMp2OKkMEqqjg7ElfCVoUKCAZ4owti09wRTHCmTn6z15aHvKztPUysuyHuy5B+SvHE= X-Received: by 2002:a25:b216:: with SMTP id i22mr27778235ybj.264.1625635948658; Tue, 06 Jul 2021 22:32:28 -0700 (PDT) MIME-Version: 1.0 References: <6a2301d76848$05f7d120$11e77360$@rsiz.com> <6c6601d769b6$eff78760$cfe69620$@rsiz.com> In-Reply-To: <6c6601d769b6$eff78760$cfe69620$@rsiz.com> From: Connor McDonald Date: Wed, 7 Jul 2021 13:32:18 +0800 Message-ID: Subject: Re: Transportable Tablespaces for Data Archival ORA-39901 ORA-39921 Cc: oracle-l Content-Type: multipart/alternative; boundary="00000000000041032f05c681dd66" X-archive-position: 80313 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: mcdonald.connor@gmail.com Precedence: normal Reply-To: mcdonald.connor@gmail.com List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-Subscribe: List-Owner: List-post: List-Archive: X-list: oracle-l --00000000000041032f05c681dd66 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Full demo here https://asktom.oracle.com/pls/apex/asktom.search?tag=3Duse-transportable-ta= blespace-to-archive-old-data On Fri, Jun 25, 2021 at 7:46 PM Mark W. Farnham wrote: > You=E2=80=99re welcome. Two minor things: > > > > 1) I wrote =E2=80=9Cfull list of partitions=E2=80=9D when I should hav= e written =E2=80=9Cfull > list of datafiles for the tablespaces for the partitions=E2=80=9D. (You c= an 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 n= ot > need to be =E2=80=9Cmoved,=E2=80=9D 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 =E2=80=9Cdays=E2=80=9D for= a year into one > partition for the year or 12 =E2=80=9Cmonthly=E2=80=9D 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 futur= e, > keeping in mind the =E2=80=9Cdeath by inches=E2=80=9D 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 =E2=80=9Cmove=E2=80=9D all the data anyway, it is unclear = whether > transportable tablespaces or =E2=80=9CUNLOAD=E2=80=9D into a format that = can=E2=80=99t 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 =E2=80=9Cproduction=E2=80=9D datab= ase. 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 =E2=80=9Cproduction= =E2=80=9D insert > from across the link may also work. > > > > Good luck, > > > > mwf > > > > *From:* oracle-l-bounce@freelists.org [mailto: > oracle-l-bounce@freelists.org] *On Behalf Of *Hall, Liz > *Sent:* Thursday, June 24, 2021 6:18 PM > *To:* Mark W. Farnham; oracle-l@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 > *Sent:* Wednesday, June 23, 2021 9:54 AM > *To:* Hall, Liz ; > oracle-l@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=E2=80=99t do them or check them one piec= e at a > time if there are multiple tablespaces involved. > > > > Now if you are directly trying to =E2=80=9Cswap out and archive=E2=80=9D = 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@freelists.org [ > mailto:oracle-l-bounce@freelists.org ] *On > Behalf Of *Hall, Liz > *Sent:* Wednesday, June 23, 2021 11:31 AM > *To:* oracle-l@freelists.org > *Subject:* Transportable Tablespaces for Data Archival ORA-39901 ORA-3992= 1 > > > > Esteemed Listers, > > I am looking to use TTS to remove old data partitions from a table. I hav= e > tried a test case so that I can understand how it works. Found this examp= le > 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=3D> 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=3Dttsfy1.dmp DIRECTORY=3Dtrans_dir TRANSPORT_TABLESPACES =3D t= tsdat1 > > > > 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=3Dttsfy1.dmp DIRECTORY=3Dtrans_dir TRANSPORT_TABLESPACES=3Dttsda= t1 > > 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 > > > > > > > > > --=20 Connor McDonald =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D blog: connormcdonald.wordpress.com twitter: @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* --00000000000041032f05c681dd66 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Fri, Jun 25, 2021 at 7:46 PM Mark W. Farnham <mwf@rsiz.com> wrote:

You=E2=80=99re welcome. Two minor things:=

=C2=A0

1)=C2=A0=C2=A0=C2=A0 I wrote =E2=80=9Cfull list of partitions=E2=80=9D when I s= hould have written =E2=80=9Cfull list of datafiles for the tablespaces for = the partitions=E2=80=9D. (You can move a set, but you have to move the whol= e set. There does not exist a syntax to transport things that are self-cont= ained at the partition level.

2)=C2=A0=C2= =A0=C2=A0 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 =E2=80=9Cmoved,=E2=80=9D and if your partition granularity i= s, for example, daily, but your archive-purge cycle is annually (often afte= r 7 years in practice), if you put all the daily partitions =E2=80=9Cdays= =E2=80=9D for a year into one partition for the year or 12 =E2=80=9Cmonthly= =E2=80=9D partitions (still for the year), you can do those exchanges and t= he single datafile tablespace or multiple datafile tablespace by exchange i= n place using the single datafile or datafile comma list syntax. You can sw= itch to that strategy for the future, keeping in mind the =E2=80=9Cdeath by= inches=E2=80=9D problem of slicing datafiles and tablespaces too small.

=C2=A0

You knew that your ti= me based partitions were logically internally complete, but for Oracle to a= llow that in the general case is annoyingly complex.

=C2=A0

IF you have to =E2=80=9Cmove=E2=80=9D all = the data anyway, it is unclear whether transportable tablespaces or =E2=80= =9CUNLOAD=E2=80=9D into a format that can=E2=80=99t be loaded elsewhere (pr= esumably your archive database) via loader or inserting from an external ta= ble is faster when all your columns are legal to unload and suck back in. T= his is less work on the =E2=80=9Cproduction=E2=80=9D database. If the initi= al image of the archive is on the same campus with a high speed connection = and you need to avoid the insert work on =E2=80=9Cproduction=E2=80=9D inser= t from across the link may also work.

=C2=A0=

Good luck,

=C2=A0

mwf

=C2=A0

From:<= /b> oracle-l-bounce@fr= eelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Hall= , Liz
Sent: Thursday, June 24, 2021 6:18 PM
To: Mark W.= Farnham; oracl= e-l@freelists.org
Subject: RE: Transportable Tablespaces for = Data Archival ORA-39901 ORA-39921

=C2=A0

Mark ,<= /u>

=C2=A0

Thank you for your reply.=C2=A0 I did not understand the whole pro= cess.=C2=A0 Your comment about partition exchanged made me rethink the proc= ess and my work.=C2=A0

=C2= =A0

Solution:

To transport the old =C2=A0tablespace/partition you MUST move i= t to a discrete table and do a partition exchange with the original table i= n order to transport it.

=C2= =A0

In hindsight its obvious.=C2=A0 Thank = you for nudging me along to the solution!

=C2=A0

Liz

=

=C2=A0

=C2=A0

=C2=A0

=C2=A0

=C2=A0

=

From: Mark W. Farnham <mwf@rsiz.com>
Sent: Wednesday, June 23, 2021= 9:54 AM
To: Hall, Liz <Liz.Hall@imaginecommunications.com>= ;; oracle-l@fre= elists.org
Subject: RE: Transportable Tablespaces for Data Ar= chival ORA-39901 ORA-39921

=C2=A0

If memory serves you just need to give the chec= k set the full list of partitions with commas. You cain=E2=80=99t do them o= r check them one piece at a time if there are multiple tablespaces involved= .

=C2=A0

Now if you are dir= ectly trying to =E2=80=9Cswap out and archive=E2=80=9D an individual partit= ion at a time, the best way I know of is to use partition exchange (unless = you have global indexes, this is a=C2=A0 huuge win) and slap the resulting = table (which going forward would already be born in the tablespace=C2=A0 yo= u want to transport later) into the tts.

= =C2=A0

=C2=A0

=C2=A0=

From: = oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On = Behalf Of Hall, Liz
Sent: Wednesday, June 23, 2021 11:31 AMTo: or= acle-l@freelists.org
Subject: Transportable Tablespaces for D= ata Archival ORA-39901 ORA-39921

=C2=A0

<= span style=3D"font-size:11.5pt;font-family:"Oracle Sans",serif;co= lor:rgb(85,90,98)">Esteemed Listers,

I am looking to use TTS to remove ol= d data partitions from a table. I have tried a test case so that I can unde= rstand how it works. Found this example in Oracle Support and cannot get it= to work. Doc ID 731559.1

Below code, creates a d= ate range partitioned table. The goal is to move *the oldest* partition (FY= 2017 stored in tablespace ttsdat1) to a new database. The transportable set= check fails and I do not know why. See bottom for the failures.<= /u>

What am I doing wrong? Why does the transport check fail?=

= =C2=A0

I'm on 19c, ASM and Enterprise Edition.=

=C2= =A0

CREATE TABLESPACE tts= dat1 DATAFILE '+data'=C2=A0=C2=A0SIZE 1M AUTOEXTEND ON MAXSIZE 50M;=

CREATE TABLESPACE ttsdat2 DATAFILE '+data'= =C2=A0=C2=A0SIZE 1M AUTOEXTEND ON MAXSIZE 50M;

CREA= TE TABLESPACE ttsdat3 DATAFILE '+data'=C2=A0=C2=A0SIZE 1M AUTOEXTEN= D ON MAXSIZE 50M;

CREATE TABLESPACE ttsdat4 DATAFIL= E '+data'=C2=A0=C2=A0SIZE 1M AUTOEXTEND ON MAXSIZE 50M;

CREATE TABLESPACE ttsdat5 DATAFILE '+data'=C2=A0=C2=A0= SIZE 1M AUTOEXTEND ON MAXSIZE 50M;

CREATE SEQUENCE = trans_id_seq;

=C2=A0

--drop table txns;

=C2=A0

CREATE TABLE txns (

=C2=A0= =C2=A0 trans_id=C2=A0 NUMBER(12),

=C2=A0=C2=A0 tran= s_dt=C2=A0 DATE,

=C2=A0=C2=A0 from_acct CHAR(10),

=C2=A0=C2=A0 to_acct=C2=A0=C2=A0 CHAR(10),=

=C2=A0=C2=A0 amount=C2=A0=C2=A0=C2=A0 NUMBER(12,2))

=C2=A0=C2=A0 tablespace ttsdat1

=C2= =A0=C2=A0 PARTITION BY RANGE (trans_dt)

=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 ( PARTITION fy2017 VALUES LESS THAN (to_date('201= 8-01-01','yyyy-mm-dd') )

=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLESPACE ttsdat1,=

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 PARTITION fy20= 18 VALUES LESS THAN (to_date('2019-01-01','yyyy-mm-dd') )

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 TABLESPACE ttsdat2,

=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 =C2=A0=C2=A0PARTITION fy2019 VALUES LESS THAN (to_date('2020-= 01-01','yyyy-mm-dd') )

=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLESPACE ttsdat3,=

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 PARTITION fy2020 = VALUES LESS THAN (to_date('2021-01-01','yyyy-mm-dd') )

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 TABLESPACE ttsdat4,

<= span style=3D"font-family:"Courier New"">=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 PARTITION fy2021 VALUES LESS THAN (to_date('2022-01-= 01','yyyy-mm-dd') )

=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLESPACE ttsdat5 );<= /u>

=C2=A0

--Load data

BEGIN

FOR i I= N 1..25000 LOOP

=C2=A0 begin<= /p>

=C2=A0=C2=A0 INSERT INTO txns SELECT

=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 trans_id_seq.nextval,

=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 SYSDATE-ABS(dbms_random.random)/power(2,31)*365*4,=

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 SUBSTR(TO_CHAR(dbms= _random.random,'000000000000'),-10,10),

=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 SUBSTR(TO_CHAR(dbms_random.random,'00000000= 0000'),-10,10),

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = TO_CHAR(ABS(dbms_random.random)/100000,'999999999.00') FROM dual;

=C2=A0

=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 COMMIT;

=C2=A0=C2=A0 END LOOP;<= /u>

END;

/

=C2=A0

exec dbms_stats.gather_table_stats('&yours= chema','TXNS',cascade=3D> True);

= =C2=A0

EXECUTE sys.DBMS_TTS.TRANSPORT_SET_CHECK('t= tsdat1', TRUE);

=C2=A0

=

= SQL>=C2=A0 SELECT * FROM sys.transport_set_violations order by 1;=

=C2=A0

VIOLATIONS

------------------------------------------------------------------= ------------------------------------------------------=

ORA-39901: Partitioned table ECLIPSE.TXNS is partially contained in the= transportable set.

ORA-39921: Default Partition (T= able) Tablespace TTSDAT2 for TXNS not contained in transportable set.

ORA-39921: Default Partition (Table) Tablespace TTSDAT3 = for TXNS not contained in transportable set.

ORA-39= 921: Default Partition (Table) Tablespace TTSDAT4 for TXNS not contained in= transportable set.

ORA-39921: Default Partition (T= able) Tablespace TTSDAT5 for TXNS not contained in transportable set.

=C2=A0

=C2=A0

  • Export fails same error

=C2=A0

[oracle@us3-qa-= icmt-xgldb-01 ~]$ expdp SYSTEM/xG@XGLDB=C2=A0 DUMPFILE=3Dttsfy1.dmp=C2=A0= =C2=A0 DIRECTORY=3Dtrans_dir TRANSPORT_TABLESPACES =3D ttsdat1

=C2=A0

Export: Release 19.0.0.0.0 - Pro= duction on Mon Jun 21 15:56:02 2021

Version 19.9.0.= 0.0

=C2=A0

Copyright (c) 19= 82, 2019, Oracle and/or its affiliates.=C2=A0 All rights reserved.

=C2=A0

Connected to: Oracle Databas= e 19c Enterprise Edition Release 19.0.0.0.0 - Production

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":= =C2=A0 sYSTEM/********@XGLDB DUMPFILE=3Dttsfy1.dmp DIRECTORY=3Dtrans_dir TR= ANSPORT_TABLESPACES=3Dttsdat1

ORA-39396: Warning: e= xporting encrypted data using transportable option without password<= u>

=C2=A0

Processing object type TRAN= SPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS

Proces= sing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER

ORA-39123: Data Pump transportable tablespace job aborted

ORA-39187: The transportable set is not self-contained, violati= on list is

=C2=A0

ORA-3990= 1: Partitioned table ECLIPSE.TXNS is partially contained in the transportab= le set.

ORA-39921: Default Partition (Table) Tables= pace 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 transportab= le set.

ORA-39921: Default Partition (Table) Tables= pace TTSDAT5 for TXNS not contained in transportable set.

Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" stopp= ed due to fatal error at Mon Jun 21 15:56:40 2021 elapsed 0 00:00:35=

=C2=A0

=C2=A0

=C2=A0

=C2=A0


--
Connor McDonald
=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
blog: =C2=A0 connormcdonal= d.wordpress.com
twitter: @connor_mc_d

"If you are n= ot living on the edge, you are taking up too much room."=C2=A0
- Jayne Howard

Fine print: = Views expressed here are my own and not necessarily that of my employer
--00000000000041032f05c681dd66-- -- http://www.freelists.org/webpage/oracle-l