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

5 Responses to 'Haste makes waste, again …'

Subscribe to comments with RSS or TrackBack to 'Haste makes waste, again …'.

  1. This is exactly what I have been looking for. Thanks for posting this.

    Tony

    8 Jul 11 at 7:56 am

  2. As the guy said above:

    “This is exactly what I have been looking for. Thanks for posting this.”

    Regards
    Martin

    Martin

    28 Aug 12 at 3:42 am

  3. Hi,

    Good post, again “This is exactly what I have been looking for. Thanks for posting this.”
    Except, I’d have used dba_lobs instead of user_lobs.

    But thanks for the info.

    Samy G.

    22 Nov 12 at 3:33 am

  4. Samy,

    As DBAs, we often live in the privileged schemas but most developers don’t. Errors like these typically occur during development not production, and shouldn’t they be resolved in the current working schema?

    Michael

    maclochlainn

    22 Nov 12 at 9:56 am

  5. I’m not connected to Quest Software in any way, but TOAD allows you to find this by looking through the indices on your schema, providing you know the object name.

    Marlon

    21 May 13 at 3:10 am

Leave a Reply