发这个帖子主要是有两个疑问:
第一:表已经干掉了为什么还能select count(1),但又做不了其他操作,导致该表名无法再次使用;
第二:网上的帖子都是count(1)和count(*)一样,但是在此似乎不一样,何解?
我被这问题整的很是蛋疼,又是生产环境,又不能随便动,很无助啊!!!!如下是整个故事发生的由来及异常:
--导致下述异常的可能性操作:
第一步:(删除临时表报如下错)
DROP TABLE B_CIF_ENT_BASIC_EX;
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
第二步:(清空回收站)
purge recyclebin;
再往后就报了下列异常。
--在当前用户下已经查不到该表,当前用户即建表用户
SQL> SELECT * FROM USER_TABLES WHERE TABLE_NAME='B_CIF_ENT_BASIC_EX';no rows selected--但是select count(1)还能查出该表的记录数
SQL> SELECT COUNT(1) FROM B_CIF_ENT_BASIC_EX;
1024--通过select count(*)、count(rowid)方式无法查询该表
SQL> SELECT COUNT(*) FROM B_CIF_ENT_BASIC_EX;
SELECT COUNT(*) FROM B_CIF_ENT_BASIC_EX
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsircinfo_num1], [11], [25], [3327443], [], [], [], []
SQL> SELECT COUNT(rowid) FROM B_CIF_ENT_BASIC_EX;
SELECT COUNT(rowid) FROM B_CIF_ENT_BASIC_EX
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsircinfo_num1], [11], [25], [3327443], [], [], [], []--与该表相关的DDL/DCL语句均执行失败
SQL> TRUNCATE TABLE B_CIF_ENT_BASIC_EX;
TRUNCATE TABLE B_CIF_ENT_BASIC_EX
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsircinfo_num1], [11], [25], [3327443], [], [], [], []
SQL> DELETE FROM B_CIF_ENT_BASIC_EX;
DELETE FROM B_CIF_ENT_BASIC_EX
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsircinfo_num1], [11], [25], [3327443], [], [], [], []
SQL> SELECT * FROM B_CIF_ENT_BASIC_EX;
SELECT * FROM B_CIF_ENT_BASIC_EX
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsircinfo_num1], [11], [25], [3327443], [], [], [], []--回收站已清空
SQL> select * from user_recyclebin;no rows selected--无法以该表做参考创建其他表
SQL> CREATE TABLE B_CIF_ENT_BASIC_EX_CJF
2 AS
3 SELECT * FROM B_CIF_ENT_BASIC_EX;
SELECT * FROM B_CIF_ENT_BASIC_EX
*
ERROR at line 3:
ORA-00600: internal error code, arguments: [ktsircinfo_num1], [11], [25], [3327443], [], [], [], []--其他表可以正常使用
SQL> CREATE TABLE INCT_30_CJF
2 AS
3 SELECT * FROM INCT_30;Table created.SQL> DROP TABLE INCT_30_CJF;Table dropped.SQL> SELECT COUNT(*) FROM INCT_30;
1SQL> SELECT * FROM INCT_30;
8002000000558732 999999996 30 02 20130731 31-JUL-13 20130731 104412 04096 0045335 2130396 09091 003 10
20130731 87109053 国土城建和水务局 000 0000000 003 009
0303 COR 20130731 20130731
purgecount(1)count(*)ORA-00600ORA-38301recyclebin
第一:表已经干掉了为什么还能select count(1),但又做不了其他操作,导致该表名无法再次使用;
第二:网上的帖子都是count(1)和count(*)一样,但是在此似乎不一样,何解?
我被这问题整的很是蛋疼,又是生产环境,又不能随便动,很无助啊!!!!如下是整个故事发生的由来及异常:
--导致下述异常的可能性操作:
第一步:(删除临时表报如下错)
DROP TABLE B_CIF_ENT_BASIC_EX;
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
第二步:(清空回收站)
purge recyclebin;
再往后就报了下列异常。
--在当前用户下已经查不到该表,当前用户即建表用户
SQL> SELECT * FROM USER_TABLES WHERE TABLE_NAME='B_CIF_ENT_BASIC_EX';no rows selected--但是select count(1)还能查出该表的记录数
SQL> SELECT COUNT(1) FROM B_CIF_ENT_BASIC_EX;
1024--通过select count(*)、count(rowid)方式无法查询该表
SQL> SELECT COUNT(*) FROM B_CIF_ENT_BASIC_EX;
SELECT COUNT(*) FROM B_CIF_ENT_BASIC_EX
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsircinfo_num1], [11], [25], [3327443], [], [], [], []
SQL> SELECT COUNT(rowid) FROM B_CIF_ENT_BASIC_EX;
SELECT COUNT(rowid) FROM B_CIF_ENT_BASIC_EX
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsircinfo_num1], [11], [25], [3327443], [], [], [], []--与该表相关的DDL/DCL语句均执行失败
SQL> TRUNCATE TABLE B_CIF_ENT_BASIC_EX;
TRUNCATE TABLE B_CIF_ENT_BASIC_EX
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsircinfo_num1], [11], [25], [3327443], [], [], [], []
SQL> DELETE FROM B_CIF_ENT_BASIC_EX;
DELETE FROM B_CIF_ENT_BASIC_EX
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsircinfo_num1], [11], [25], [3327443], [], [], [], []
SQL> SELECT * FROM B_CIF_ENT_BASIC_EX;
SELECT * FROM B_CIF_ENT_BASIC_EX
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsircinfo_num1], [11], [25], [3327443], [], [], [], []--回收站已清空
SQL> select * from user_recyclebin;no rows selected--无法以该表做参考创建其他表
SQL> CREATE TABLE B_CIF_ENT_BASIC_EX_CJF
2 AS
3 SELECT * FROM B_CIF_ENT_BASIC_EX;
SELECT * FROM B_CIF_ENT_BASIC_EX
*
ERROR at line 3:
ORA-00600: internal error code, arguments: [ktsircinfo_num1], [11], [25], [3327443], [], [], [], []--其他表可以正常使用
SQL> CREATE TABLE INCT_30_CJF
2 AS
3 SELECT * FROM INCT_30;Table created.SQL> DROP TABLE INCT_30_CJF;Table dropped.SQL> SELECT COUNT(*) FROM INCT_30;
1SQL> SELECT * FROM INCT_30;
8002000000558732 999999996 30 02 20130731 31-JUL-13 20130731 104412 04096 0045335 2130396 09091 003 10
20130731 87109053 国土城建和水务局 000 0000000 003 009
0303 COR 20130731 20130731
purgecount(1)count(*)ORA-00600ORA-38301recyclebin
从mos上根据官方的解释是bugbug号分别为 14280879 ORA-600 [ktsircinfo_num1] from segment advisor on cube-organized table
13544396 Corruption / ORA-600 after ALTER TABLE of a LONG to CLOB
12971775 OERI:[ktsircinfo_num1] with concurrent parallel DML and online index creation
参考文档 139182.1
本人从你的贴出的仅有的现象初步猜测是bug导致 希望对你有点帮助
oracle保存了一个缓存结果你试试把缓存清空之后试试
还有没有检索结果
连续TRUNCATE 两次就正常了:
第一次TRUNCATE也是提示ORA-00600错误,
第二次TRUNCATE就提示表不存在了。
之后那个表名又可以用了,我表示一点脾气都没有了