For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
I am not exporting package scirpt both in DDL and lb genobject in UTF-8 for lb the changelog is in UTF-8 however the package in the wrpped xml NOT
Unique constraints automatically create (or "capture," if one already exists) an index, so just create the constraint - no need for a separate index. The underlying index may be unique or not, depending upon whether the constraint is deferrable. So, you can make the error go away by not creating the index in the first place
Having said that, it looks like you've discovered a situation where Oracle gets confused, and filing an SR with Oracle could either get that issue resolved or explained as to why it's not an issue.
John
First of all you have show_orders in index and show_order in constraint. I'll assume it is a typo. Secondly, when you create constraint without specifying reinforcing index Oracle looks for existing index on same set of columns in ASCending order. If such index doesn't exists (and that's your case since one of your index columns is in DESCending order), Oracle tries to create one with same name as the constraint. That's why you get ORA-00955: name is already used by an existing object. In general, you can explicitly specify index name to force Oracle using existing index. But it will not help you. If you issue
alter table orders add constraint orders_year_show_uq unique(year, show_orders) using index orders_year_show_uq;
You'll get ORA-14196: Specified index cannot be used to enforce the constraint.
Anyway, Oracle doesn't support using DESC indexes for PK/UK.
SY.
Ok, thank you for your answer.