Exchange/Move partitions between tables

Question

I have this table as my main table (100M rows):

create table prova_log(
       id_dispositive       number,
       type                 number,
       date_verification    date,
       status               number
)
partition by range (date_verification) interval (NUMTODSINTERVAL(3,'DAY'))
subpartition by list (type)  
subpartition TEMPLATE (
    SUBPARTITION type1 VALUES (1),
    SUBPARTITION type2 VALUES (2),
    SUBPARTITION type3 VALUES (3),
    SUBPARTITION type4 VALUES (4)
)                          
(
   partition p0816 values less than (to_date('01/09/2016','dd/mm/yyyy'))
);

And I want to make some sort of backup with older values, so I created this (0 rows):

create table prova_log_old (
       id_dispositive       number,
       type                 number,
       date_verification    date,
       status               number
)
partition by range (date_verification) interval (NUMTODSINTERVAL(3,'DAY'))
subpartition by list (type)  
subpartition TEMPLATE (
    SUBPARTITION type1 VALUES (1),
    SUBPARTITION type2 VALUES (2),
    SUBPARTITION type3 VALUES (3),
    SUBPARTITION type4 VALUES (4)
)                          
(
   partition p_old values less than (to_date('01/09/2016','dd/mm/yyyy'))
);

So I want to move/copy/exchange (whatever term) old partitions (15 days+) to prova_log_old.

To do so I've created this job:

