All,
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 and cannot get it to work. Doc ID 731559.1 Please educate me.
Below code, creates a date range partitioned table. The goal is to move *the oldest* partition 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?
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 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 );
CREATE SEQUENCE trans_id_seq;
-- 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;
exception
when others then null;
end;
END LOOP;
END;
exec dbms_stats.gather_table_stats('&yourschema','TXNS',cascade=> True);
EXECUTE sys.DBMS_TTS.TRANSPORT_SET_CHECK('ttsdat1', TRUE);
--- verify that that ttsdat1 is self contained. It fails.
SELECT * FROM sys.transport_set_violations order by 1;
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.