No question at this time
The DBA-Village forum
as RSS feed
Site StatisticsEver registered users | 48572 | Total active users | 1389 | Act. users last 24h | 3 | Act. users last hour | 0 | Registered user hits last week | 108 | Registered user hits last month | 299 |
|
Go up
Partition foolishness
Message |
Score |
Author |
Date |
I have a table that has multiple partitions breaki...... |
|
Kirt Thomas |
Oct 15, 2020, 20:45 |
Hi Kirt,
The opposite from split is to merge pa...... |
|
Philip Wisse |
Oct 19, 2020, 15:17 |
I'm not sure how that applies but thanks for readi...... |
|
Kirt Thomas |
Oct 20, 2020, 21:56 |
I'm sorry, should have included an example.
You...... |
|
Philip Wisse |
Oct 21, 2020, 08:58 |
No, I understand - it's just that it doesn't take ...... |
|
Kirt Thomas |
Oct 22, 2020, 16:14 |
Subject: |
Partition foolishness |
Author: |
Kirt Thomas, United States |
Date: |
Oct 15, 2020, 20:45, 99 days ago |
Os info: |
AIX |
Oracle info: |
11 |
Message: |
I have a table that has multiple partitions breaking on number of rows (10,000,000). There is a maxvalue partition, in case of overflow.
I was adding new partitions to the table, and via typo, set the next break at 59,400,000,000 rows to many. Instead of the 600,000,0000 - I added an extra zero and wound up with 60,000,000,000
alter table oe_order_lines_all
split partition oe_linemaxvalues
at (6000000000) into (partition oe_line51 tablespace APPS_TS_TX_DATA, partition oe_linemaxvalues tablespace APPS_TS_TX_DATA)
update indexes
parallel 20;
So, I figured I could do a split. However the first time I did the split I didn't specify things correctly:
alter table oe_order_lines_all split partition oe_line51 at (600000000);
Creating a 2 sys_nnnn partitions. One at the correct next value of 600M, and one still at 60B. Also badly named. I figured out my issue here and first renamed the two partitions.
alter table ont.oe_order_lines_all rename partition SYS_P8561 to oe_line51;
alter table ont.oe_order_lines_all rename partition SYS_P8562 to oe_line52;
Then I split the 2nd 60B value index correctly...
alter table oe_order_lines_all
split partition oe_line52
at (610000000) into (partition oe_line52 tablespace APPS_TS_TX_DATA, partition oe_line53 tablespace APPS_TS_TX_DATA)
update indexes
parallel 20;
I did this creating splits at 10M records for 3 more partitions (oe_line53, oe_line54, oe_line55) leaving me with an oe_line56 with the high_value of 60B.
This will give me 18 months of growth.
I then unlock stats on the table, do a copy of the stats to all the new partitions (since each one has 10M rows..)..
EXEC DBMS_STATS.COPY_TABLE_STATS ('ONT', 'OE_ORDER_LINES_ALL', 'OE_LINE25', 'OE_LINE51'); --etc etc
But for the two partitions I renamed, I get an error!:
ERROR at line 1:
ORA-20000: partition "OE_LINE51" of index "ONT"."OE_ORDER_LINES_U1" does not
exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 28416
ORA-06512: at line 1
The other partitions are fine. (stats copy, no errors).
I look at the all_ind_partitions table, and see that instead of the oe_line51 and oe_line52 partitions, there are the sys_p8561 and sys_p8562 (that I'd renamed).
Now I have some time before the partition gets data but I'd like to figure a fix out now.
Can I rename like so:
alter index ont.OE_ORDER_LINES_U1 rename partition SYS_P8561 to OE_LINE51
Or do I even need to, can I simply copy the stats:
EXEC DBMS_STATS.COPY_TABLE_STATS ('ONT', 'OE_ORDER_LINES_ALL', 'OE_LINE25', 'SYS_P8561');
Also - a bonus question, how do I get rid of or change the high_value of the oe_line56 partition that's at 60B?
I know there's a lot here, and I know I made some mistakes - duh - Thanks for any help. |
Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
Subject: |
Re: Partition foolishness |
Author: |
Philip Wisse, Netherlands |
Date: |
Oct 19, 2020, 15:17, 96 days ago |
Message: |
Hi Kirt,
The opposite from split is to merge partitions. When partitions are empty this is instantaneous.
Thanks for your story. It would be advisable to generate the commands using scripts.
HTH, Philip |
Your rating?: |
This reply is Good Excellent |
Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
Subject: |
Re: Partition foolishness |
Author: |
Kirt Thomas, United States |
Date: |
Oct 20, 2020, 21:56, 94 days ago |
Message: |
I'm not sure how that applies but thanks for reading.
I ended up renaming the partitions:
alter index ont.OE_ORDER_LINES_U1 rename partition SYS_P8562 to OE_LINE51
And then copying the stats:
EXEC DBMS_STATS.COPY_TABLE_STATS ('ONT', 'OE_ORDER_LINES_ALL', 'OE_LINE34', 'OE_LINE51');
No errors on the copy this time so I'm confident it worked.
Now all I need to do is figure out if I can adjust down the max value of the last partition. Not a big deal, it essentially sits in front of the overflow partition - I'll just need to remember to split it when I add new parts. |
Your rating?: |
This reply is Good Excellent |
Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
Subject: |
Re: Partition foolishness |
Author: |
Philip Wisse, Netherlands |
Date: |
Oct 21, 2020, 08:58, 94 days ago |
Message: |
I'm sorry, should have included an example.
You can add range partitions using the split command.
You can delete them by using the merge command.
https://docs.oracle.com/database/121/VLDBG/GUID-DFCAA278-5300-48F8-8AA7-C1E57BC0E797.htm
Regards, Philip |
Your rating?: |
This reply is Good Excellent |
Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
Subject: |
Re: Partition foolishness |
Author: |
Kirt Thomas, United States |
Date: |
Oct 22, 2020, 16:14, 93 days ago |
Message: |
No, I understand - it's just that it doesn't take care of my problem:
"The resulting partition inherits the higher upper bound of the two merged partitions."
It's the higher upper bound that's the issue. I suppose I could merge that partition with the maxvalue partition; but I have 20M records in that partition (from a previous dba, that forgot to add partitions).
If I can't change the range value, and I can't drop the empty partition, I'll just leave it, it's essentially acting like the maxvalue partition anyway.
Thanks. |
Your rating?: |
This reply is Good Excellent |
Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
|