Re: Dropped table still has name in use

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Fri, 30 Apr 2004 21:23:37 -0700
Message-ID: <1083385412.489365@yasure>

David Best wrote:

> "KevJohnP" <nospam_at_nowhere.com> wrote in message
> news:qdlkc.42$8J.1856_at_news.xtra.co.nz...
>

>>anonymous wrote:
>>
>>
>>>I used the TOAD feature of "Rebuild Table" on a table I am experimenting
>>>with (no important data in it).  It renamed the table to table_x ok,

>
> copied
>

>>>the data to it, dropped the original table, then when it went to

>
> re-create
>

>>>the table, it gave ORA-00955: name is aready used by an existing object.

>
> At
>

>>>the time, I was also testing an alter schema DDL trigger which had

>
> problems,
>

>>>so it's possible this had some interaction (I've since dropped the

>
> trigger).
>

>>>A select from all_objects and even sys.obj$ looking for the table name,
>>>returns nothing.  But trying to create even a dummy table using that

>
> name
>

>>>gives the error.
>>>
>>>Any ideas?  TIA.  Oh, Oracle 9.2.0.1
>>>
>>>
>>>
>>
>>Maybe post your create table statement, possible causes that spring to
>>mind are that it is not the table name that is the problem, but a
>>constraint or similar on your renamed table.

>
>
> I dropped the renamed table and all triggers/constraints.
>
> Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
> Connected as pmg
>
> SQL> select count(*) from all_objects where object_name = 'UTL_TABLES';
>
> COUNT(*)
> ----------
> 0
>
> SQL> create table utl_tables (dmy number);
>
> create table utl_tables (dmy number)
>
> ORA-00955: name is already used by an existing object

Just on a chance ... try this:

SELECT owner, table_name
FROM dba_tables
WHERE LOWER(TABLE_NAME) LIKE '%utl_tables%';

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Fri Apr 30 2004 - 23:23:37 CDT