系统down了后,启动后一直向一个临时对象中insert 数据

显示全部楼层 · 2005-7-16 18:49:11

INSERT /*+ APPEND BYPASS_RECURSIVE_CHECK */ INTO "SYS"."SYS_TEMP_0FD9D7251_5DBAD5
2" SELECT /*+ SEMIJOIN_DRIVER INDEX("V_SALE_TMP_LOG","IDX_STL_RESELLER_ID&quot

*/ "V
_SALE_TMP_LOG"."RESELLER_ID" "C0","V_SALE_TMP_LOG"."GAME_CARD_TYPE_ID" "C1","V_SA
LE_TMP_LOG"."GAME_CARD_NUM" "C2","V_SALE_TMP_LOG"."CITY_AGENT_COST" "C3","V_SALE_
TMP_LOG"."PROVINCE_AGENT_COST" "C4","V_SALE_TMP_LOG"."SALE_MODE" "C5" FROM "CYBER
CAFE"."SALE_TMP_LOG" "V_SALE_TMP_LOG" WHERE ("V_SALE_TMP_LOG"."SALE_MODE"=2 OR "V
_SALE_TMP_LOG"."SALE_MODE"=3 OR "V_SALE_TMP_LOG"."SALE_MODE"=4) AND "V_SALE_TMP_L
OG"."IS_SUCCESS"='Y' AND "V_SALE_TMP_LOG"."LOG_TIME">=TO_DATE('2004-08-31 00:00:0
0', 'yyyy-mm-dd hh24:mi:ss') AND "V_SALE_TMP_LOG"."LOG_TIME" desc SYS_TEMP_0FD9D7251_5DBAD52
Name
Null?Type
----------------------------------------------------------------- -------- ---------------------------------------------
C0
NUMBER(11)
C1
NUMBER(11)
C2
NUMBER(11)
C3
NUMBER(11,2)
C4
NUMBER(11,2)
C5
NUMBER(1)
SQL> select count(*) from SYS_TEMP_0FD9D7251_5DBAD52
2;
COUNT(*)
----------
0
Elapsed: 00:00:00.00
已经好几个钟头啦

千问 · 2005-7-16 18:49:11

是不是数据文件坏了？还是单这个表/索引坏啦？

千问 · 2005-7-16 18:49:11

遭遇bug了，并且是目前原因未明的bug
如果有使用 dbms_stats自动搜索统计信息，先停掉，或者停掉 job进程先看看

千问 · 2005-7-16 18:49:11

由于不小心用kill将dbwr杀了,重启系统后就是这样的
没能使用dbms_stats,但这个package已经invalid,后来
用utlrp.sql重新编绎后,系统中没有invalid对象,job有一
个是每5分钟执行一次,已经停了.

千问 · 2005-7-16 18:49:11

[php]
0358261.00 2004-08-31/20:45:50
INSERT /*+ APPEND BYPASS_RECURSIVE_CHECK */ INTO "SYS"."SYS_TEMP_0FD9D765A_5DBAD5
2" SELECT /*+ SEMIJOIN_DRIVER INDEX("V_SALE_TMP_LOG","IDX_STL_RESELLER_ID&quot

*/ "V
_SALE_TMP_LOG"."RESELLER_ID" "C0","V_SALE_TMP_LOG"."GAME_CARD_TYPE_ID" "C1","V_SA
LE_TMP_LOG"."GAME_CARD_NUM" "C2","V_SALE_TMP_LOG"."CITY_AGENT_COST" "C3","V_SALE_
TMP_LOG"."PROVINCE_AGENT_COST" "C4","V_SALE_TMP_LOG"."SALE_MODE" "C5" FROM "CYBER
CAFE"."SALE_TMP_LOG" "V_SALE_TMP_LOG" WHERE ("V_SALE_TMP_LOG"."SALE_MODE"=2 OR "V
_SALE_TMP_LOG"."SALE_MODE"=3 OR "V_SALE_TMP_LOG"."SALE_MODE"=4) AND "V_SALE_TMP_L
OG"."IS_SUCCESS"='Y' AND "V_SALE_TMP_LOG"."LOG_TIME">=TO_DATE('2004-08-31 00:00:0
0', 'yyyy-mm-dd hh24:mi:ss') AND "V_SALE_TMP_LOG"."LOG_TIME"=TO_DATE('2004-08-31 00:00:0
0', 'yyyy-mm-dd hh24:mi:ss') AND "V_SALE_TMP_LOG"."LOG_TIME"TO_DATE('2004-08-16 09:59:59', 'yyyy-mm-dd hh24:mi:ss') AND "A"."SALE_MOD
E"=4 AND "A"."IS_SUCCESS"='Y'
[/php]

千问 · 2005-7-16 18:49:11

如果把这个表DROP掉,不知会有什么后果？

千问 · 2005-7-16 18:49:11

终于好啦
SQL> select count(*) from SYS_TEMP_0FD9D71CE_5DBAD52;
select count(*) from SYS_TEMP_0FD9D71CE_5DBAD52

*
ERROR at line 1:
ORA-00942: table or view does not exist

千问 · 2005-7-16 18:49:11

最初由 xzh2000 发布
[B]终于好啦
SQL> select count(*) from SYS_TEMP_0FD9D71CE_5DBAD52;
select count(*) from SYS_TEMP_0FD9D71CE_5DBAD52

*
ERROR at line 1:
ORA-00942: table or view does not exist [/B]
Please be more specific on how you solved the problem so we all benefit from it. Did you simply drop the table while the INSERT was running?
Biti must be citing the Metalink thread 519531.996, which in turn cites Bug 3630873. The bug report says you have row_locking=intent set and you get ORA-600 [qks3tassert:1] error. Is that the case with you? If not, then it's a new mystery.
Yong Huang

千问 · 2005-7-16 18:49:11

最初由 Yong Huang 发布
[B]
Please be more specific on how you solved the problem so we all benefit from it. Did you simply drop the table while the INSERT was running?
Biti must be citing the Metalink thread 519531.996, which in turn cites Bug 3630873. The bug report says you have row_locking=intent set and you get ORA-600 [qks3tassert:1] error. Is that the case with you? If not, then it's a new mystery.
Yong Huang [/B]
没有能删除这个表,删除时报ora-30732错误
SQL> host oerr ora 30732
30732, 00000, "table contains no user-visible columns"
// *Cause:An attempt was made to query on a system table which has no
//
user-visible columns.
// *Action: Do not query on a system table that has no user-visible
//
columns.
我每几分钟从v$sql中抓一次buffer_gets/executions较大的SQL,
到10:00钟时没有这类SQL出现,
到sys中看了看,SYS_TEMP_....表已经不在存在
SQL> select count(*) from SYS_TEMP_0FD9D71CE_5DBAD52;
select count(*) from SYS_TEMP_0FD9D71CE_5DBAD52
*
ERROR at line 1:
ORA-00942: table or view does not exist
猜想是因为oracle由于dbwr被kill后,sale_tmp_log表有大量的
数据被损坏,每当有访问这个时表,
oracle会去读取正确的信息（从raid或者日志）