SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-23-2008, 09:11 AM
| ||||
| ||||
 Dropped table still has name in use 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      |
|
02-23-2008, 09:12 AM
| |||
| |||
| Re: Dropped table still has name in use 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. |
|
02-23-2008, 09:13 AM
| |||
| |||
| Re: Dropped table still has name in use "KevJohnP" <nospam@nowhere.com> wrote in message news:qdlkc.42$8J.1856@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 |
|
02-23-2008, 09:14 AM
| |||
| |||
| Re: Dropped table still has name in use David Best wrote: > "KevJohnP" <nospam@nowhere.com> wrote in message > news:qdlkc.42$8J.1856@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/e...ad/oad_crs.asp http://www.outreach.washington.edu/e...oa/aoa_crs.asp damorgan@x.washington.edu (replace 'x' with a 'u' to reply) |
|
02-23-2008, 09:14 AM
| |||
| |||
| Re: Dropped table still has name in use "Daniel Morgan" <damorgan@x.washington.edu> wrote in message news:1083385412.489365@yasure... > > Just on a chance ... try this: > > SELECT owner, table_name > FROM dba_tables > WHERE LOWER(TABLE_NAME) LIKE '%utl_tables%'; > Nope, no data found. I also tried: SELECT * FROM sys.obj$ WHERE LOWER(TABLE_NAME) LIKE '%utl_tables%'; on the off chance that maybe the dictionary has been corrupted (I don't know if that's possible), but still nothing. But the name must exist somewhere! If it is a dictionary corruption issue, where could I find some info to dig into this? Thanks, Dave |
|
02-23-2008, 09:14 AM
| |||
| |||
| Re: Dropped table still has name in use David Best wrote: > "Daniel Morgan" <damorgan@x.washington.edu> wrote in message > news:1083385412.489365@yasure... > >>Just on a chance ... try this: >> >>SELECT owner, table_name >>FROM dba_tables >>WHERE LOWER(TABLE_NAME) LIKE '%utl_tables%'; >> > > > Nope, no data found. I also tried: > > SELECT * > FROM sys.obj$ > WHERE LOWER(TABLE_NAME) LIKE '%utl_tables%'; > Well, I hope you didn't do precisely that, because obj$ doesn't actually have a column called TABLE_NAME. Just NAME will do. Regards HJR |
|
02-23-2008, 09:14 AM
| ||||
| ||||
| Re: Dropped table still has name in use "Howard J. Rogers" <hjr@dizwell.com> wrote in message news:40935421$0$4548$afc38c87@news.optusnet.com.au ... > David Best wrote: > > "Daniel Morgan" <damorgan@x.washington.edu> wrote in message > > news:1083385412.489365@yasure... > > > >>Just on a chance ... try this: > >> > >>SELECT owner, table_name > >>FROM dba_tables > >>WHERE LOWER(TABLE_NAME) LIKE '%utl_tables%'; > >> > > > > > > Nope, no data found. I also tried: > > > > SELECT * > > FROM sys.obj$ > > WHERE LOWER(TABLE_NAME) LIKE '%utl_tables%'; > > > > Well, I hope you didn't do precisely that, because obj$ doesn't actually > have a column called TABLE_NAME. Just NAME will do. > > Regards > HJR doh, you're right...I copied/edited your lines rather than paste from sqlplus because I accidentally closed the dos window too soon, my bad. But yes, I did the correct query and got no data found. Dave |
Linear Mode
