Oracle 使用可传输表空间存档旧数据

我的第一篇文章，但我在这个伟大的网站上读了百万分之一!

我正在寻找使用TTS从表中删除旧数据分区。我已经尝试了一个测试用例，以便可以理解它的工作原理。在Oracle支持中找到了此示例，但无法使其正常工作。Doc ID 731559.1
在代码下面，创建一个日期范围分区表。目标是将 * 最旧的 * 分区 (存储在表空间ttsdat1中的FY2017) 移至新数据库。The transportable set check fails and I do not know why.有关故障，请参见底部。
我做错了什么？为什么运输检查失败？

我在19c，ASM和企业版。

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);

SELECT * FROM sys.transport_set_violations order by 1;

违规行为
-
ORA-39901: 分区表ECLIPSE.TXNS部分包含在可传输集中。
ORA-39921: 可传输集中未包含的TXNS的默认分区 (表) 表空间TTSDAT2。
ORA-39921: 可传输集中未包含的TXNS的默认分区 (表) 表空间TTSDAT3。
ORA-39921: 可传输集中未包含的TXNS的默认分区 (表) 表空间TTSDAT4。
ORA-39921: 可传输集中未包含的TXNS的默认分区 (表) 表空间TTSDAT5。


Export导出失败同样的错误

 expdp sYSTEM/xxxx#@XGLDB  DUMPFILE=ttsfy1.dmp   DIRECTORY=trans_dir TRANSPORT_TABLESPACES = ttsdat1

出口: 发布19.0.0.0.0-Mon 6月21日15:56:02 2021上的生产
版本19.9.0.0.0

版权所有 (c) 1982、2019、Oracle和/或其附属公司。保留所有权利。

