Oracle - Unable to drop tables

Question

This question is related to the one I posted yesterday but with further implications.

The situation is: I'm unable to drop ANY table. Here's an example:

SQL> CREATE TABLE FOO (BAR NUMBER) TABLESPACE SYSTEM
 /
Table created.

SQL> SELECT COUNT(1) FROM FOO;
  COUNT(1)
----------
         0

SQL> DROP TABLE FOO;
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 19

So, the table seems to exist but I'm not capable of dropping it.

Notice the error ORA-00604: error occurred at recursive SQL level 1. If I try to drop a non existing table, this error does not appear:

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

Somehow, the system is unable to find the table at dropping time.

The oracle installation and the DB itself is new (one day old).

EDIT - I retried this test using another tablespace and user (I just created ones) and I got a slightly different behaviour: using SYS, after I got the DROP error I can still SELECT from the table. However, using this new user, after I got the DROP error, I no longer can SELECT from the table.

Solution

We found the problem: the MDSYS.SDO_GEOR_SYSDATA_TABLE was missing, preventing the drop operation.The solution is to restore that table. Here is the complete solution, by Gaurav Soni (by the way, many thanks).

Run the script catmd.sql (located in $ORACLE_HOME/md/admin dir).

The catmd.sql script is the script that loads all objects needed by Oracle spatial in the database. Then drop the user.

you can also refer to oracle metalinks

Steps for Manual Installation of Oracle Spatial Data Option

Dropping user results in ORA-942 against SDO_GEOM_METADATA_TABLE

Answer 1

I'd suggest that you activate SQL tracing (ALTER SESSION SET SQL_TRACE=TRUE;) and try the drop again. This will generate a trace file on the server (in the udump directory) that will show all the SQL the session executed, including recursive statements. This should show you the recursive SQL statement that is failing.