Oracle - Unable to drop tables
This question is related to the one I posted yesterday , but with further implications.
The situation is like this: I cannot 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 can't seem to dump it.
Note error ORA-00604: Error at recursive SQL level 1 . If I try to drop an 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 cannot find the table during the drop.
The oracle installation and the DB itself are new (one day).
EDIT - I repeated this test using a different tablespace and user (I just created them) and I got a slightly different behavior: using SYS
, after I got the error DROP
, I can still select from the table. However, by using this new account, after I got the error DROP
, I can no longer SELECT from a table.
Decision
We found the problem: MDSYS.SDO_GEOR_SYSDATA_TABLE was missing, which prevents the drop operation. The solution is to restore this table. Here is the complete solution, Gaurav Soni (thanks a lot by the way).
Run script catmd.sql (located in the $ ORACLE_HOME / md / admin directory).
catmd.sql script is a script that loads all the objects needed by the Oracle space in the database. Then release the user.
you can also go to metal oracles
Steps to manually set the Oracle spatial data option
Dropping user results in ORA-942 versus SDO_GEOM_METADATA_TABLE
source to share
I suggest activating SQL tracing ( ALTER SESSION SET SQL_TRACE=TRUE;
) and trying again. This will create a trace file on the server (in a directory udump
) that will show the entire SQL session including recursive statements. This should show you the recursive SQL statement that is failing.
source to share