1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-00904 Invalid Identifier: Unable to drop a table

Discussion in 'SQL PL/SQL' started by Evert, Mar 11, 2011.

  1. Evert

    Evert Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    hello,

    when i try to drop a table this fails with ora-00904 invalid identifier

    can someone help me?
     
  2. ora

    ora Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Dear Evert,
    ORA-00904 string: invalid identifier

    Cause: The column name entered is either missing or invalid.

    Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #.

    If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.

    I think you would have created using double quotes.....

    Regards.....
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Just show us
    1. Description of tables (Desc <tablename>) and
    2. Drop command and Error message.

    This will reveal the issue.. hopefully.
     
  4. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    You quoted the wrong guy! :)
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Thanks, I corrected that mistake ..
     
  6. Evert

    Evert Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    hello Raj,

    i can give you this information:

    SQL*Plus: Release 10.2.0.3.0 - Production on Ma Mrt 14 09:13:59 2011

    Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


    Verbonden met:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
    With the Partitioning and Data Mining options

    SQL> drop table weggooien1;
    drop table weggooien1
    *
    FOUT in regel 1:
    .ORA-00904: "": ongeldige ID


    SQL> desc weggooien1
    Naam Null? Type
    ----------------------------------------- -------- ----------------------------
    WEG2 VARCHAR2(1 CHAR)

    SQL>

    Evert
     
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    try the following query and show us the query and output.

    Code (SQL):
    SELECT TABLE_NAME FROM user_tables WHERE TABLE_NAME='weggooien1';
     
  8. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Btw, I hope this error message is in Dutch language :)
     
  9. Evert

    Evert Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    hello Raj

    SQL> SELECT TABLE_NAME FROM user_tables WHERE TABLE_NAME='weggooien1';

    Er zijn geen rijen geselecteerd.

    Evert
     
  10. Evert

    Evert Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    hello Raj,

    when i execute the last statement whith table_name in uppercase , then is the result of the query:

    select table_name from user_tables where table_name='WEGGOOIEN1'
    SQL> /

    TABLE_NAME
    --------------------------------------------------------------------------------
    WEGGOOIEN1

    SQL>

    Evert
     
  11. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Looks strange.. anyway, try with

    Code (SQL):
    DROP TABLE "WEGGOOIEN1";
     
     
  12. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    And if it is not working. Just give the o/p for

    Code (SQL):
    DESC sys.mon_mods$
    from sqlplus after connecting to SYS or DBA user.
     
  13. Evert

    Evert Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    hello Raj,
    my output:
    SQL> drop table "WEGGOOIEN1";
    drop table "WEGGOOIEN1"
    *
    FOUT in regel 1:
    .ORA-00904: "": ongeldige ID


    SQL> exit
    Verbinding met Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
    Production
    With the Partitioning and Data Mining options is verbroken.
    u21:/home/oracle SID=gdp 0> sqlplus

    SQL*Plus: Release 10.2.0.3.0 - Production on Ma Mrt 14 14:52:31 2011

    Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

    Voer gebruikersnaam in: / as sysdba

    Verbonden met:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
    With the Partitioning and Data Mining options

    SQL> desc sys.mon_mods$
    Naam Null? Type
    ----------------------------------------- -------- ----------------------------
    OBJ# NUMBER
    INSERTS NUMBER
    UPDATES NUMBER
    DELETES NUMBER
    TIMESTAMP DATE
    FLAGS NUMBER
    DROP_SEGMENTS NUMBER

    SQL>

    Evert
     
  14. ppargai

    ppargai Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hi Evert,
    Are you trying to drop table from correct schema?
    May be your table does not exist in the current schema from where you are trying to drop table.
     
  15. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    If I remember correctly there's a bug in 10.2.0.3 that can arise in such situations; you may need to patch this database to 10.2.0.4 to resolve the issue.
     
  16. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    It seems to be a bug with the current version . Please try to drop some other table in the same schema. If issue still persists consider upgrading to new version as Zargon suggested.
     
  17. Evert

    Evert Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    hello Pargai,

    the table I want to drop is in the correct schema

    Evert
     
  18. Evert

    Evert Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Raj, i am able to drop another table in the same schama, the isue still persists.
    i will try to move the table to another tablespace, and drop that whole tablespace