Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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.
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
To do what markgeer suggest and you want to do it with no downtime, checkout: Zero Downtime technique to rebuild Oracle tables
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.
You should be able to SPLIT the existing partition to get what you want.
Quick example of splitting < 2 into < 1 and < 2.
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';
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.
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?
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?
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).
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).
@markgeerPerhaps you did not use it correctly. The DBMS_REDEFINITION package works like a charm,
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.
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.
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 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 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
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);
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.
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.
>> 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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial