No question at this time
DBA Top 10
1 M. Cadot 10300
2 A. Kavsek 7200
3 B. Vroman 4200
4 P. Wisse 3500
5 J. Schnackenberg 3200
6 J. PĂ©ran 2200
7 T. Boles 600
8 Z. Hudec 400
8 G. Lambregts 400
8 N. Havard 400
8 F. Pachot 400
8 D. Dave 400
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48548
Total active users1403
Act. users last 24h3
Act. users last hour1
Registered user hits last week214
Registered user hits last month641
Go up

Partition foolishness
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