RE: ORA-00955: name is already used by an existing object

You will get ORA-12992 if there was FK referencing to column you attempted to drop.

There are bugs related to dropping columns from a table with LOB datatypes. This is very bizarre in 8i.
I have experienced it while testing this new feature....

Here is a reproducible case on all versions of 8i, from Metalink Note for Bug# 1769244. (This works fine in 9i, however).

create table mstest (col1 varchar2(4000), col2 varchar2(4000)); alter table mstest add (col1_temp varchar2(4000)); alter table mstest drop column col1 ;
alter table mstest add (col1 clob);
alter table mstest drop column col1_temp; alter table mstest add (col2_temp varchar2(4000)); alter table mstest drop column col2;
alter table mstest add (col2 clob);
-- ORA-955 returned for following....
alter table mstest drop column col2_temp;

But if you changed the above sequence of statements to the following, it works in 8i:

create table mstest (col1 varchar2(4000), col2 varchar2(4000)); alter table mstest add (col1_temp varchar2(4000)); alter table mstest drop column col1 ;
alter table mstest add (col1 clob);
REM drop column col1_temp statement moved to next to last line alter table mstest add (col2_temp varchar2(4000)); alter table mstest drop column col2;
alter table mstest add (col2 clob);
alter table mstest drop column col1_temp; alter table mstest drop column col2_temp; -- no error generated.

HTH,

• Kirti

-----Original Message-----
Sent: Monday, January 20, 2003 10:05 AM
To: Multiple recipients of list ORACLE-L

off the top of my head, although I would expect a different error... are there any foreign key references to the column?

Any other column in any other table which has a column whose name matches for the first 15-20 characters as this one?

