Re: Unable to Delete table

"balu" <nairb_at_sabc.co.za> wrote in message news:1136982300.327664.111510_at_f14g2000cwb.googlegroups.com...
> In my oracle 9i Schema there is a table called 'AREA'
>
> When I ran a DTS from SQL Server to populate some tables in my schema
> it erroneously created another table by the name 'area'. how it is
> possible to create two objects with the same name?
>
> (actually two other tables 'area_party_votes_pr' &
> 'area_party_votes_ward' was also created. For convenience am referring
> only to one table 'area')
>
>
> More trouble is that , I am not able to delete the new 'area' table.
>
> pls see the below sqls
>
>
> ers2006_at_SABCDB01.WORLD>select * from user_tables where table_name like
> 'ar%' or table_name like 'AR%';
>
> TABLE_NAME TABLESPACE_NAME
> CLUSTER_NAME IOT_NAME
> ------------------------------ ------------------------------
> --------------------------
> AREA ERS2006
>
> AREA_PARTY_VOTES_PR ERS2006
>
> AREA_PARTY_VOTES_WARD ERS2006
>
> area ERS2006
>
> area_party_votes_pr ERS2006
>
> area_party_votes_ward ERS2006
>
>
>
>
> ers2006_at_SABCDB01.WORLD>drop table area;
>
> Table dropped.
>
> ers2006_at_SABCDB01.WORLD>select * from user_tables where table_name like
> 'ar%' or table_name like 'AR%';
>
> TABLE_NAME TABLESPACE_NAME
> CLUSTER_NAME IOT_NAME
> ------------------------------ ------------------------------
> ------------------------------ -----------
> AREA_PARTY_VOTES_PR ERS2006
>
> AREA_PARTY_VOTES_WARD ERS2006
>
> area ERS2006
>
> area_party_votes_pr ERS2006
>
> area_party_votes_ward ERS2006
>
>
> -- this shows the table 'AREA' was dropped.
>
> -- now again I shall execute the same statement in an attempt to drop
> 'area'
>
>
> ers2006_at_SABCDB01.WORLD>drop table area;
> drop table area
> *
> ERROR at line 1:
> ORA-00942: table or view does not exist
>
> -- any idea how to delete the 'area' table
>

what has happened here is that the DTS process from SQL Server created tables with the names enclosed in double-quotes, i.e., "area". that is why you see two different tables (with different names) when you select from the data dictionary -- one created without double-quotes on the name, which causes Oracle to convert the name to upper case and apply naming convention rules to it, one created with double-quotes on the name, which caues Oracle to use the name as is.

whenever an object (table, column, view, etc.) is created with a double-quoted name, it must always be referenced with a double-quoted name

for instance:

SQL> create table "lower case with spaces" (id number);

Table created.

SQL> create table " " (justspaces number);

Table created.

SQL> describe lower case with spaces
Usage: DESCRIBE [schema.]object[@db_link]

SQL> describe "lower case with spaces"

 Name                                      Null?    Type
 ----------------------------------------- -------- ---------
 ID                                                 NUMBER

SQL> describe "     "
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------
 JUSTSPACES                                         NUMBER

SQL> drop table "lower case with spaces";

Table dropped.

SQL> drop table " ";

Table dropped.

++ mcs Received on Wed Jan 11 2006 - 06:42:01 CST