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



  • 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.



  • 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;




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2