连接到: Oracle数据库19c企业版版本19.0.0.0.0-生产
启动 “系统”。“系统 _ 导出 _ 传输 _ 01”: 系统/******** @ XGLDB DUMPFILE = ttsfy1.dmp目录 = trans_dir传输 _ 表空间 = ttsdat1
ORA-39396: 警告: 使用不带密码的可传输选项导出加密数据

处理对象类型TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
处理对象类型TRANSPORTABLE_EXPORT/STATISTICS/MARKER
ORA-39123: 数据泵可传输表空间作业中止
ORA-39187: 可运输集合不是自包含的，违规列表是

ORA-39901: 分区表ECLIPSE.TXNS部分包含在可传输集中。
ORA-39921: 可传输集中未包含的TXNS的默认分区 (表) 表空间TTSDAT2。
ORA-39921: 可传输集中未包含的TXNS的默认分区 (表) 表空间TTSDAT3。
ORA-39921: 可传输集中未包含的TXNS的默认分区 (表) 表空间TTSDAT4。
ORA-39921: 可传输集中未包含的TXNS的默认分区 (表) 表空间TTSDAT5。
作业 “系统”。“SYS_EXPORT_TRANSPORTABLE_01” 由于星期一6月21日15:56:40 2021经过0 00:00:35时的致命错误而停止

分区表的规则是:

从文档中:https://docs.oracle.com/en/database/oracle/oracle-database/18/admin/transporting-data.html#GUID-1901E9C3-8FCE-4D4E-AB65-34D703474E52

The tablespace set you want to copy must contain either all partitions of a partitioned table, or none of the partitions of a partitioned table. If you want to transport a subset of a partition table, you must exchange the partitions into tables.

我将从您的职位开始，并从那里开始工作 (我冒昧地使您的插入脚本更加高效)

SQL> CREATE TABLESPACE ttsdat1 DATAFILE 'X:\ORACLE\ORADATA\DB19\PDB1\D1.DBF'  SIZE 1M AUTOEXTEND ON MAXSIZE 50M;

Tablespace created.

SQL> CREATE TABLESPACE ttsdat2 DATAFILE 'X:\ORACLE\ORADATA\DB19\PDB1\D2.DBF'  SIZE 1M AUTOEXTEND ON MAXSIZE 50M;

Tablespace created.

SQL> CREATE TABLESPACE ttsdat3 DATAFILE 'X:\ORACLE\ORADATA\DB19\PDB1\D3.DBF'  SIZE 1M AUTOEXTEND ON MAXSIZE 50M;

Tablespace created.

SQL> CREATE TABLESPACE ttsdat4 DATAFILE 'X:\ORACLE\ORADATA\DB19\PDB1\D4.DBF'  SIZE 1M AUTOEXTEND ON MAXSIZE 50M;

Tablespace created.

SQL> CREATE TABLESPACE ttsdat5 DATAFILE 'X:\ORACLE\ORADATA\DB19\PDB1\D5.DBF'  SIZE 1M AUTOEXTEND ON MAXSIZE 50M;

Tablespace created.

SQL> CREATE SEQUENCE trans_id_seq CACHE 1000;

Sequence created.

SQL> CREATE TABLE txns (
  2     trans_id  NUMBER(12),
  3     trans_dt  DATE,
  4     from_acct CHAR(10),
  5     to_acct   CHAR(10),
  6     amount    NUMBER(12,2))
  7     tablespace ttsdat1
  8     PARTITION BY RANGE (trans_dt)
  9        ( PARTITION fy2017 VALUES LESS THAN (to_date('2018-01-01','yyyy-mm-dd') )
 10             TABLESPACE ttsdat1,
 11          PARTITION fy2018 VALUES LESS THAN (to_date('2019-01-01','yyyy-mm-dd') )
 12             TABLESPACE ttsdat2,
 13          PARTITION fy2019 VALUES LESS THAN (to_date('2020-01-01','yyyy-mm-dd') )
 14             TABLESPACE ttsdat3,
 15          PARTITION fy2020 VALUES LESS THAN (to_date('2021-01-01','yyyy-mm-dd') )
 16             TABLESPACE ttsdat4,
 17          PARTITION fy2021 VALUES LESS THAN (to_date('2022-01-01','yyyy-mm-dd') )
 18             TABLESPACE ttsdat5 );

Table created.

SQL>    INSERT INTO txns SELECT
  2        trans_id_seq.nextval,
  3        SYSDATE-ABS(dbms_random.random)/power(2,31)*365*4,
  4        SUBSTR(TO_CHAR(dbms_random.random,'000000000000'),-10,10),
  5        SUBSTR(TO_CHAR(dbms_random.random,'000000000000'),-10,10),
  6        TO_CHAR(ABS(dbms_random.random)/100000,'999999999.00') FROM dual
  7        connect by level <= 25000;

25000 rows created.

SQL>       COMMIT;

Commit complete.

SQL>
SQL> exec dbms_stats.gather_table_stats('','TXNS',cascade=> True);

PL/SQL procedure successfully completed.

SQL>
SQL> EXECUTE sys.DBMS_TTS.TRANSPORT_SET_CHECK('ttsdat1', TRUE);

PL/SQL procedure successfully completed.

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

VIOLATIONS
------------------------------------------------------------------------------------------------------------------------
ORA-39901: Partitioned table MCDONAC.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.

所以这里有几件事要解决

1) 将存档分区放入自己的表中

SQL> create table archive_txns_2017 tablespace ttsdat1
  2  for exchange with table txns;

Table created.

SQL> alter table txns exchange partition fy2017 with table archive_txns_2017;

Table altered.

SQL> EXECUTE sys.DBMS_TTS.TRANSPORT_SET_CHECK('ttsdat1', TRUE);

PL/SQL procedure successfully completed.

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

VIOLATIONS
----------------------------------------------------------------------------------------------------
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.

但是似乎仍然存在问题。见下文

2) 修复默认属性

除分区定义外，该表还有一个默认的表空间设置，该设置仍指向ttsdata1。我们需要解决这个问题。

SQL> alter table txns modify default attributes tablespace ttsdat2;

Table altered.

SQL> EXECUTE sys.DBMS_TTS.TRANSPORT_SET_CHECK('ttsdat1', TRUE);

PL/SQL procedure successfully completed.

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

no rows selected

我们结束了。