1. The Forum has been upgraded to a modern and advanced system. Please read the announcement about this update.
  2. 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-00942: table or view does not exist

Discussion in 'SQL PL/SQL' started by prashant, Jan 22, 2009.

  1. prashant

    prashant Forum Advisor

    Messages:
    85
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    New Delhi, India
    Hi,

    I am facing a small issue here in one of our Dev schemas during cleaning up the schema.

    When i do a select on user_objects i get the following results:-
    Code (Text):

    SQL> select object_type,count(1) from user_objects group by object_type;

    OBJECT_TYPE           COUNT(1)
    ------------------- ----------
    LIBRARY                      1
    TABLE                      968
    INDEX                      802

    SQL> select count(1) from user_tables;

      COUNT(1)
    ----------
           968

    SQL> select table_name from user_tables where rownum=1;

    TABLE_NAME
    ------------------------------
    TAB_ACTION_EU

    SQL> drop table TAB_ACTION_EU purge;
    drop table TAB_ACTION_EU purge
               *
    ERROR at line 1:
    ORA-00942: table or view does not exist
     
    I also tried spooling and running the following script:-
    Code (Text):

    select 'drop table '||table_name||' purge;' from user_tables;
     
    but again i got the same message for all the tables.

    Please suggest.

    Regards,
    Prashant
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    18
    Trophy Points:
    260
    Location:
    India
    Hi Prashant, you will get the ORA-00942 error if

    1. You are trying to execute an SQL statement that references a table or view that does not exist.
    2. A synonym that is not allowed here was used.
    3. You do not have access rights to the particular object.
    4. The table or view belongs to another schema and you didn’t reference the table by the schema name.
    5. A view was referenced where a table is required.

    Now see for which of the reason you are getting the error. My feeling is that you are trying to delete a synonym for an object from another schema.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Are you able to select from those tables ?

    If no , there is a chance that you have mixed characters in the table name. Just use double quotes in the query as shown below.

    Code (Text):

    SQL> select TABLE_NAME from user_tables where TABLE_NAME like 'T%'
      2  ;

    TABLE_NAME
    ------------------------------
    TEST

    SQL> SELECT * FROM TEST;
    SELECT * FROM TEST
                  *
    ERROR at line 1:
    ORA-00942: table or view does not exist


    SQL> DROP TABLE TEST;
    DROP TABLE TEST
               *
    ERROR at line 1:
    ORA-00942: table or view does not exist


    SQL> select 'drop table '||table_name||' purge;' from user_tables where TABLE_NA
    ME like 'T%'
      2  ;

    'DROPTABLE'||TABLE_NAME||'PURGE;'
    ------------------------------------------------
    drop table TEST  purge;

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


    SQL> select 'drop table "'||table_name||'" purge;' from user_tables where TABLE_
    NAME like 'T%'
      2  ;

    'DROPTABLE"'||TABLE_NAME||'"PURGE;'
    --------------------------------------------------
    drop table "TEST " purge;

    SQL> drop table "TEST " purge;

    Table dropped.

    SQL>
     
     
  4. prashant

    prashant Forum Advisor

    Messages:
    85
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    New Delhi, India
    Hi,

    Thank you tyro , rajavu for your reply.

    The reason why i wanted to remove all the objects was due to failure during import which was due to insufficient space in system tablespace. There were a lot of lines in the logfile which says "unable to increase obj$ segment by .... in tablespace system.. ".

    As such all the objects were supposed to be dropped for refiring the import. So, i dropped schema (using "drop user <> cascade;") and recreated it. It took a total of 25 mins for it to complete for around 9000 objects.

    Regards,
    Prashant