title: Oracle - Zombie Table
body: <p>I'm having this odd problem since yesterday. I've tried several options and I actually reinstalled ORACLE and the DB itself.</p>
  
  <p>Here's the problem: I have this table that is somekind of zombie. Here are the symptoms:</p>
  
  <pre><code>SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME='MYTABLE'
  </code></pre>
  
  <p>Returns a record, meaning that the table exists.</p>
  
  <pre><code>SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'MYTABLE'
  </code></pre>
  
  <p>Returns all the columns of MYTABLE. So far so good, the table exists.</p>
  
  <pre><code>SELECT * FROM MYTABLE
  </code></pre>
  
  <p>Returns <strong>ORA-00942: table or view does not exist</strong>.
  At this point I'm quite confused: the table seems to exist on the USERTABLES but I cannot SELECT over it?</p>
  
  <pre><code>CREATE TABLE MYTABLE (Foo NUMBER) TABLESPACE MYTABLESPACE
  </code></pre>
  
  <p>Returns:
  <strong>ORA-00604: error occurred at recursive SQL level 1
  ORA-00001: unique constraint (SYS.I_OBJ2) violated</strong></p>
  
  <p>I do not understand this error. But the best is yet to come.</p>
  
  <pre><code>SELECT * FROM MYTABLE
  </code></pre>
  
  <p>Surprisingly, the above query (an exact copy of the 3rd query) returns several records now!
  Moreover, I noticed that the column Foo is not present: the table I now see is my initial table that had other columns.</p>
  
  <pre><code>DROP TABLE MYTABLE
  </code></pre>
  
  <p>I now try to drop the table and I get the following errors:</p>
  
  <p><strong>ORA-00604: error occurred at recursive SQL level 1
  ORA-00942: table or view does not exist
  ORA-06512: at line 19</strong></p>
  
  <pre><code>SELECT * FROM MYTABLE
  </code></pre>
  
  <p>More confused than ever, I try the above query and, surprise surprise, the table no longer exists.</p>
  
  <p>I don't undestand this: the table is on USERTABLES but I cannot SELECT over it, however, if I create a new table with the same name, I get an error but now I can SELECT over the previous version of that table with several records.</p>
  
  <p>Any thoughts ? I really need your help :(</p>
  
  <p><strong>EDIT</strong> - I checked now: I'm unable to drop ANY table. This might just be a new symptom.</p>
  
  <p><strong>Solution</strong></p>
  
  <p>The problem was that MDSYS.SDO_GEOR_SYSDATA_TABLE table was missing and a drop event trigger was trying to access it, generating the error. The solution was restoring that table. </p>