Avatar of IT_ETL
IT_ETL
 asked on

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.
Oracle Database

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
Mark Geerlings

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_DBA

To do what markgeer suggest and you want to do it with no downtime, checkout: Zero Downtime technique to rebuild Oracle tables
Mark Geerlings

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
slightwv (䄆 Netminder)

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_ETL

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

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?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Mark Geerlings

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_DBA

@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_ETL

ASKER
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
IT_ETL

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

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