2

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

Community
  • 1
  • 1
jpaires
  • 355
  • 6
  • 13
  • which oracle version have you installed? [link](http://www.dba-oracle.com/sf_ora_00604_error_occurred_and_recursive_sql_level_string.htm) – Gaurav Soni Mar 23 '12 at 11:34
  • Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production – jpaires Mar 23 '12 at 11:40
  • A bit of a long-shot, but it acts like there's something else occurring here. Could there be a DROP trigger on DATABASE or your current schema? – Bob Jarvis - Слава Україні Mar 23 '12 at 12:02
  • I've checked that but I don't really know how to interpret that. I have the following drop triggers. AW_DROP_TRG on SYS. SDO_DROP_USER, SDO_GEOR_DROP_USER, SDO_NETWORK_CONS_DROP_TRIG, SDO_NETWORK_DROP_USER, SDO_NETWORK_UD_DROP_TRIG on MDSYS. Any suspect? – jpaires Mar 23 '12 at 12:28
  • `select owner,trigger_name,trigger_type,triggering_event from dba_triggers where table_name ='FOO' AND trigger_type like '%EVENT' and triggering_event like '%DROP%' order by 1,2 /` Can you provide the output of this query – Gaurav Soni Mar 23 '12 at 12:44
  • Sure, but no rows are returned from that query. – jpaires Mar 23 '12 at 14:08

2 Answers2

3

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.

Dave Costa
  • 47,262
  • 8
  • 56
  • 72
  • Using your suggestion and Gaurav's I was able to narrow it down: the problem is with the MDSYS.SDO_GEOR_DROP_USER trigger, most precisily with the SDO_GEOR_SYSDATA_TABLE that doesn't seem to exist! I tried to select over that table and I get a "ORA-00942: table or view does not exist". Then I edited the trigger to return before using that table, now I can drop tables. Next, and finally, I reedited the trigger to return immediatly after using that table and I no longer can drop tables. The table does not exist or cannot be found. Any ideas on why? – jpaires Mar 23 '12 at 14:56
  • No idea. That's related to Oracle's Spatial Option, I believe, which I don't have any experience with. It might be that there was an error during the installation process. If you're not using this option, best thing is to remove it. – Dave Costa Mar 23 '12 at 15:06
  • Many, many thanks. I've asked my coworkers the creation script for this table. Now I have the table and I'm finally able to drop tables. However, I'm still curious about how this happened. – jpaires Mar 23 '12 at 15:28
0

I think the problem is that you created the table on system tablespace. You should create it on the user tablespace or create one to store your data.

HamoriZ
  • 2,370
  • 18
  • 38
  • As I said on the EDIT part of my question, I tried with a different user and tablespace. – jpaires Mar 23 '12 at 12:19
  • I don't know of any general reason why this would prevent dropping of the table. I can create and drop tables in my `SYSTEM` tablespace. – Dave Costa Mar 23 '12 at 12:21