ORA-00942: table or view does not exist

in Oracle Database; Hi, I am facing a small issue here in one of our Dev schemas during cleaning up the schema. When ...
+ Post Reply + Post New Topic
Results 1 to 4 of 4
  1. #1
    prashant is offline Forum Advisor
    Points: 2,080, Level: 27
    Join Date
    15 Jan 2009
    Location
    New Delhi, India
    Posts
    85
    Points
    2,080

    ORA-00942: table or view does not exist

    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 :
    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 :
    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. #2
    tyro's Avatar
    tyro is offline Forum Genius
    Points: 5,680, Level: 48
    Join Date
    20 Aug 2008
    Location
    India
    Posts
    368
    Points
    5,680

    Re: ORA-00942: table or view does not exist

    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. #3
    rajavu's Avatar
    rajavu is offline Forum Guru
    Points: 10,830, Level: 68
    Join Date
    13 Oct 2008
    Location
    @ Bangalore , India
    Posts
    815
    Points
    10,830

    Re: ORA-00942: table or view does not exist

    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 :
    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>

    Raj.

  4. #4
    prashant is offline Forum Advisor
    Points: 2,080, Level: 27
    Join Date
    15 Jan 2009
    Location
    New Delhi, India
    Posts
    85
    Points
    2,080

    Re: ORA-00942: table or view does not exist

    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

Other Solutions
  1. ORA-01034 ORA-27101 ORACLE not available shared memory realm does not exist
    By StryderKC in forum Server Administration and Options
    Replies: 7
    Last Post: 06-07-2010, 04:34 AM
  2. Getting Error(revision class ID already exist) while importing
    By sandeepg in forum Oracle Webcenter Suite (formerly Oracle ECM)
    Replies: 2
    Last Post: 10-22-2009, 11:02 AM
  3. Replies: 2
    Last Post: 07-18-2009, 01:01 PM
  4. ORA-00942 table or view does not exist Error in Oracle Forms
    By sakchennai in forum Oracle Forms and Reports
    Replies: 4
    Last Post: 07-08-2009, 09:02 AM
  5. View all table structure in schema
    By professional in forum SQL PL/SQL
    Replies: 6
    Last Post: 12-17-2008, 05:53 AM