Error in Table Creation and Dropping a Table

[vivek_ku]

Hi,

I am new to Oracle and have 2 small problems.

1. Whenever I drop a table and then re-create it, the table is created yet an error message is flashed:

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

I would like to know why is this so, for I did not get a proper answer at OTN.

2. While adding some columns to a table (in an order where they should appear and not at the tail end which is Oracle's default), I copied all data (from say, X table) to a new table (Table Y) with the type of column order that I wished. As I wish to retain the old name of the table (X), I need to drop this table, re-create it having new structure (similar to Y) and then copy back data from Y to X. It just does not happen. The error message is:

ORA-02449: unique/primary keys in table referenced by foreign keys

In fact I have disabled the Foreign Key constraint in the concerned table (through OEM).

Can someone help me please?

Regards.

[fraze]

1. Are you sure you dropped the correct table. To check run
select * from dba_tables where table_name = 'XXXXXX';

2. After table Y has been created and populated you can drop the table X by
drop table X cascade constraints;

This will drop the foreign key constraints to the table.

You can then rename table Y to table X

alter table Y rename to X;

You'll then need to re-create the foriegn key constraints to it.

[vivek_ku]

Thanks Fraze.


1. Yes, I am sure I have dropped the right table (I did it with more than 1 table before with the same result). By the way, as I issued the suggested command:

select * from dba_tables where table_name = 'XXXXXX';

for one of the tables, it displays the table name twice. I can't decipher what does it really mean since there is only one table in my schema by that particular name.

2. I am going to try it out and let you know tomorrow.

Thanks.

[fraze]

1. In that case theres probably a table with the same name in a seperate schema - check the owner column of dba_tables out. When you drop the table qualify the schema name i.e.
drop table myschema.table_name

[vivek_ku]

Thanks Fraze. You are right.

OK, tables can be created by different users (in different schemas) having same names. That's fine!

However, if I log into my own schema and create a table by some name and, the name has already been used by some other user (in some other schema), why does Oracle have to inform me about it and that too with some Error message? Any idea?

-Vivek

[davey23uk]

its probably best if you show us a cut and paste from sql*plus showing your exact problem as you can drop and create tables as many times as you want without an error

[rajabalachandra]

hi,
this is from an sqlplus screen.

SQL> create table "raja" (name varchar2(10));

Table created.

SQL>
SQL> create table "Raja" (name varchar2(10));

Table created.

SQL>
SQL> create table "RAja" (name varchar2(10));

Table created.

SQL> select table_name from user_tables where upper(table_name) like 'RAJA';

TABLE_NAME
------------------------------
RAja
Raja
raja

observe the case and quotes in the table names;

check for the case sensitivity in your table names.

-Raja

[vivek_ku]

Hi,

I have been using SQL scripts for creating tables, though I don't think it can ever make a difference. However, as I issued command suggested by Rajabalchandra, it did not display duplicate table names.

I think, I should have been more cautious towards table names (case sensitivity). I guess, perhaps I did commit mistakes here.

Thanks dear friends for the kind help!

Sincere regards,

-Vivek