PROCEDURE move_data_from_huge_table
   IS
      -- This will move all data after 'vcountdaystokeepdata' days
      vcountdaystokeepdata        NUMBER := 15;
      vcountdatainsidepartition   NUMBER := 0;
   BEGIN
      FOR item IN
         (SELECT *
            FROM (SELECT partition_name,
                         TO_DATE
                            (TRIM
                                ('''' FROM REGEXP_SUBSTR
                                             (EXTRACTVALUE
                                                 (DBMS_XMLGEN.getxmltype
                                                     (   'select high_value from all_tab_partitions where table_name='''
                                                      || table_name
                                                      || ''' and table_owner = '''
                                                      || table_owner
                                                      || ''' and partition_name = '''
                                                      || partition_name
                                                      || ''''
                                                     ),
                                                  '//text()'
                                                 ),
                                              '''.*?'''
                                             )
                                ),
                             'syyyy-mm-dd hh24:mi:ss'
                            ) high_value
                    FROM all_tab_partitions
                   WHERE table_name = 'PROVA_LOG')
           WHERE high_value < SYSDATE - vcountdaystokeepdata)
      LOOP                 
         EXECUTE IMMEDIATE    'alter table PROVA_LOG EXCHANGE PARTITION '
                           || item.partition_name
                           || ' with table PROVA_LOG_OLD';

         EXECUTE IMMEDIATE    'select count(*) from PROVA_LOG partition ('
                           || item.partition_name
                           || ')'
                      INTO vcountdatainsidepartition;

         IF vcountdatainsidepartition = 0
         THEN
            EXECUTE IMMEDIATE    'ALTER TABLE PROVA_LOG DROP PARTITION '
                              || item.partition_name
                              || '';
         END IF;

      END LOOP;
   END;

But when I run the procedure I got

ORA-14292 partitioning type of table must match subpartitioning type of composite partition

I assume that I must have a partition in my backup table with the same name as my main partitioned table right?

How can I make this work?

I tried to add a partition to my backup table but without success..It's important to mention that all partition's name are random (oracle generates it).

Why do you want to copy/move data to another table? It is one of the main benefits of a partitioned table that you don't need such stuff. — Wernfried Domscheit, Dec 09 '16 at 12:34
@WernfriedDomscheit because I have another job to count these millions of rows by range of dates. even partitioned the process takes too long when the table is like 500M+ rows... Anyways... I want to "drop" (without losing) the older valeus since I don't need them anymore. — Marllon Nasser, Dec 09 '16 at 12:36

Wernfried Domscheit · Accepted Answer · 2016-12-09T13:46:33.863

2

I still don't understand why you want to move the partitions, anyway I have a solution.

First of all, you can address a partition either like

SELECT COUNT(*) FROM PROVA_LOG PARTITION (SYS_P7138);

or you can do it as

SELECT COUNT(*) FROM PROVA_LOG PARTITION FOR (TO_DATE('2016-10-01', 'YYYY-MM-DD'));

or if you prefer DATE literals

SELECT COUNT(*) FROM PROVA_LOG PARTITION FOR (DATE '2016-10-01');

An automatic solution for you problem could be this one:

DECLARE

    CURSOR TabPartitions IS
    SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE
    FROM USER_TAB_PARTITIONS 
    WHERE TABLE_NAME = 'PROVA_LOG'
    ORDER BY 1,2;

    ts DATE;

BEGIN
    FOR aPart IN TabPartitions LOOP
        EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT ts;
        IF ts <> DATE '2016-09-10' AND ts < SYSDATE - 15 THEN
            --EXECUTE IMMEDIATE 'INSERT INTO PROVA_LOG_OLD SELECT * FROM PROVA_LOG PARTITION FOR (DATE '''||TO_CHAR(ts, 'yyyy-mm-dd')||''')'; 
            --EXECUTE IMMEDIATE 'ALTER TABLE PROVA_LOG DROP PARTITION FOR (DATE '''||TO_CHAR(ts, 'yyyy-mm-dd')||''') UPDATE GLOBAL INDEXES';
            EXECUTE IMMEDIATE 'ALTER TABLE PROVA_LOG EXCHANGE PARTITION FOR (DATE '''||TO_CHAR(ts, 'yyyy-mm-dd')||''') WITH TABLE PROVA_LOG_OLD INCLUDING INDEXES';
        END IF;
    END LOOP;

END;

Your backup table must be like this:

CREATE TABLE prova_log_old (
       id_dispositive       NUMBER,
       TYPE                 NUMBER,
       date_verification    DATE,
       status               NUMBER
)
PARTITION BY LIST (TYPE) 
(
    PARTITION type1 VALUES (1),
    PARTITION type2 VALUES (2),
    PARTITION type3 VALUES (3),
    PARTITION type4 VALUES (4)
);

or no partitioning at all

CREATE TABLE prova_log_old (
       id_dispositive       NUMBER,
       TYPE                 NUMBER,
       date_verification    DATE,
       status               NUMBER
);

edited Dec 09 '16 at 13:46

answered Dec 09 '16 at 12:56

Wernfried Domscheit

54,457
9
76
110

hey mate thanks for the answer.. but I think this is expensive.. I mean each partition has at least 10M rows... this will take a long time to execute right? – Marllon Nasser Dec 09 '16 at 13:07
Now I'm getting an `ORA-14128 foreign key constraint mismatch in alter table exchange partition` – Marllon Nasser Dec 09 '16 at 14:14
I tested it and for me it worked. Did you create new table `prova_log_old` as provided? – Wernfried Domscheit Dec 09 '16 at 14:22
Yes... I created partitioned by list as you said. But my main table has some FK... – Marllon Nasser Dec 09 '16 at 14:23
This seems to be a problem. For example you cannot run `TRUNCATE TABLE ...` if you have Foreign-Keys on it. I assume you have to same limitation for EXCHANGE PARTITION. Maybe you can disable them temporarily. – Wernfried Domscheit Dec 09 '16 at 14:26
do they help in performace? I mean I have the table partitioned and indexed. A unique process to insert data on it... do I really need the constraint? – Marllon Nasser Dec 09 '16 at 14:28
Ok I disabled the constraints temporarily.. and now i'm getting `ORA-14099 all rows in table do not qualify for specified partition` – Marllon Nasser Dec 09 '16 at 14:30
Check http://psoug.org/oraerror/ORA-14099.htm - foreign-keys will never improve any performance. – Wernfried Domscheit Dec 09 '16 at 14:51
@WernfriedDomscheit One late note. I disagree with you that "foreign-keys will never improve any performance." Check link http://stackoverflow.com/questions/8153674/do-foreign-key-constraints-influence-query-transformations-in-oracle and http://www.oracle.com/technetwork/testcontent/o39asktom-096149.html – Evgeniy K. Dec 12 '16 at 07:11
@EvgeniyK., oh yes you are right. However, I assume while Foreign Keys may improve query performance the will never improve any DML operation. – Wernfried Domscheit Dec 12 '16 at 07:52

score 1 · Answer 2 · answered Dec 09 '16 at 13:07

1

You are doing it wrong. You exchange partition with all partitioned table not with it partition, just look one more at your code

 EXECUTE IMMEDIATE    'alter table PROVA_LOG EXCHANGE PARTITION '
                           || item.partition_name
                           || ' with table PROVA_LOG_OLD';

In case of exchange partition you should do as follows

Create empty table without partition with same structure like PROVA_LOG but not partitioned.
Exchange partition in production table with new_table
Exchange partition in hist table with new_table

answered Dec 09 '16 at 13:07

Evgeniy K.

1,137
7
11

I'm sorry... I don't understand. I have to create an empty table without partition and do what with it? – Marllon Nasser Dec 09 '16 at 13:20
@MarllonNasser Tom kyte has already solved this problem. See https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1238800184155,%7Bpartitions%7D. (message from April 02, 2006) I should add this link first. – Evgeniy K. Dec 09 '16 at 13:39

Exchange/Move partitions between tables

2 Answers2