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
, after I got the error
, I can still select from the table. However, by using this new account, after I got the error
, I can no longer SELECT from a table.
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
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
) that will show the entire SQL session including recursive statements. This should show you the recursive SQL statement that is failing.
source to share