My table half-exists. Can't drop it

Question

I have a table that is taking up quite a bit of space even though it has no rows. I also dropped it a while ago. When I try to drop it now, it says the table doesn't exist.

It shows up in user_tables. Also, all of its partitions and indexes show up in user_objects.

It's not in the recycle bin either.

What's going on here?

EDIT:

Here is the result of user_tables:

TABLE_NAME  TABLESPACE_NAME CLUSTER_NAME    IOT_NAME    STATUS  PCT_FREE PCT_USED   INI_TRANS   MAX_TRANS   INITIAL_EXTENT  NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE    FREELISTS   FREELIST_GROUPS LOGGING BACKED_UP   NUM_ROWS    BLOCKS  EMPTY_BLOCKS    AVG_SPACE   CHAIN_CNT   AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS   NUM_FREELIST_BLOCKS DEGREE  INSTANCES   CACHE   TABLE_LOCK  SAMPLE_SIZE LAST_ANALYZED   PARTITIONED IOT_TYPE    TEMPORARY   SECONDARY   NESTED  BUFFER_POOL ROW_MOVEMENT    GLOBAL_STATS    USER_STATS  DURATION    SKIP_CORRUPT    MONITORING  CLUSTER_OWNER   DEPENDENCIES    COMPRESSION COMPRESS_FOR    DROPPED READ_ONLY
jeremy_ALLK             VALID                                                   N                                            8           1      N   ENABLED         YES     N   N   NO      ENABLED NO  NO      DISABLED    YES     DISABLED            NO  NO

When I do: "drop table jeremy_ALLK", I get:

Error starting at line 7 in command:
drop table jeremy_ALLK
Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

Can you cut and paste from SQL*Plus where you are querying `user_tables` to see the table and then then attempting to drop the table causing the error? Are you sure that you're trying to drop the same table that is in `user_tables` (particularly if you used a case-sensitive table name)? Are you using the fully qualified table name in your `DROP` statement? What you're describing doesn't make sense unless your database is pretty seriously corrupted. — Justin Cave, Apr 10 '12 at 20:50

score 9 · Accepted Answer · answered Apr 10 '12 at 21:08

You're getting caught with the case sensitivity of the table name. In Oracle, all of these commands are exactly the same:

drop table jeremy_ALLK
drop table Jeremy_ALLK
drop table JEREMY_ALLK
drop table "JEREMY_ALLK"

That is to say, the table name is by default "lifted" to upper case. Since you have lower-case letters in your table name you'll have to specify the drop statement like this:

drop table "jeremy_ALLK"

The double-quotes are used whenever you have a database object named with lower-case letters or spaces(!).

score 3 · Answer 2 · answered Apr 10 '12 at 21:09

OK. The problem appears to be that you created the table with a case-sensitive table name (note that the table_name in user_tables is in mixed case). If you do that (and I would strongly suggest not doing that in the future), you need to use the case-sensitive table name everywhere. So your DROP TABLE statement would need to be

DROP TABLE "jeremy_ALLK";

My table half-exists. Can't drop it

2 Answers2