Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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:
share|improve this question

migrated from stackoverflow.com Apr 11 '12 at 13:00

This question came from our site for professional and enthusiast programmers.

    
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

2 Answers 2

up vote 8 down vote accepted

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(!).

share|improve this answer
    
That was it! THANK YOU! –  Jeremy Apr 11 '12 at 0:46

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";
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.