SQL使用EXCHANGE分区创建备份表

Question

我想用EXCHANGE PARTITION备份表a1_crm_query。此表包含具有不同状态的行，如'ERROR'， 'NEW'或'DONE'，如果有另一个表(a1_crm_query_LOG)具有状态'ERROR'和'DONE'，那将是伟大的，但我的第一个表(a1_crm_query)将仅具有'NEW'。

首先创建我的表:

 CREATE TABLE ma_user.a1_crm_query (
       ID NUMBER PRIMARY KEY,
       DATA VARCHAR2(200) 
    );

然后创建第二个带有partition的表。

CREATE TABLE ma_user.a1_crm_query_LOG (
       ID NUMBER PRIMARY KEY,
       DATA VARCHAR2(200)
    )
    PARTITION BY LIST (DATA) (
       PARTITION DONE_STATUS VALUES ('DONE'),       
       PARTITION ERROR_STATUS VALUES ('ERROR')
       ) ;

then insert values into a1_crm_query:

INSERT INTO ma_user.a1_crm_query SELECT 1 , CAST('NEW' AS VARCHAR2(200)) FROM dual;
INSERT INTO ma_user.a1_crm_query SELECT 2 , CAST('DONE' AS VARCHAR2(200)) FROM dual;
INSERT INTO ma_user.a1_crm_query SELECT 3, CAST('ERROR' AS VARCHAR2(200)) FROM dual; 

现在我想创建每日进程，应该移动所有行'DONE'和'ERROR'到表a1_crm_query_LOG, a1_crm_query应该只有'NEW'。

我尝试使用exchange partition:

    ALTER TABLE ma_user.a1_crm_query_LOG EXCHANGE PARTITION ERROR_STATUS WITH TABLE ma_user.a1_crm_query WITHOUT VALIDATION;
ALTER TABLE ma_user.a1_crm_query_LOG EXCHANGE PARTITION DONE_STATUS WITH TABLE ma_user.a1_crm_query WITHOUT VALIDATION;

但是在此之后ERROR_STATUS分区包含所有状态的所有行

Answer 1

您可能想要创建两个临时表(temp_error, temp_done)，其中包含从a1_crm_query表中复制的ERROR和DONE数据，然后与temp_error和temp_done表交换分区。

你所面临的问题是因为WITHOUT VALIDATION。实际上，你是在告诉Oracle，我已经验证了正在交换的数据，所以Oracle不会为你验证它。

查看此链接

Update: Approach 1所以这就是我要做的。

CREATE TABLE TEMP_ERROR
(       ID NUMBER PRIMARY KEY,
        DATA VARCHAR2(200) );
CREATE TABLE TEMP_DONE 
(
  ID NUMBER PRIMARY KEY,
  DATA VARCHAR2(200) );
insert into TEMP_ERROR
select * from a1_crm_query
where data = 'ERROR';
insert into TEMP_DONE
select * from a1_crm_query
where data = 'DONE';
ALTER TABLE a1_crm_query_LOG EXCHANGE PARTITION ERROR_STATUS WITH TABLE TEMP_ERROR WITHOUT VALIDATION;
ALTER TABLE a1_crm_query_LOG EXCHANGE PARTITION DONE_STATUS WITH TABLE TEMP_DONE WITHOUT VALIDATION;
truncate table temp_error;
truncate table temp_done

更新2:方法2如果您也可以对a1_crm_query进行分区，那么这种方法可能最适合您。你需要一张临时桌子。这种方法不需要删除或截断。

CREATE TABLE a1_crm_query (
   ID NUMBER PRIMARY KEY,
   DATA VARCHAR2(200) 
)
    PARTITION BY LIST (DATA) (
   PARTITION DONE_STATUS VALUES ('DONE'),       
   PARTITION ERROR_STATUS VALUES ('ERROR'),
   PARTITION OTHER_STATUS VALUES (DEFAULT)
   ) ;
CREATE TABLE a1_crm_query_LOG (
   ID NUMBER PRIMARY KEY,
   DATA VARCHAR2(200)
)
PARTITION BY LIST (DATA) (
   PARTITION DONE_STATUS VALUES ('DONE'),       
   PARTITION ERROR_STATUS VALUES ('ERROR')
   ) ;

INSERT INTO a1_crm_query SELECT 1 , 'NEW' FROM dual;
INSERT INTO a1_crm_query SELECT 2 , 'DONE'  FROM dual;
INSERT INTO a1_crm_query SELECT 3, 'ERROR' FROM dual; 
commit;       
CREATE TABLE interim
(       ID NUMBER PRIMARY KEY,
       DATA VARCHAR2(200) );

ALTER TABLE a1_crm_query EXCHANGE PARTITION ERROR_STATUS WITH TABLE INTERIM WITHOUT VALIDATION;
ALTER TABLE a1_crm_query_LOG EXCHANGE PARTITION ERROR_STATUS WITH TABLE INTERIM  WITHOUT VALIDATION;
ALTER TABLE a1_crm_query EXCHANGE PARTITION DONE_STATUS WITH TABLE INTERIM WITHOUT VALIDATION;
ALTER TABLE a1_crm_query_LOG EXCHANGE PARTITION DONE_STATUS WITH TABLE INTERIM WITHOUT VALIDATION;

必须在a1_crm_query

上重建索引

ALTER INDEX <index name> REBUILD;
select * from a1_crm_query;
select * from interim;
select * from a1_crm_query_LOG partition(ERROR_STATUS);
select * from a1_crm_query_LOG partition(done_STATUS)

另见此链接