SQL Creating backup table using EXCHANGE PARTITION

Question

I want to make a backup of table a1_crm_query using EXCHANGE PARTITION. This table contains rows with different statuses like 'ERROR', 'NEW', or 'DONE', and it would be great if there will be another table (a1_crm_query_LOG) with statuses 'ERROR' and 'DONE' but my first table(a1_crm_query) will be with only 'NEW'.

First of all I create my table:

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

    );

Then I create second table with 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;

Now I want to create daily process which should move all rows with 'DONE' and 'ERROR' into table a1_crm_query_LOG, a1_crm_query should be only with 'NEW'.

I try to use 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;

But after this ERROR_STATUS partition contains all rows with all statuses.

Akio Hamasaki · Accepted Answer · 2016-10-04T17:14:10.203

You might want to create two temp tables (temp_error, temp_done) with ERROR and DONE data copied in them from the a1_crm_query table, then exchange partition with table temp_error and temp_done.

The issue you are facing is because of WITHOUT VALIDATION. Essentially you are telling Oracle that I have already validated the data being exchanged so Oracle will not validate it for you.

See this link

Update: Approach 1 So this is what I would do.

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

Update 2: Approach 2 If you can have a1_crm_query partitioned as well, then this approach might suit you best. You will need one interim table. No deletes or truncates required with this approach.

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;

You must then rebuild the index on 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)

Also See this link

Hi, Akio! I should use only one _log table which should contans both statuses. I cant catch up why move ALL rows include 'NEW' — Jdzel, Oct 04 '16 at 13:56
Akio, thanks, it works, but table a1_crm_query still has rows with 'ERROR' and 'DONE'. Frankly speaking, i dont see advantageous of this method. I thought that EXCHANGE PARTITION should replace INSERT-DELETE operation — Jdzel, Oct 04 '16 at 14:37
@Jdzel you can delete ERROR and DONE records from a1_crm_query. I will post an alternative approach if you can have a1_crm_query partitioned as well. — Akio Hamasaki, Oct 04 '16 at 14:53
Yes,it will be great if you post this. I can make a1_crm_query partitioned. — Jdzel, Oct 04 '16 at 14:59
Akio, thanks, but after this exchanging i cant insert other rows with this statuses('ERROR') into a1_crm_query. I think it happens because of moving partition from this table. Should I add partition to a1_crm_query after exchanging? — Jdzel, Oct 04 '16 at 16:57
aah! you have to rebuild the index ALTER INDEX SYS_C0020616 REBUILD; Replace the index name with yours. I have updated the answer above. — Akio Hamasaki, Oct 04 '16 at 17:19

SQL Creating backup table using EXCHANGE PARTITION

1 Answers1