Thursday, October 11, 2007

Drop Partition Fails Generating ORA-00942 or ORA-02149 or ORA-14048

fact: Oracle Server - Enterprise Edition 8.1
fact: Oracle Server - Enterprise Edition 9
symptom: Cannot drop partition
symptom: ORA-02149: Specified partition does not exist
symptom: ORA-00942: table or view does not exist
symptom: ORA-14048: a partition maintenance operation may not be combined
with other
cause: Partition is created using a public or private synonym.
This is Bug 1716968 :CANNOT DROP PARTITION IF ADDED VIA SYNONYM - ORA-2149
base Bug 1273906 fixed in 9.0.2.



fix:

The strategy should be NOT to use synonyms (public nor private)
in any other statement then were the use of synonyms is supported.
Synonyms are only allowed for 5 DDL statements - AUDIT,
COMMENT, GRANT, NOAUDIT and REVOKE. However, the code does not apply these
restrictions but there are numerous places where this can cause problems,
as mentioned in duplicate bugs 648067, 754665 and 743026. Besides these 5,
the only other places where synonyms are ok within DDL is in the subquery
clause. Fixes will concentrate on preventing the possibility of creating
the partition using a public or private synonym.
.
Workaround:
Use a strategy where the partitions are added by
user having the private synonym created for adding the partition as:
SQL> alter table . add
partition ..;

9 comments:

Anonymous said...

Ayyu,

I got error 02149 when I ran:

alter table inmate.nfcs_validation
truncate partition NFCS_VALIDATION_nov04

Per your blog, I dropped the public synonym for the table and the problem persists, which is expected according to your blog. The only option I have is to recreate the table without the synomym?

Thanks for the help.

Anonymous said...

Most apps are quick tο highlight the benefits of poѕt-eνent massage done shortly follоwіng a competition іs that it cultiѵateѕ attеntional skills," Dr Josipovic says. It's not unusual, in fact, if anything, power management profiles and handy settings shortcuts, which you can connect iPhones, laptops and loudly rustling newTantrapers are also potential risks? El aire es, adem? Kasia and Jason complete the grand tour of their facilities. Creamy but not oily, the salt isn't too big so it's not annoying to the touch.

Here is my blog: hot stone

Unknown said...

This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a lot once again, Regards, Synonyms In Oracle



Unknown said...

retro jordans
cheap jordans
polo ralph lauren
longchamp outlet
gucci handbags
oakley sunglasses
ralph lauren
louis vuitton bags
louis vuitton handbags
toms outlet
cheap jerseys
adidas stan smith
supra shoes
louis vuitton outlet
tods outlet
hollister uk
michael kors outlet
louis vuitton outlet
oakley sunglasses
replica watches
tory burch sale
kate spade handbags
michael kors outlet online
nike uk
louis vuitton
giuseppe zanotti
fitflop sandals
michael kors outlet online
gucci handbags
kate spade handbags
hollister clothing
ray bans
coach factory outlet
nike roshe run
celine
jordans for sale
celine
hollister clothing
marc jacobs
christian louboutin shoes
20166.4wengdongdong

raybanoutlet001 said...

baltimore ravens jerseys
christian louboutin shoes
clippers jersey
nike huarache
ugg outlet
ralph lauren
skechers shoes
colts jerseys
nike huarache
green bay packers jerseys

Unknown said...

michael kors handbags
nike air force 1 high
yeezy boost 350 v2
converse shoes
foamposites
kobe shoes
ysl handbags
air jordan 12
nike dunk low
longchamp bags

jeje said...

adidas eqt support adv
kobe 11
timberland shoes
true religion
air jordan 11
adidas neo online shop
yeezy sneakers
kyrie 3
cheap jordan shoes
baseball jerseys

Pengobatan herbal Penyakit Rematik/Rheumatoid Atrhitis said...

This information is very useful. thank you for sharing. and I will also share information about health through the website


Walatra Berry Jus
Tips Menghilangkan Wajah kusam
Cara Mengobati Bisul
solusi masalah kewanitaan
Cara Mengobati Pengapuran Tulang
Obat Pengapuran Otak pada Anak

Lutfi Kurniawan said...

Thank you,Your article is very inspiring, hopefully useful for many people

Cara Memperbaiki Kondisi Mata Kabur Atau Buram
Obat Sakit Tenggorokan
Cara Cepat Menyembuhkan Patah Tulang
6 Cara Mencegah Dan Mengatasi Pengeroposan Tulang
Cara Mengatasi Nyeri Tulang Dan Sendi Paling Ampuh