Forum Stats

  • 3,741,184 Users
  • 2,248,385 Discussions
  • 7,861,667 Comments

Discussions

Data Archival Using Transportable Tablespaces

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.


    

Sign In or Register to comment.