MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Haste makes waste, again …

with 5 comments

I was working on a code example for my database class, got in a hurry, and changed a table name without dropping the original table. Oops!

Naturally, I got this error message.

  LOB (administrator_photo) STORE AS admin_photo
ERROR at line 5:
ORA-00955: name IS already used BY an existing object

The LOB segment name existed but why and where. It was in the table that I forgot to drop. This query find the latent table and column while illustrating the relationship (for my students and others) between an OBJECT_NAME and SEGMENT_NAME:

SELECT TABLE_NAME, column_name
FROM   user_lobs
WHERE  segment_name = (SELECT object_name
                       FROM   user_objects
                       WHERE  object_name = UPPER('&object_name')
                       AND    object_type = 'LOB');

Now, I can grab it later because it’ll happen again. 😉 At Samy mentioned in his comment you also have the option of using ALL_ or DBA_ views when you’re a DBA.

Written by maclochlainn

January 5th, 2010 at 10:31 pm

Posted in Oracle,sql