• "Krishnaswamy, Ranganath" <Ranganath.Krishnaswamy_at_blr.hpsglobal.com> wrote:
  > I have the following table in Oracle 8.1.7:
  >
  > Name Null? Type
  > ----------------------------------------- -------- --------------
  > DIENSTLEISTUNGSOBJEKTOID VARCHAR2(23)
  > OID NOT NULL VARCHAR2(23)
  > AUMESSWERTE VARCHAR2(23)
  > AUART NUMBER(10)
  > BAUJAHRBEGINN NUMBER(10)
  > BAUJAHRENDE NUMBER(10)
  > BEZUGSNUMMER NUMBER(10)
  > CHECKBOXMANUELL NUMBER(1)
  > ENTSPRAUSDRUCK NUMBER(10)
  > GETRIEBETYP VARCHAR2(10)
  > HERSTELLERTEXT VARCHAR2(18)
  > HUBRAUMCCM NUMBER(10)
  > HUBRAUMLITER NUMBER
  > LEISTUNGKW NUMBER(10)
  > MINDESTOKTANZAHL NUMBER(10)
  > MODELL VARCHAR2(20)
  > MODELLJAHR VARCHAR2(11)
  > MODELLVARNUMMER NUMBER(10)
  > MOTORCODE VARCHAR2(18)
  > SPEZAUSRUESTUNG VARCHAR2(25)
  > ZYLINDERZAHL NUMBER(10)
  > CLSTYPE NOT NULL VARCHAR2(40)
  > MESSUNGSART NUMBER(10)
  > ANZAHLTEILSCHAEDEN NUMBER(10)
  > BESICHTIGUNGSBEDINGUNGEN VARCHAR2(23)
  > DURCHGEFUEHRTEREPARATUREN VARCHAR2(23)
  > EINGEBAUTENTAUSCHAGGREGATKOPF VARCHAR2(23)
  > ERSATZTEILE VARCHAR2(23)
  > FZALLGEMEINZUSTAND VARCHAR2(23)
  > GEBRAUCHSSCHADENKOPF VARCHAR2(23)
  > KALKULATIONBETRAEGE VARCHAR2(23)
  > LACKAZT VARCHAR2(23)
  > LACKMATERIAL VARCHAR2(23)
  > LACKIERUNG VARCHAR2(23)
  > LAUFLEISTUNG VARCHAR2(23)
  > LOHNKOSTEN VARCHAR2(23)
  > MECHANIK VARCHAR2(23)
  > NACHSCHADEN VARCHAR2(23)
  > NOTWREPARATURKOSTENINCLMWST NUMBER(1)
  > NOTWENDIGENTAUSCHAGGREGATKOPF VARCHAR2(23)
  > REPARTURWEG VARCHAR2(23)
  > SCHADENDATEN VARCHAR2(23)
  > SCHADENBESCHREIBUNG VARCHAR2(23)
  > SONSTIGEKALKDATEN VARCHAR2(23)
  > UMBAUKOSTEN VARCHAR2(23)
  > VERSCHLEISSSCHADENKOPF VARCHAR2(23)
  > VORGANGSDATEN VARCHAR2(23)
  > VORSCHAEDEN VARCHAR2(23)
  > ZUORDNUNG VARCHAR2(23)
  > ZUSTAND VARCHAR2(23)
  > MESSWERTHERKUNFT NUMBER(10)
  > MESSWERTLISTAKTIV NUMBER(1)
  > FEHLENDETEILE VARCHAR2(23)
  > BEWERTUNGERGEBNIS VARCHAR2(23)
  > WERTKORREKTUREN VARCHAR2(23)
  > BEMERKUNG VARCHAR2(4000)
  > NUMMER VARCHAR2(40)
  > VORGANG VARCHAR2(40)
  > AUDATEXUEBERNEHMEN NUMBER(1)
  > BAUMUSTER VARCHAR2(100)
  > HERSTELLER VARCHAR2(100)
  > NFZAUSSTATTUNG VARCHAR2(23)
  > RADSTAND1 NUMBER(10)
  > RADSTAND2 NUMBER(10)
  > VERKAUFSBEZEICHNUNG VARCHAR2(100)
  > FZALLGEMEINZUSTANDBEMERKUNG CLOB
  > FZALLGEMEINZUSTANDCBXCODE NUMBER(10)
  > NICHTMITBEWERTETETEILE CLOB
  > EREIGNISCBX VARCHAR2(40)
  > FAHRER VARCHAR2(20)
  > FAHRERORT VARCHAR2(30)
  > FAHRERSTRASSE VARCHAR2(30)
  > HERGANGSSCHILDERUNG CLOB
  > HERGANGSSCHILDERUNGLTCBX VARCHAR2(40)
  > NACHSCHADENANGABENCBX VARCHAR2(40)
  > NACHSCHADENBEMERKUNG CLOB
  > POLAUFNAHMEARTCBX VARCHAR2(40)
  > POLAUFNAHMEARTTEXT VARCHAR2(30)
  > POLAUFNAHMEDURCH VARCHAR2(30)
  > SCHADENORT VARCHAR2(30)
  > SCHADENSTRASSE VARCHAR2(30)
  > SCHADENUNFALLDATUMCBX VARCHAR2(40)
  > SCHADENUNFALLDATUMSTR VARCHAR2(10)
  > SCHADENUNFALLORT VARCHAR2(30)
  > VORGANGSBEMERKUNGEN CLOB
  > UMBAUKOSTENBEMERKUNG CLOB
  > UMBAUKOSTENMMWST NUMBER
  > UMBAUKOSTENOMWST NUMBER
  > UMBAUKOSTENZUBEHOERBITS NUMBER(19)
  > ZUORDNUNGBEMERKUNG CLOB
  > ZUORDNUNGCBXCODE NUMBER(10)
  > VORGANGSBEMERKUNG CLOB
  > VORGANGSCBX VARCHAR2(40)
  > VORGANGSNUMMER VARCHAR2(20)
  > BESONDERES CLOB
  > UMBAUKOSTENAUDATEXKALKRAW CLOB
  > MESSGERAETETYP VARCHAR2(20)
  > PRGVERSION VARCHAR2(20)
  > OBDAUSLESEGERAET VARCHAR2(20)
  > OBDGERAETPRGVERSION VARCHAR2(20)
  >
  > When I issue the below command I am getting ORA-00955 error:
  >
  > SQL> alter table feststellung drop column FZALLGEMEINZUSTANDCBXCODE;
  > alter table feststellung drop column FZALLGEMEINZUSTANDCBXCODE
  > *
  > ERROR at line 1:
  > ORA-00955: name is already used by an existing object
  >
  > I observe no locks on the object. Inspite of that it is not allowing
  > me to
  > drop the column. Could anybody let me know as to what might be the
  > reason
  > for encountering this error and how do I resolve the same? Any help
  > in this
  > regard is very much appreciated.
  >
  > Thanks and Regards,
  >
  > Ranganath
  > WARNING: The information in this message is confidential and may be
  > legally
  > privileged. It is intended solely for the addressee. Access to this
  > message
  > by anyone else is unauthorised. If you are not the intended
  > recipient, any
  > disclosure, copying, or distribution of the message, or any action or
  > omission taken by you in reliance on it, is prohibited and may be
  > unlawful.
  > Please immediately contact the sender if you have received this
  > message in
  > error. Thank you.
  > --
  > Please see the official ORACLE-L FAQ: http://www.orafaq.net
  > --
  > Author: Krishnaswamy, Ranganath
  >

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).