How to drop unique index
Hi Gurus, I tried to drop unique index using ALTER TABLE table_name DROP UNIQUE (column_name); unsuccessfully. How should I drop the unique index ? Please advise. Thanks. SQL SELECT * FROM USER_IND_COLUMNS WHERE TABLE_NAME='SPY_ADHOC_PAYMENT'; INDEX_NAME TABLE_NAME COLUMN_NAME -- -- -- U_SPYADH_2 SPY_ADHOC_PAYMENT STDNAME SQL SELECT INDEX_NAME,TABLE_OWNER,TABLE_NAME,UNIQUENESS FROM USER_INDEXES WHERE TABLE_NAME='SPY_ADHOC_PAYMENT'; INDEX_NAME TABLE_OWNERTABLE_NAME UNIQUENES -- -- U_SPYADH_2 SPYSPY_ADHOC_PAYMENT UNIQUE SQL ALTER TABLE SPY.SPY_ADHOC_PAYMENT DROP UNIQUE (STDNAME); ALTER TABLE SPY.SPY_ADHOC_PAYMENT DROP UNIQUE (STDNAME) * ERROR at line 1: ORA-02442: Cannot drop nonexistent unique key SQL ALTER TABLE SPY.SPY_ADHOC_PAYMENT DROP CONSTRAINT U_SPYADH_2; ALTER TABLE SPY.SPY_ADHOC_PAYMENT DROP CONSTRAINT U_SPYADH_2 * ERROR at line 1: ORA-02443: Cannot drop constraint - nonexistent constraint Regds, New Bee -Original Message- From: Manivannan.M [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 30, 2001 2:20 PM To: Multiple recipients of list ORACLE-L Subject:RE: catrep.sql Hi It will be there under $ORACLE_HOME/rdbms/admin regards, Manivannan.M -- On Tue, 29 May 2001, Timajo, Joel - Equicom wrote: hello oracle gurus! i just tried a default installation of oracle 8.0.6 to our hp-unix machine. i followed step by step the procedures as stated in the oracle installation guide. my problem is when it comes to the step (pp 4-2 of the installation guide) that says run the catrep.sql. where can i find this sql script? thanks! oracle newbie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Manivannan.M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CHAN Chor Ling Catherine (CSC) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to drop unique index
Suggestion given by Magesh should work for a non-constraint index. If it is index created by Oracle during the time when you created your unique constraint you won't be able to get rid of that index until you eliminate your constraint. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, May 30, 2001 3:31 AM Try out this Drop index U_SPYADH_2 ; Regards Magesh - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, May 30, 2001 5:50 PM Hi Gurus, I tried to drop unique index using ALTER TABLE table_name DROP UNIQUE (column_name); unsuccessfully. How should I drop the unique index ? Please advise. Thanks. SQL SELECT * FROM USER_IND_COLUMNS WHERE TABLE_NAME='SPY_ADHOC_PAYMENT'; INDEX_NAME TABLE_NAME COLUMN_NAME -- -- -- U_SPYADH_2 SPY_ADHOC_PAYMENT STDNAME SQL SELECT INDEX_NAME,TABLE_OWNER,TABLE_NAME,UNIQUENESS FROM USER_INDEXES WHERE TABLE_NAME='SPY_ADHOC_PAYMENT'; INDEX_NAME TABLE_OWNERTABLE_NAME UNIQUENES -- -- U_SPYADH_2 SPY SPY_ADHOC_PAYMENT UNIQUE SQL ALTER TABLE SPY.SPY_ADHOC_PAYMENT DROP UNIQUE (STDNAME); ALTER TABLE SPY.SPY_ADHOC_PAYMENT DROP UNIQUE (STDNAME) * ERROR at line 1: ORA-02442: Cannot drop nonexistent unique key SQL ALTER TABLE SPY.SPY_ADHOC_PAYMENT DROP CONSTRAINT U_SPYADH_2; ALTER TABLE SPY.SPY_ADHOC_PAYMENT DROP CONSTRAINT U_SPYADH_2 * ERROR at line 1: ORA-02443: Cannot drop constraint - nonexistent constraint Regds, New Bee -Original Message- From: Manivannan.M [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 30, 2001 2:20 PM To: Multiple recipients of list ORACLE-L Subject: RE: catrep.sql Hi It will be there under $ORACLE_HOME/rdbms/admin regards, Manivannan.M -- -- -- On Tue, 29 May 2001, Timajo, Joel - Equicom wrote: hello oracle gurus! i just tried a default installation of oracle 8.0.6 to our hp-unix machine. i followed step by step the procedures as stated in the oracle installation guide. my problem is when it comes to the step (pp 4-2 of the installation guide) that says run the catrep.sql. where can i find this sql script? thanks! oracle newbie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Manivannan.M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CHAN Chor Ling Catherine (CSC) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: magesh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: lyudah INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public
Re: How to drop unique index
Hi, Did you try : SQL drop index index_name ; ?? HIH, Antonio Belloni CHAN Chor Ling Catherine (CSC) [EMAIL PROTECTED]@fatcity.com on 30/05/2001 05:50:25 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Hi Gurus, I tried to drop unique index using ALTER TABLE table_name DROP UNIQUE (column_name); unsuccessfully. How should I drop the unique index ? Please advise. Thanks. SQL SELECT * FROM USER_IND_COLUMNS WHERE TABLE_NAME='SPY_ADHOC_PAYMENT'; INDEX_NAME TABLE_NAME COLUMN_NAME -- -- -- U_SPYADH_2 SPY_ADHOC_PAYMENT STDNAME SQL SELECT INDEX_NAME,TABLE_OWNER,TABLE_NAME,UNIQUENESS FROM USER_INDEXES WHERE TABLE_NAME='SPY_ADHOC_PAYMENT'; INDEX_NAME TABLE_OWNERTABLE_NAME UNIQUENES -- -- U_SPYADH_2 SPYSPY_ADHOC_PAYMENT UNIQUE SQL ALTER TABLE SPY.SPY_ADHOC_PAYMENT DROP UNIQUE (STDNAME); ALTER TABLE SPY.SPY_ADHOC_PAYMENT DROP UNIQUE (STDNAME) * ERROR at line 1: ORA-02442: Cannot drop nonexistent unique key SQL ALTER TABLE SPY.SPY_ADHOC_PAYMENT DROP CONSTRAINT U_SPYADH_2; ALTER TABLE SPY.SPY_ADHOC_PAYMENT DROP CONSTRAINT U_SPYADH_2 * ERROR at line 1: ORA-02443: Cannot drop constraint - nonexistent constraint Regds, New Bee -Original Message- From: Manivannan.M [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 30, 2001 2:20 PM To: Multiple recipients of list ORACLE-L Subject: RE: catrep.sql Hi It will be there under $ORACLE_HOME/rdbms/admin regards, Manivannan.M -- On Tue, 29 May 2001, Timajo, Joel - Equicom wrote: hello oracle gurus! i just tried a default installation of oracle 8.0.6 to our hp-unix machine. i followed step by step the procedures as stated in the oracle installation guide. my problem is when it comes to the step (pp 4-2 of the installation guide) that says run the catrep.sql. where can i find this sql script? thanks! oracle newbie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Manivannan.M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CHAN Chor Ling Catherine (CSC) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to drop unique index
If it is an unique index that enforces unique constraint you cannot simply drop it You have to drop unique constraint alter table tablename drop constraint constraintname; an example follows SQL desc qaqa1 NameNull?Type --- COL1 NUMBER(5) COL2 VARCHAR2(4000) SQL select * from user_constraints where table_name = 'QAQA1'; no rows selected SQL select * from user_indexes where table_name = 'QAQA1'; no rows selected SQL alter table qaqa1 add constraint zzz unique (col1); Table altered. SQL select * from user_constraints where table_name = 'QAQA1'; OWNER CONSTRAINT_NAMEC TABLE_NAME -- -- - --- MANTAOWNER ZZZU QAQA1 SQL select * from user_indexes where table_name = 'QAQA1'; INDEX_NAME INDEX_TYPE TABLE_OWNER -- --- ZZZNORMAL MANTAOWNER SQL drop index zzz; drop index zzz * ERROR at line 1: ORA-02429: cannot drop index used for enforcement of unique/primary key SQL alter table qaqa1 drop constraint zzz; Table altered. SQL select * from user_constraints where table_name = 'QAQA1'; no rows selected SQL select * from user_indexes where table_name = 'QAQA1'; no rows selected Gints Plivna -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to drop unique index
that's not dropping a unique index, that's dropping a column! to drop an index: drop index indexname; doesn't matter if it is unique or not. If you are trying to drop a unique constraint, that's different. first find the name of the unique constraint on that table select constraint_name from user_constraints where constraint_type='U'; will give you all the unique constraints on that table. If there is more than one unique constraint, check in user_cons_columns to match the column to the constraint. Then you can alter table tablename drop constraint constraintname; Rachel From: CHAN Chor Ling Catherine (CSC) [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: How to drop unique index Date: Wed, 30 May 2001 00:50:25 -0800 Hi Gurus, I tried to drop unique index using ALTER TABLE table_name DROP UNIQUE (column_name); unsuccessfully. How should I drop the unique index ? Please advise. Thanks. SQL SELECT * FROM USER_IND_COLUMNS WHERE TABLE_NAME='SPY_ADHOC_PAYMENT'; INDEX_NAME TABLE_NAME COLUMN_NAME -- -- -- U_SPYADH_2 SPY_ADHOC_PAYMENT STDNAME SQL SELECT INDEX_NAME,TABLE_OWNER,TABLE_NAME,UNIQUENESS FROM USER_INDEXES WHERE TABLE_NAME='SPY_ADHOC_PAYMENT'; INDEX_NAME TABLE_OWNERTABLE_NAME UNIQUENES -- -- U_SPYADH_2 SPYSPY_ADHOC_PAYMENT UNIQUE SQL ALTER TABLE SPY.SPY_ADHOC_PAYMENT DROP UNIQUE (STDNAME); ALTER TABLE SPY.SPY_ADHOC_PAYMENT DROP UNIQUE (STDNAME) * ERROR at line 1: ORA-02442: Cannot drop nonexistent unique key SQL ALTER TABLE SPY.SPY_ADHOC_PAYMENT DROP CONSTRAINT U_SPYADH_2; ALTER TABLE SPY.SPY_ADHOC_PAYMENT DROP CONSTRAINT U_SPYADH_2 * ERROR at line 1: ORA-02443: Cannot drop constraint - nonexistent constraint Regds, New Bee -Original Message- From: Manivannan.M [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 30, 2001 2:20 PM To: Multiple recipients of list ORACLE-L Subject:RE: catrep.sql Hi It will be there under $ORACLE_HOME/rdbms/admin regards, Manivannan.M -- On Tue, 29 May 2001, Timajo, Joel - Equicom wrote: hello oracle gurus! i just tried a default installation of oracle 8.0.6 to our hp-unix machine. i followed step by step the procedures as stated in the oracle installation guide. my problem is when it comes to the step (pp 4-2 of the installation guide) that says run the catrep.sql. where can i find this sql script? thanks! oracle newbie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Manivannan.M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CHAN Chor Ling Catherine (CSC) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services
RE: How to drop unique index
Thanks to all who replied. The command Drop Unique index_name works. Regds, New Bee -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CHAN Chor Ling Catherine (CSC) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
