Go up

Next thread:	OCR file lost
Prev thread:	oracle 12.2 on red hat 8

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

Follow up by mail

Click here

---

Subject:	Partition foolishness
Author:	Kirt Thomas, United States
Date:	Oct 15, 2020, 20:45, 10 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, 7 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, 5 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, 5 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, 4 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