甲骨文-僵尸表 [英] Oracle - Zombie Table

问题描述

自昨天以来，我遇到了这个奇怪的问题.我尝试了几种方法，实际上我重新安装了ORACLE和数据库本身.

I'm having this odd problem since yesterday. I've tried several options and I actually reinstalled ORACLE and the DB itself.

这是问题所在:我有这张有点像僵尸的桌子.这些是症状:

Here's the problem: I have this table that is somekind of zombie. Here are the symptoms:

SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME='MYTABLE'

返回一条记录，表示该表存在.

Returns a record, meaning that the table exists.

SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'MYTABLE'

返回MYTABLE的所有列.到目前为止，表已经存在.

Returns all the columns of MYTABLE. So far so good, the table exists.

SELECT * FROM MYTABLE

返回 ORA-00942:表或视图不存在. 在这一点上，我很困惑:该表似乎存在于USERTABLES上，但是我无法对其进行SELECT?

Returns ORA-00942: table or view does not exist. At this point I'm quite confused: the table seems to exist on the USERTABLES but I cannot SELECT over it?

CREATE TABLE MYTABLE (Foo NUMBER) TABLESPACE MYTABLESPACE

返回: ORA-00604:在递归SQL级别1发生错误 ORA-00001:违反了唯一约束(SYS.I_OBJ2)

Returns: ORA-00604: error occurred at recursive SQL level 1 ORA-00001: unique constraint (SYS.I_OBJ2) violated

我不明白此错误.但是最好的还没到.

I do not understand this error. But the best is yet to come.

SELECT * FROM MYTABLE

令人惊讶的是，以上查询(第3个查询的精确副本)现在返回多条记录！ 此外，我注意到Foo列不存在:我现在看到的表是我的初始表具有其他列.

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.

DROP TABLE MYTABLE

我现在尝试删除表，但出现以下错误:

I now try to drop the table and I get the following errors:

ORA-00604:在递归SQL级别1发生错误 ORA-00942:表或视图不存在 ORA-06512:在第19行

ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist ORA-06512: at line 19

SELECT * FROM MYTABLE

比以往任何时候都更困惑，我尝试了上面的查询，令人惊讶的是，该表不再存在.

More confused than ever, I try the above query and, surprise surprise, the table no longer exists.

我不理解这一点:该表位于USERTABLES上，但无法对其进行选择，但是，如果我创建一个具有相同名称的新表，则会收到错误消息，但现在我可以对它的先前版本进行SELECT了.带有多个记录的表.

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.

有什么想法吗?我真的需要您的帮助:(

Any thoughts ? I really need your help :(

编辑-我已检查:我无法删除任何表格.这可能只是一种新症状.

EDIT - I checked now: I'm unable to drop ANY table. This might just be a new symptom.

解决方案

问题是缺少MDSYS.SDO_GEOR_SYSDATA_TABLE表，并且放置事件触发器正在尝试访问它，从而产生错误.解决方案是恢复该表.

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.

推荐答案

在尝试选择和删除时，您没有限定模式名称.您会话的CURRENT_SCHEMA可能与登录用户不同.尝试

You didn't qualify the schema names when trying to select and drop. The CURRENT_SCHEMA of your session may be different form the log-on user. Check by trying

select SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') from dual;

除了描述输出是什么，您能不能为我们复制/粘贴完整的输出?

Instead of describing what the output was, could you please copy/paste the complete output for us?

最后，您可以排除有人弄乱了词典吗?您知道，SYSDBA可以做任何事情....

Lastly, can you exclude that someone messed up the dictionary? You know, SYSDBA can do anything....

这篇关于甲骨文-僵尸表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！