Oracle: I drop a table, but select still show it

by

The question:

I see today a strange thing on my oracle db.

SQL> select tablespace_name, table_name from user_tables;

TABLESPACE_NAME                                       |TABLE_NAME
------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSTEM                                            |lavoratori2

1 riga selezionata.

Passati: 00:00:00.07
SQL> drop table lavoratori2 purge;
drop table lavoratori2 purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


Passati: 00:00:00.03

I have tried this as non-sys user, now I try as SYS and … same thing.

SQL> select tablespace_name, table_name from all_tables where table_name ='lavoratori2';

TABLESPACE_NAME
------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
SYSTEM
lavoratori2


SQL> drop table lavoratori2;
drop table lavoratori2
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

How to solve?

I tried to empty the trash bin, but the immortal table still exist

SQL> purge recyclebin;

Recyclebin purged.

SQL> select tablespace_name, table_name from all_tables where table_name ='lavoratori2';

TABLESPACE_NAME
------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
SYSTEM
lavoratori2

another strange thing..

SQL> select * from lavoratori2;
select * from lavoratori2
              *
ERROR at line 1:
ORA-00942: table or view does not exist

If I did

select * from "lavoratori2";

return

ORA-00942: table or view does not exist

If I did

SQL> select owner from all_tables where table_name ='lavoratori2';

return the correct name of non-sys user which is the owner of the table.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

First, if you are not connected as the owner of the table, you should always prefix your table name with the owner. It doesn’t hurt if you actually are connected as the owner. Connected as THE_USER (which could be SYS or any other user),

select * from lavoratori2;
--is equivalent to
select * from THE_USER.lavoratori2;

drop table lavoratori2;
--is equivalent to
drop table THE_USER.lavoratori2;

So it only works if THE_USER is the owner of the table.

Second, I suspect the case of the name is also important. Oracle’s default for object names is uppercase, so if you use lowercase you need to explicitly escape it, otherwise it is considered as uppercase:

select * from lavoratori2;
--is equivalent to
select * from "LAVORATORI2";

drop table lavoratori2;
--is equivalent to
drop table "LAVORATORI2";

Your query shows that your table name is lowercase, so it should be "lavoratori2".

To recap, try this (replace THE_USER with the actual owner of the table):

select * from THE_USER."lavoratori2";

drop table THE_USER."lavoratori2" purge;


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment