ORA-00942: table or view does not exist

[prashant]

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

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

select 'drop table '||table_name||' purge;' from user_tables;

but again i got the same message for all the tables.

Please suggest.

Regards,
Prashant

[tyro]

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.

[rajavu]

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.

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>

[prashant]

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