How do I insert old partion into a table?

I like to insert old partion into a table. For example, partition (P201505) which contains May data needs to be inserted into tmp_table1. But the table already has partitions P201506 and P201507. The script for P201505 is following,

alter table tmp_table1 add PARTITION P201505 VALUES LESS  (201506) COMPRESS;

For partitions P201506 and P201507 are following. By the way, these partitions already exist on the table.

alter table tmp_table1 add PARTITION P201506 VALUES LESS  (201507) COMPRESS;
alter table tmp_table1 add PARTITION P201507 VALUES LESS  (201508) COMPRESS;

Please advise how to insert a old partition P201505 into a table considering that partitions P201506 and P201507 already exist on the table.
IT_ETLAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark GeerlingsDatabase AdministratorCommented:
Oracle does not support adding "old" partitions to an existing table that is "range" partitioned.

So, if you really want to add this partition, you will have to rename the current table, create a new table with the partitions you want, then copy the: data, indexes, constraints and grants from the original table to the new table.
MikeOM_DBACommented:
To do what markgeer suggest and you want to do it with no downtime, checkout: Zero Downtime technique to rebuild Oracle tables
Mark GeerlingsDatabase AdministratorCommented:
Yes, in theory, the DBMS_REDEFINITION package offers this capability.  In practice, I have not always found that package to work quite as well as advertised.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Get Your eBook
slightwv (䄆 Netminder) Commented:
You should be able to SPLIT the existing partition to get what you want.

Quick example of splitting < 2 into < 1 and < 2.

drop table tab1 purge;
create table tab1 (
    col1 number
)
partition by range (col1) (
    PARTITION p1 VALUES LESS THAN (2),
    PARTITION p2 VALUES LESS THAN (3),
    PARTITION p3 VALUES LESS THAN (4)
);

insert into tab1 values(1);
insert into tab1 values(2);
insert into tab1 values(3);
commit;

select partition_name, high_value from user_tab_partitions where table_name='TAB1';

ALTER TABLE tab1 SPLIT PARTITION 
      p1 at (1) INTO ( PARTITION
      p1a, PARTITION p1b);

select partition_name, high_value from user_tab_partitions where table_name='TAB1';

Open in new window

IT_ETLAuthor Commented:
It looks like dropping partition is not a option. Can I delete data from table tmp_tabl1 partition (P201505)? Then re-insert data for this partition into tmp_table1.
slightwv (䄆 Netminder) Commented:
Not sure what dropping it will get you.


If you just want to insert a previous months data into a temp table, just insert it.

You will just end up with a single partition with two months of data in it.

Did you look at the SPLIT option I posted above?
Mark GeerlingsDatabase AdministratorCommented:
Good idea!  That "split partition" suggestion may work.  I've done a lot with partitioned tables, but I have never tried the "split partition" operation.  I think it is not particularly efficient, like many partition-related commands are, but it may solve the problem you have here with minimal (or no) application downtime.  If it works, but the resulting partitions don't have the names you want or use more space than you want, you can use either "alter table ... rename partition ..." commands or "alter table... move partition..." commands to resize, shrink or rename the resulting partitions.  Similar rename commands can also be used on the corresponding "local" index partitions.  

If you use the "alter table... move partition..." commands to resize or compress table partitions, you need to manually rebuild any corresponding local index partitions.  If you have any global (non-partitioned) indexes on the table, you should add "update global indexes" to the "alter table... move partition..." commands.  This avoids having to do a complete index rebuild on the global index(es).
MikeOM_DBACommented:
@markgeer

Yes, in theory, the DBMS_REDEFINITION package offers this capability.  In practice, I have not always found that package to work quite as well as advertised.
Perhaps you did not use it correctly. The DBMS_REDEFINITION package works like a charm,
I have used it to change partitioning for some dozen tables, changing from manual partitioning to interval, from hash to range, for changing column type number to character, for moving lobs to securefile lobs, etc, etc, etc... And all with no downtime required, in fact done during normal operating hours with no impact on response time.
Read the EE article by Greg Clough I posted.
IT_ETLAuthor Commented:
Slightwv,

I have dropped three P201505, P201506, and P201507 then added those partitions and re-inserted data to those partitions.

Question regarding split partition, partition P201505 has incorrect data. If I could delete data from this partition then re-insert data then it would be fine. Seems like that's not a possible scenario. If I split partition P201505 to P1a and P1b then are both partitions will have same data just like P201505.
slightwv (䄆 Netminder) Commented:
>>I have dropped three P201505, P201506, and P201507 then added those partitions and re-inserted data to those partitions.

Not sure why you dropped them.  If you used split, it shouldn't have been necessary.


>>If I split partition P201505 to P1a and P1b then are both partitions will have same data just like P201505.

I don't understand.  The new partitions should have the data they are allowed to have.

I've updated my test case below.  I've added data and after I split the partitions, I query them directly to show they have the data they are supposed to have.

What is your exact version of Oracle (all 4 numbers please).

The code below was test with 11.2.0.2

drop table tab1 purge;
create table tab1 (
    col1 number
)
partition by range (col1) (
    PARTITION p1 VALUES LESS THAN (2),
    PARTITION p2 VALUES LESS THAN (3),
    PARTITION p3 VALUES LESS THAN (4)
);

insert into tab1 values(0);
insert into tab1 values(1);
insert into tab1 values(2);
insert into tab1 values(3);
commit;

select partition_name, high_value from user_tab_partitions where table_name='TAB1';

select * from tab1 partition(p1);
select * from tab1 partition(p2);

ALTER TABLE tab1 SPLIT PARTITION 
      p1 at (1) INTO ( PARTITION
      p1a, PARTITION p1b);

select partition_name, high_value from user_tab_partitions where table_name='TAB1';

select * from tab1 partition(p1a);
select * from tab1 partition(p1b);

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IT_ETLAuthor Commented:
My bad. I didn't read below statement carefully,

ALTER TABLE tab1 SPLIT PARTITION
      p1 at (1) INTO ( PARTITION
      p1a, PARTITION p1b);

It looks like data can be deleted from old partition. For example,

delete from tab1 partition(p1a);
commit;

That would been easier. I could delete incorrect data from this partition then insert correct data to this partition. Then rename this partition to original partition (for example, P1) name.

Thanks for your help.
slightwv (䄆 Netminder) Commented:
>> I could delete incorrect data from this partition then insert correct data to this partition.

There should be no need to delete and insert.  You can split it in place and the data moves automatically.

In my example, I dropped nothing, I deleted nothing and everything ended up where it was supposed to be.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.