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" <ORACLE-L_at_fatcity.com> 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" <ORACLE-L_at_fatcity.com>
> 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_OWNER TABLE_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:manivannan.m_at_tatainfotech.com]
> > 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: manivannan.m_at_tatainfotech.com
> >
> > 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: ListGuru_at_fatcity.com (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: clchan_at_nie.edu.sg
> >
> > 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: ListGuru_at_fatcity.com (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: magesh_at_garoo.ne.jp
>
> 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: ListGuru_at_fatcity.com (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: lyudah_at_home.com

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: ListGuru_at_fatcity.com (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).