监控信息显示报如下错误：
ORA-00600: 内部错误代码, 参数: [19004], [], [], [], [], [], [], [], [], [], [], []
1.查看报错信息
查看alert.log文件中提示的trc文件显示运行下面一条sql语句报错的
INSERT INTO TREE_HIS.CK_BCLSB_MX
SELECT * FROM CK_BCLSB_MX TA
WHERE NOT EXISTS
(SELECT * FROM TREE_HIS.CK_BCLSB_MX
WHERE BOCI_NO = TA.BOCI_NO AND HANGHAO = TA.HANGHAO AND DANJ_NO = TA.DANJ_NO)
AND NOT EXISTS (SELECT 1
FROM CK_KPD_HZ WHERE DANJ_NO= TA.DANJ_NO AND ZUOY_STATE<'N2' )
报 ORA-00600: 内部错误代码, 参数: [19004]
实际情况是运行一个procedure TREE.PRC_UTL_DAYTERM ，运行到上面一条语句就报了这个错误
信息如下：
ORA-12012: 自动执行作业 21477 出错
ORA-14400: 插入的分区关键字未映射到任何分区
ORA-06512: 在 "TREE.PRC_UTL_DAYTERM", line 432
2.分析报错信息
单独运行如下语句
SELECT count(1)
FROM CK_BCLSB_MX TA
WHERE NOT EXISTS (SELECT *
FROM TREE_HIS.CK_BCLSB_MX
WHERE BOCI_NO = TA.BOCI_NO
AND HANGHAO = TA.HANGHAO
AND DANJ_NO = TA.DANJ_NO)
AND NOT EXISTS (SELECT 1
FROM CK_KPD_HZ
WHERE DANJ_NO = TA.DANJ_NO
AND ZUOY_STATE < 'N2')
不通过，报错误
ORA-00600: 内部错误代码, 参数: [19004], [], [], [], [], [], [], [], [], [], [], []
3.排查报错信息
网上查找一般是统计信息出错所致：
那么就通过DBA_TAB_STATISTICS视图，一张一张表分析这张表的统计信息，如下：
select * from DBA_TAB_STATISTICS where owner= upper('&owner') and TABLE_NAME = upper('&table_name') ;
hbjzt@TREE6> select * from DBA_TAB_STATISTICS where owner='TREE_HIS' and TABLE_NAME = 'CK_BCLSB_MX' ;
OWNER TABLE_NAME PARTITION_NAME
--------------- ------------------------------ ------------------------------------------------------------------------------------------
PARTITION_POSITION SUBPARTITION_NAME SUBPARTITION_POSITION
------------------ ------------------------------------------------------------------------------------------ ---------------------
OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS
------------------------------------ ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- -------------------
AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE LAST_ANALYZED GLOBAL_ST USER_STAT STATTYPE_LOCKED STALE_STA
----------------- ------------------- ----------- ------------------ --------- --------- --------------- ---------
TREE_HIS CK_BCLSB_MX
TABLE 1704995 6070 0 0 0 45 0 0
1704995 26-MAR-14 YES NO ALL NO
hbjzt@TREE6> select * from DBA_TAB_STATISTICS where owner= upper('&owner') and TABLE_NAME = upper('&table_name') ;
Enter value for owner: TREE
Enter value for table_name: CK_BCLSB_MX
old 1: select * from DBA_TAB_STATISTICS where owner= upper('&owner') and TABLE_NAME = upper('&table_name')
new 1: select * from DBA_TAB_STATISTICS where owner= upper('TREE') and TABLE_NAME = upper('CK_BCLSB_MX')
OWNER TABLE_NAME PARTITION_NAME
--------------- ------------------------------ ------------------------------------------------------------------------------------------
PARTITION_POSITION SUBPARTITION_NAME SUBPARTITION_POSITION
------------------ ------------------------------------------------------------------------------------------ ---------------------
OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS
------------------------------------ ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- -------------------
AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE LAST_ANALYZED GLOBAL_ST USER_STAT STATTYPE_LOCKED STALE_STA
----------------- ------------------- ----------- ------------------ --------- --------- --------------- ---------
TREE CK_BCLSB_MX
TABLE 4724 31 0 0 0 44 0 0
4724 14-JUL-16 YES NO NO
hbjzt@TREE6> select * from DBA_TAB_STATISTICS where owner= upper('&owner') and TABLE_NAME = upper('&table_name') ;
Enter value for owner: TREE
Enter value for table_name: CK_KPD_HZ
old 1: select * from DBA_TAB_STATISTICS where owner= upper('&owner') and TABLE_NAME = upper('&table_name')
new 1: select * from DBA_TAB_STATISTICS where owner= upper('TREE') and TABLE_NAME = upper('CK_KPD_HZ')
OWNER TABLE_NAME PARTITION_NAME
--------------- ------------------------------ ------------------------------------------------------------------------------------------
PARTITION_POSITION SUBPARTITION_NAME SUBPARTITION_POSITION
------------------ ------------------------------------------------------------------------------------------ ---------------------
OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS
------------------------------------ ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- -------------------
AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE LAST_ANALYZED GLOBAL_ST USER_STAT STATTYPE_LOCKED STALE_STA
----------------- ------------------- ----------- ------------------ --------- --------- --------------- ---------
TREE CK_KPD_HZ
TABLE 4667 247 0 0 0 268 0 0
4667 14-JUL-16 YES NO NO
hbjzt@TREE6> select count(1) from TREE_his.CK_BCLSB_MX ;
COUNT(1)
----------
38306 ---统计信息里显示是6070,相差很大,明显是统计信息出问题了。
但是为什么呢？其他的表有统计信息收集，而这张表没有呢
4. 查看自动任务是否开启：
col client_name format a32
col consumer_group format a30
col window_group format a32
col servICE_name format a15
col attributes format a55
SELECT CLIENT_NAME,STATUS,CONSUMER_GROUP,WINDOW_GROUP,SERVICE_NAME,ATTRIBUTES FROM DBA_AUTOTASK_CLIENT;
CLIENT_NAME STATUS CONSUMER_GROUP WINDOW_GROUP SERVICE_NAME
-------------------------------- ------------------------ ------------------------------ -------------------------------- ---------------
ATTRIBUTES
-------------------------------------------------------
auto optimizer stats collection ENABLED ORA$AUTOTASK_STATS_GROUP ORA$AT_WGRP_OS
ON BY DEFAULT, VOLATILE, SAFE TO KILL
auto space advisor ENABLED ORA$AUTOTASK_SPACE_GROUP ORA$AT_WGRP_SA
ON BY DEFAULT, VOLATILE, SAFE TO KILL
sql tuning advisor DISABLED ORA$AUTOTASK_SQL_GROUP ORA$AT_WGRP_SQ
ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL
col window_next_time format a26
col WINDOW_ACTIVE format a13
SELECT * FROM DBA_AUTOTASK_WINDOW_CLIENTS;
WINDOW_NAME WINDOW_NEXT_TIME WINDOW_ACTIVE AUTOTASK_STATUS OPTIMIZER_STATS SEGMENT_ADVISOR
-------------------- -------------------------- ------------- ------------------------ ------------------------ ------------------------
SQL_TUNE_ADVISOR HEALTH_MONITOR
------------------------ ------------------------
MONDAY_WINDOW 18-JUL-16 01.00.00.000000 FALSE ENABLED ENABLED ENABLED
AM +08:00
DISABLED DISABLED
TUESDAY_WINDOW 19-JUL-16 01.00.00.000000 FALSE ENABLED ENABLED ENABLED
AM +08:00
DISABLED DISABLED
WEDNESDAY_WINDOW 20-JUL-16 01.00.00.000000 FALSE ENABLED ENABLED ENABLED
AM +08:00
DISABLED DISABLED
THURSDAY_WINDOW 21-JUL-16 01.00.00.000000 FALSE ENABLED ENABLED ENABLED
AM +08:00
DISABLED DISABLED
FRIDAY_WINDOW 15-JUL-16 01.00.00.000000 FALSE ENABLED ENABLED ENABLED
AM +08:00
DISABLED DISABLED
SATURDAY_WINDOW 16-JUL-16 01.00.00.000000 FALSE ENABLED ENABLED ENABLED
AM +08:00
DISABLED DISABLED
SUNDAY_WINDOW 17-JUL-16 01.00.00.000000 FALSE ENABLED ENABLED ENABLED
AM +08:00
DISABLED DISABLED
7 rows selected.
hbjzt@TREE6>
hbjzt@TREE6>
hbjzt@TREE6> set linesize 300
col job_info format a50
col job_name format a30
col window_name format a17
col job_status format a11
col job_duration format a13
col window_start_time format a26
col job_start_time format a26
SELECT CLIENT_NAME,WINDOW_NAME,WINDOW_START_TIME,JOB_NAME,JOB_STATUS,JOB_START_TIME,JOB_DURATION,JOB_ERROR,JOB_INFO
FROM (
SELECT CLIENT_NAME,
WINDOW_NAME,
WINDOW_START_TIME,
JOB_NAME,
JOB_STATUS,
JOB_START_TIME,
JOB_DURATION,
JOB_ERROR,
JOB_INFO,
ROW_NUMBER() OVER(PARTITION BY CLIENT_NAME ORDER BY JOB_START_TIME DESC) AS RN
FROM DBA_AUTOTASK_JOB_HISTORY
) T
15 WHERE RN <= 10;
CLIENT_NAME WINDOW_NAME WINDOW_START_TIME JOB_NAME JOB_STATUS JOB_START_TIME JOB_DURATION JOB_ERROR JOB_INFO
-------------------------------- ----------------- -------------------------- ------------------------------ ----------- -------------------------- ------------- ---------- --------------------------------------------------
auto optimizer stats collection THURSDAY_WINDOW 14-JUL-16 03.00.00.273000 ORA$AT_OS_OPT_SY_761 SUCCEEDED 14-JUL-16 03.00.02.457000 +000 00:06:05 0
AM +08:00 AM +08:00
auto optimizer stats collection WEDNESDAY_WINDOW 13-JUL-16 03.00.00.147000 ORA$AT_OS_OPT_SY_741 SUCCEEDED 13-JUL-16 03.00.02.097000 +000 00:10:34 0
AM +08:00 AM +08:00
auto optimizer stats collection TUESDAY_WINDOW 12-JUL-16 03.00.00.171000 ORA$AT_OS_OPT_SY_721 SUCCEEDED 12-JUL-16 03.00.02.371000 +000 00:06:34 0
AM +08:00 AM +08:00
auto optimizer stats collection MONDAY_WINDOW 11-JUL-16 03.00.00.024000 ORA$AT_OS_OPT_SY_704 SUCCEEDED 11-JUL-16 03.00.02.052000 +000 00:02:33 0
AM +08:00 AM +08:00
auto optimizer stats collection SUNDAY_WINDOW 10-JUL-16 03.00.00.141000 ORA$AT_OS_OPT_SY_701 SUCCEEDED 10-JUL-16 03.00.02.232000 +000 00:04:13 0
AM +08:00 AM +08:00
auto optimizer stats collection SATURDAY_WINDOW 09-JUL-16 03.00.00.176000 ORA$AT_OS_OPT_SY_681 SUCCEEDED 09-JUL-16 03.00.02.314000 +000 00:20:48 0
AM +08:00 AM +08:00
auto optimizer stats collection FRIDAY_WINDOW 08-JUL-16 03.00.00.182000 ORA$AT_OS_OPT_SY_661 SUCCEEDED 08-JUL-16 03.00.02.366000 +000 00:07:10 0
AM +08:00 AM +08:00
auto optimizer stats collection THURSDAY_WINDOW 07-JUL-16 03.00.00.159000 ORA$AT_OS_OPT_SY_641 SUCCEEDED 07-JUL-16 03.00.01.422000 +000 00:09:11 0
AM +08:00 AM +08:00
auto space advisor THURSDAY_WINDOW 14-JUL-16 03.00.00.273000 ORA$AT_SA_SPC_SY_762 SUCCEEDED 14-JUL-16 03.00.02.457000 +000 00:03:49 0
AM +08:00 AM +08:00
auto space advisor WEDNESDAY_WINDOW 13-JUL-16 03.00.00.147000 ORA$AT_SA_SPC_SY_742 SUCCEEDED 13-JUL-16 03.00.02.097000 +000 00:04:49 0
AM +08:00 AM +08:00
auto space advisor TUESDAY_WINDOW 12-JUL-16 03.00.00.171000 ORA$AT_SA_SPC_SY_722 SUCCEEDED 12-JUL-16 03.00.02.371000 +000 00:04:35 0
AM +08:00 AM +08:00
auto space advisor MONDAY_WINDOW 11-JUL-16 03.00.00.024000 ORA$AT_SA_SPC_SY_705 SUCCEEDED 11-JUL-16 03.00.02.052000 +000 00:00:54 0
AM +08:00 AM +08:00
auto space advisor SUNDAY_WINDOW 10-JUL-16 03.00.00.141000 ORA$AT_SA_SPC_SY_702 SUCCEEDED 10-JUL-16 03.00.02.232000 +000 00:02:11 0
AM +08:00 AM +08:00
auto space advisor SATURDAY_WINDOW 09-JUL-16 03.00.00.176000 ORA$AT_SA_SPC_SY_682 SUCCEEDED 09-JUL-16 03.00.02.314000 +000 00:59:59 0
AM +08:00 AM +08:00
auto space advisor FRIDAY_WINDOW 08-JUL-16 03.00.00.182000 ORA$AT_SA_SPC_SY_662 SUCCEEDED 08-JUL-16 03.00.02.366000 +000 00:23:59 0
AM +08:00 AM +08:00
auto space advisor THURSDAY_WINDOW 07-JUL-16 03.00.00.159000 ORA$AT_SA_SPC_SY_642 SUCCEEDED 07-JUL-16 03.00.01.422000 +000 00:04:42 0
AM +08:00 AM +08:00
sql tuning advisor THURSDAY_WINDOW 14-JUL-16 03.00.00.273000 ORA$AT_SQ_SQL_SW_763 SUCCEEDED 14-JUL-16 03.00.02.442000 +000 01:00:06 0
AM +08:00 AM +08:00
sql tuning advisor WEDNESDAY_WINDOW 13-JUL-16 03.00.00.147000 ORA$AT_SQ_SQL_SW_743 SUCCEEDED 13-JUL-16 03.00.02.097000 +000 01:00:09 0
AM +08:00 AM +08:00
sql tuning advisor TUESDAY_WINDOW 12-JUL-16 03.00.00.171000 ORA$AT_SQ_SQL_SW_723 SUCCEEDED 12-JUL-16 03.00.02.371000 +000 01:00:05 0
AM +08:00 AM +08:00
sql tuning advisor MONDAY_WINDOW 11-JUL-16 03.00.00.024000 ORA$AT_SQ_SQL_SW_706 SUCCEEDED 11-JUL-16 03.00.02.052000 +000 01:00:06 0
AM +08:00 AM +08:00
sql tuning advisor SUNDAY_WINDOW 10-JUL-16 03.00.00.141000 ORA$AT_SQ_SQL_SW_703 SUCCEEDED 10-JUL-16 03.00.02.232000 +000 01:00:03 0
AM +08:00 AM +08:00
sql tuning advisor SATURDAY_WINDOW 09-JUL-16 03.00.00.176000 ORA$AT_SQ_SQL_SW_683 SUCCEEDED 09-JUL-16 03.00.02.314000 +000 01:00:05 0
AM +08:00 AM +08:00
sql tuning advisor FRIDAY_WINDOW 08-JUL-16 03.00.00.182000 ORA$AT_SQ_SQL_SW_663 SUCCEEDED 08-JUL-16 03.00.02.366000 +000 01:00:02 0
AM +08:00 AM +08:00
sql tuning advisor THURSDAY_WINDOW 07-JUL-16 03.00.00.159000 ORA$AT_SQ_SQL_SW_643 SUCCEEDED 07-JUL-16 03.00.01.422000 +000 01:00:03 0
AM +08:00 AM +08:00
24 rows selected.
hbjzt@TREE6>
以上信息显示自动任务auto optimizer stats collection 是正常的
5.查看统计信息是否被锁：
select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS from DBA_TAB_STATISTICS where owner= upper('&owner') and TABLE_NAME = upper('&table_name') ;
select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS from DBA_TAB_STATISTICS where owner= upper('TREE_HIS') and TABLE_NAME in ('CK_BCLSB_MX')
union all
select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS from DBA_TAB_STATISTICS where owner= upper('TREE') and TABLE_NAME in ('CK_BCLSB_MX')
union all
select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS from DBA_TAB_STATISTICS where owner= upper('TREE') and TABLE_NAME in ('CK_KPD_HZ')
OWNER TABLE_NAME LAST_ANALYZED STATTYPE_LOCKED STALE_STA
--------------- ------------------------------ ------------------ --------------- ---------
TREE_HIS CK_BCLSB_MX 26-MAR-14 ALL NO
TREE CK_BCLSB_MX 14-JUL-16 NO
TREE CK_KPD_HZ 14-JUL-16 NO
很明显统计信息有效，但是却被锁了。
6.查看多少表统计信息被锁
select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS from DBA_TAB_STATISTICS where STATTYPE_LOCKED='ALL' and owner not in ('SYS', 'WMSYS', 'SYSTEM', 'SYS', 'SYSMAN') and table_name not like '%$%' ;
SELECT 'dbms_stats.gather_table_stats('''||owner||''','''|| table_name||''');'
FROM DBA_TAB_STATISTICS
where stattype_locked = 'ALL'
and owner not in ('SYS', 'WMSYS', 'SYSTEM', 'SYS', 'SYSMAN')
and table_name not like '%$%' ;
7. 解除被锁的统计信息而后重新生成统计信息
exec dbms_stats.UNLOCK_TABLE_STATS('TREE_HIS','CK_BCLSB_MX') ;
exec dbms_stats.gather_table_stats('TREE_HIS','CK_BCLSB_MX') ;
hbjzt@TREE6> exec dbms_stats.UNLOCK_TABLE_STATS('TREE_HIS','CK_BCLSB_MX') ;
PL/SQL procedure successfully completed.
select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS from DBA_TAB_STATISTICS where owner= upper('TREE_HIS') and TABLE_NAME in ('CK_BCLSB_MX')
union all
select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS from DBA_TAB_STATISTICS where owner= upper('TREE') and TABLE_NAME in ('CK_BCLSB_MX')
union all
5 select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS from DBA_TAB_STATISTICS where owner= upper('TREE') and TABLE_NAME in ('CK_KPD_HZ')
6 /
OWNER TABLE_NAME LAST_ANALYZED STATTYPE_LOCKED STALE_STA
--------------- ------------------------------ ------------------ --------------- ---------
TREE_HIS CK_BCLSB_MX 26-MAR-14 NO
TREE CK_BCLSB_MX 14-JUL-16 NO
TREE CK_KPD_HZ 14-JUL-16 NO
hbjzt@TREE6> select count(1) from TREE_his.CK_BCLSB_MX ;
COUNT(1)
----------
38306
hbjzt@TREE6> exec dbms_stats.gather_table_stats('TREE_HIS','CK_BCLSB_MX') ;
PL/SQL procedure successfully completed.
select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS from DBA_TAB_STATISTICS where owner= upper('TREE_HIS') and TABLE_NAME in ('CK_BCLSB_MX')
union all
select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS from DBA_TAB_STATISTICS where owner= upper('TREE') and TABLE_NAME in ('CK_BCLSB_MX')
union all
5 select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS from DBA_TAB_STATISTICS where owner= upper('TREE') and TABLE_NAME in ('CK_KPD_HZ')
6 /
OWNER TABLE_NAME LAST_ANALYZED STATTYPE_LOCKED STALE_STA
--------------- ------------------------------ ------------------ --------------- ---------
TREE_HIS CK_BCLSB_MX 14-JUL-16 NO
TREE CK_BCLSB_MX 14-JUL-16 NO
TREE CK_KPD_HZ 14-JUL-16 NO
SELECT * FROM CK_BCLSB_MX TA
WHERE NOT EXISTS
(SELECT * FROM TREE_HIS.CK_BCLSB_MX
WHERE BOCI_NO = TA.BOCI_NO AND HANGHAO = TA.HANGHAO AND DANJ_NO = TA.DANJ_NO)
AND NOT EXISTS (SELECT 1
6 FROM CK_KPD_HZ WHERE DANJ_NO= TA.DANJ_NO AND ZUOY_STATE<'N2' )
7 /
SELECT * FROM CK_BCLSB_MX TA
*
ERROR at line 1:
ORA-00942: table or view does not exist
hbjzt@TREE6> alter session set current_schema=TREE ;
Session altered.
hbjzt@TREE6> SELECT count(1) FROM TREE.CK_BCLSB_MX TA WHERE NOT EXISTS (SELECT * FROM TREE_HIS.CK_BCLSB_MX WHERE BOCI_NO = TA.BOCI_NO AND HANGHAO = TA.HANGHAO AND DANJ_NO = TA.DANJ_NO) AND NOT EXISTS (SELECT 1 FROM CK_KPD_HZ WHERE DANJ_NO= TA.DANJ_NO AND ZUOY_STATE<'N2' )
2 /
COUNT(1)
----------
4836
原因找到了，问题就容易解决了。
ORA-00600: 内部错误代码, 参数: [19004], [], [], [], [], [], [], [], [], [], [], []
1.查看报错信息
查看alert.log文件中提示的trc文件显示运行下面一条sql语句报错的
INSERT INTO TREE_HIS.CK_BCLSB_MX
SELECT * FROM CK_BCLSB_MX TA
WHERE NOT EXISTS
(SELECT * FROM TREE_HIS.CK_BCLSB_MX
WHERE BOCI_NO = TA.BOCI_NO AND HANGHAO = TA.HANGHAO AND DANJ_NO = TA.DANJ_NO)
AND NOT EXISTS (SELECT 1
FROM CK_KPD_HZ WHERE DANJ_NO= TA.DANJ_NO AND ZUOY_STATE<'N2' )
报 ORA-00600: 内部错误代码, 参数: [19004]
实际情况是运行一个procedure TREE.PRC_UTL_DAYTERM ，运行到上面一条语句就报了这个错误
信息如下：
ORA-12012: 自动执行作业 21477 出错
ORA-14400: 插入的分区关键字未映射到任何分区
ORA-06512: 在 "TREE.PRC_UTL_DAYTERM", line 432
2.分析报错信息
单独运行如下语句
SELECT count(1)
FROM CK_BCLSB_MX TA
WHERE NOT EXISTS (SELECT *
FROM TREE_HIS.CK_BCLSB_MX
WHERE BOCI_NO = TA.BOCI_NO
AND HANGHAO = TA.HANGHAO
AND DANJ_NO = TA.DANJ_NO)
AND NOT EXISTS (SELECT 1
FROM CK_KPD_HZ
WHERE DANJ_NO = TA.DANJ_NO
AND ZUOY_STATE < 'N2')
不通过，报错误
ORA-00600: 内部错误代码, 参数: [19004], [], [], [], [], [], [], [], [], [], [], []
3.排查报错信息
网上查找一般是统计信息出错所致：
那么就通过DBA_TAB_STATISTICS视图，一张一张表分析这张表的统计信息，如下：
select * from DBA_TAB_STATISTICS where owner= upper('&owner') and TABLE_NAME = upper('&table_name') ;
hbjzt@TREE6> select * from DBA_TAB_STATISTICS where owner='TREE_HIS' and TABLE_NAME = 'CK_BCLSB_MX' ;
OWNER TABLE_NAME PARTITION_NAME
--------------- ------------------------------ ------------------------------------------------------------------------------------------
PARTITION_POSITION SUBPARTITION_NAME SUBPARTITION_POSITION
------------------ ------------------------------------------------------------------------------------------ ---------------------
OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS
------------------------------------ ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- -------------------
AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE LAST_ANALYZED GLOBAL_ST USER_STAT STATTYPE_LOCKED STALE_STA
----------------- ------------------- ----------- ------------------ --------- --------- --------------- ---------
TREE_HIS CK_BCLSB_MX
TABLE 1704995 6070 0 0 0 45 0 0
1704995 26-MAR-14 YES NO ALL NO
hbjzt@TREE6> select * from DBA_TAB_STATISTICS where owner= upper('&owner') and TABLE_NAME = upper('&table_name') ;
Enter value for owner: TREE
Enter value for table_name: CK_BCLSB_MX
old 1: select * from DBA_TAB_STATISTICS where owner= upper('&owner') and TABLE_NAME = upper('&table_name')
new 1: select * from DBA_TAB_STATISTICS where owner= upper('TREE') and TABLE_NAME = upper('CK_BCLSB_MX')
OWNER TABLE_NAME PARTITION_NAME
--------------- ------------------------------ ------------------------------------------------------------------------------------------
PARTITION_POSITION SUBPARTITION_NAME SUBPARTITION_POSITION
------------------ ------------------------------------------------------------------------------------------ ---------------------
OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS
------------------------------------ ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- -------------------
AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE LAST_ANALYZED GLOBAL_ST USER_STAT STATTYPE_LOCKED STALE_STA
----------------- ------------------- ----------- ------------------ --------- --------- --------------- ---------
TREE CK_BCLSB_MX
TABLE 4724 31 0 0 0 44 0 0
4724 14-JUL-16 YES NO NO
hbjzt@TREE6> select * from DBA_TAB_STATISTICS where owner= upper('&owner') and TABLE_NAME = upper('&table_name') ;
Enter value for owner: TREE
Enter value for table_name: CK_KPD_HZ
old 1: select * from DBA_TAB_STATISTICS where owner= upper('&owner') and TABLE_NAME = upper('&table_name')
new 1: select * from DBA_TAB_STATISTICS where owner= upper('TREE') and TABLE_NAME = upper('CK_KPD_HZ')
OWNER TABLE_NAME PARTITION_NAME
--------------- ------------------------------ ------------------------------------------------------------------------------------------
PARTITION_POSITION SUBPARTITION_NAME SUBPARTITION_POSITION
------------------ ------------------------------------------------------------------------------------------ ---------------------
OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS
------------------------------------ ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- -------------------
AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE LAST_ANALYZED GLOBAL_ST USER_STAT STATTYPE_LOCKED STALE_STA
----------------- ------------------- ----------- ------------------ --------- --------- --------------- ---------
TREE CK_KPD_HZ
TABLE 4667 247 0 0 0 268 0 0
4667 14-JUL-16 YES NO NO
hbjzt@TREE6> select count(1) from TREE_his.CK_BCLSB_MX ;
COUNT(1)
----------
38306 ---统计信息里显示是6070,相差很大,明显是统计信息出问题了。
但是为什么呢？其他的表有统计信息收集，而这张表没有呢
4. 查看自动任务是否开启：
col client_name format a32
col consumer_group format a30
col window_group format a32
col servICE_name format a15
col attributes format a55
SELECT CLIENT_NAME,STATUS,CONSUMER_GROUP,WINDOW_GROUP,SERVICE_NAME,ATTRIBUTES FROM DBA_AUTOTASK_CLIENT;
CLIENT_NAME STATUS CONSUMER_GROUP WINDOW_GROUP SERVICE_NAME
-------------------------------- ------------------------ ------------------------------ -------------------------------- ---------------
ATTRIBUTES
-------------------------------------------------------
auto optimizer stats collection ENABLED ORA$AUTOTASK_STATS_GROUP ORA$AT_WGRP_OS
ON BY DEFAULT, VOLATILE, SAFE TO KILL
auto space advisor ENABLED ORA$AUTOTASK_SPACE_GROUP ORA$AT_WGRP_SA
ON BY DEFAULT, VOLATILE, SAFE TO KILL
sql tuning advisor DISABLED ORA$AUTOTASK_SQL_GROUP ORA$AT_WGRP_SQ
ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL
col window_next_time format a26
col WINDOW_ACTIVE format a13
SELECT * FROM DBA_AUTOTASK_WINDOW_CLIENTS;
WINDOW_NAME WINDOW_NEXT_TIME WINDOW_ACTIVE AUTOTASK_STATUS OPTIMIZER_STATS SEGMENT_ADVISOR
-------------------- -------------------------- ------------- ------------------------ ------------------------ ------------------------
SQL_TUNE_ADVISOR HEALTH_MONITOR
------------------------ ------------------------
MONDAY_WINDOW 18-JUL-16 01.00.00.000000 FALSE ENABLED ENABLED ENABLED
AM +08:00
DISABLED DISABLED
TUESDAY_WINDOW 19-JUL-16 01.00.00.000000 FALSE ENABLED ENABLED ENABLED
AM +08:00
DISABLED DISABLED
WEDNESDAY_WINDOW 20-JUL-16 01.00.00.000000 FALSE ENABLED ENABLED ENABLED
AM +08:00
DISABLED DISABLED
THURSDAY_WINDOW 21-JUL-16 01.00.00.000000 FALSE ENABLED ENABLED ENABLED
AM +08:00
DISABLED DISABLED
FRIDAY_WINDOW 15-JUL-16 01.00.00.000000 FALSE ENABLED ENABLED ENABLED
AM +08:00
DISABLED DISABLED
SATURDAY_WINDOW 16-JUL-16 01.00.00.000000 FALSE ENABLED ENABLED ENABLED
AM +08:00
DISABLED DISABLED
SUNDAY_WINDOW 17-JUL-16 01.00.00.000000 FALSE ENABLED ENABLED ENABLED
AM +08:00
DISABLED DISABLED
7 rows selected.
hbjzt@TREE6>
hbjzt@TREE6>
hbjzt@TREE6> set linesize 300
col job_info format a50
col job_name format a30
col window_name format a17
col job_status format a11
col job_duration format a13
col window_start_time format a26
col job_start_time format a26
SELECT CLIENT_NAME,WINDOW_NAME,WINDOW_START_TIME,JOB_NAME,JOB_STATUS,JOB_START_TIME,JOB_DURATION,JOB_ERROR,JOB_INFO
FROM (
SELECT CLIENT_NAME,
WINDOW_NAME,
WINDOW_START_TIME,
JOB_NAME,
JOB_STATUS,
JOB_START_TIME,
JOB_DURATION,
JOB_ERROR,
JOB_INFO,
ROW_NUMBER() OVER(PARTITION BY CLIENT_NAME ORDER BY JOB_START_TIME DESC) AS RN
FROM DBA_AUTOTASK_JOB_HISTORY
) T
15 WHERE RN <= 10;
CLIENT_NAME WINDOW_NAME WINDOW_START_TIME JOB_NAME JOB_STATUS JOB_START_TIME JOB_DURATION JOB_ERROR JOB_INFO
-------------------------------- ----------------- -------------------------- ------------------------------ ----------- -------------------------- ------------- ---------- --------------------------------------------------
auto optimizer stats collection THURSDAY_WINDOW 14-JUL-16 03.00.00.273000 ORA$AT_OS_OPT_SY_761 SUCCEEDED 14-JUL-16 03.00.02.457000 +000 00:06:05 0
AM +08:00 AM +08:00
auto optimizer stats collection WEDNESDAY_WINDOW 13-JUL-16 03.00.00.147000 ORA$AT_OS_OPT_SY_741 SUCCEEDED 13-JUL-16 03.00.02.097000 +000 00:10:34 0
AM +08:00 AM +08:00
auto optimizer stats collection TUESDAY_WINDOW 12-JUL-16 03.00.00.171000 ORA$AT_OS_OPT_SY_721 SUCCEEDED 12-JUL-16 03.00.02.371000 +000 00:06:34 0
AM +08:00 AM +08:00
auto optimizer stats collection MONDAY_WINDOW 11-JUL-16 03.00.00.024000 ORA$AT_OS_OPT_SY_704 SUCCEEDED 11-JUL-16 03.00.02.052000 +000 00:02:33 0
AM +08:00 AM +08:00
auto optimizer stats collection SUNDAY_WINDOW 10-JUL-16 03.00.00.141000 ORA$AT_OS_OPT_SY_701 SUCCEEDED 10-JUL-16 03.00.02.232000 +000 00:04:13 0
AM +08:00 AM +08:00
auto optimizer stats collection SATURDAY_WINDOW 09-JUL-16 03.00.00.176000 ORA$AT_OS_OPT_SY_681 SUCCEEDED 09-JUL-16 03.00.02.314000 +000 00:20:48 0
AM +08:00 AM +08:00
auto optimizer stats collection FRIDAY_WINDOW 08-JUL-16 03.00.00.182000 ORA$AT_OS_OPT_SY_661 SUCCEEDED 08-JUL-16 03.00.02.366000 +000 00:07:10 0
AM +08:00 AM +08:00
auto optimizer stats collection THURSDAY_WINDOW 07-JUL-16 03.00.00.159000 ORA$AT_OS_OPT_SY_641 SUCCEEDED 07-JUL-16 03.00.01.422000 +000 00:09:11 0
AM +08:00 AM +08:00
auto space advisor THURSDAY_WINDOW 14-JUL-16 03.00.00.273000 ORA$AT_SA_SPC_SY_762 SUCCEEDED 14-JUL-16 03.00.02.457000 +000 00:03:49 0
AM +08:00 AM +08:00
auto space advisor WEDNESDAY_WINDOW 13-JUL-16 03.00.00.147000 ORA$AT_SA_SPC_SY_742 SUCCEEDED 13-JUL-16 03.00.02.097000 +000 00:04:49 0
AM +08:00 AM +08:00
auto space advisor TUESDAY_WINDOW 12-JUL-16 03.00.00.171000 ORA$AT_SA_SPC_SY_722 SUCCEEDED 12-JUL-16 03.00.02.371000 +000 00:04:35 0
AM +08:00 AM +08:00
auto space advisor MONDAY_WINDOW 11-JUL-16 03.00.00.024000 ORA$AT_SA_SPC_SY_705 SUCCEEDED 11-JUL-16 03.00.02.052000 +000 00:00:54 0
AM +08:00 AM +08:00
auto space advisor SUNDAY_WINDOW 10-JUL-16 03.00.00.141000 ORA$AT_SA_SPC_SY_702 SUCCEEDED 10-JUL-16 03.00.02.232000 +000 00:02:11 0
AM +08:00 AM +08:00
auto space advisor SATURDAY_WINDOW 09-JUL-16 03.00.00.176000 ORA$AT_SA_SPC_SY_682 SUCCEEDED 09-JUL-16 03.00.02.314000 +000 00:59:59 0
AM +08:00 AM +08:00
auto space advisor FRIDAY_WINDOW 08-JUL-16 03.00.00.182000 ORA$AT_SA_SPC_SY_662 SUCCEEDED 08-JUL-16 03.00.02.366000 +000 00:23:59 0
AM +08:00 AM +08:00
auto space advisor THURSDAY_WINDOW 07-JUL-16 03.00.00.159000 ORA$AT_SA_SPC_SY_642 SUCCEEDED 07-JUL-16 03.00.01.422000 +000 00:04:42 0
AM +08:00 AM +08:00
sql tuning advisor THURSDAY_WINDOW 14-JUL-16 03.00.00.273000 ORA$AT_SQ_SQL_SW_763 SUCCEEDED 14-JUL-16 03.00.02.442000 +000 01:00:06 0
AM +08:00 AM +08:00
sql tuning advisor WEDNESDAY_WINDOW 13-JUL-16 03.00.00.147000 ORA$AT_SQ_SQL_SW_743 SUCCEEDED 13-JUL-16 03.00.02.097000 +000 01:00:09 0
AM +08:00 AM +08:00
sql tuning advisor TUESDAY_WINDOW 12-JUL-16 03.00.00.171000 ORA$AT_SQ_SQL_SW_723 SUCCEEDED 12-JUL-16 03.00.02.371000 +000 01:00:05 0
AM +08:00 AM +08:00
sql tuning advisor MONDAY_WINDOW 11-JUL-16 03.00.00.024000 ORA$AT_SQ_SQL_SW_706 SUCCEEDED 11-JUL-16 03.00.02.052000 +000 01:00:06 0
AM +08:00 AM +08:00
sql tuning advisor SUNDAY_WINDOW 10-JUL-16 03.00.00.141000 ORA$AT_SQ_SQL_SW_703 SUCCEEDED 10-JUL-16 03.00.02.232000 +000 01:00:03 0
AM +08:00 AM +08:00
sql tuning advisor SATURDAY_WINDOW 09-JUL-16 03.00.00.176000 ORA$AT_SQ_SQL_SW_683 SUCCEEDED 09-JUL-16 03.00.02.314000 +000 01:00:05 0
AM +08:00 AM +08:00
sql tuning advisor FRIDAY_WINDOW 08-JUL-16 03.00.00.182000 ORA$AT_SQ_SQL_SW_663 SUCCEEDED 08-JUL-16 03.00.02.366000 +000 01:00:02 0
AM +08:00 AM +08:00
sql tuning advisor THURSDAY_WINDOW 07-JUL-16 03.00.00.159000 ORA$AT_SQ_SQL_SW_643 SUCCEEDED 07-JUL-16 03.00.01.422000 +000 01:00:03 0
AM +08:00 AM +08:00
24 rows selected.
hbjzt@TREE6>
以上信息显示自动任务auto optimizer stats collection 是正常的
5.查看统计信息是否被锁：
select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS from DBA_TAB_STATISTICS where owner= upper('&owner') and TABLE_NAME = upper('&table_name') ;
select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS from DBA_TAB_STATISTICS where owner= upper('TREE_HIS') and TABLE_NAME in ('CK_BCLSB_MX')
union all
select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS from DBA_TAB_STATISTICS where owner= upper('TREE') and TABLE_NAME in ('CK_BCLSB_MX')
union all
select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS from DBA_TAB_STATISTICS where owner= upper('TREE') and TABLE_NAME in ('CK_KPD_HZ')
OWNER TABLE_NAME LAST_ANALYZED STATTYPE_LOCKED STALE_STA
--------------- ------------------------------ ------------------ --------------- ---------
TREE_HIS CK_BCLSB_MX 26-MAR-14 ALL NO
TREE CK_BCLSB_MX 14-JUL-16 NO
TREE CK_KPD_HZ 14-JUL-16 NO
很明显统计信息有效，但是却被锁了。
6.查看多少表统计信息被锁
select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS from DBA_TAB_STATISTICS where STATTYPE_LOCKED='ALL' and owner not in ('SYS', 'WMSYS', 'SYSTEM', 'SYS', 'SYSMAN') and table_name not like '%$%' ;
SELECT 'dbms_stats.gather_table_stats('''||owner||''','''|| table_name||''');'
FROM DBA_TAB_STATISTICS
where stattype_locked = 'ALL'
and owner not in ('SYS', 'WMSYS', 'SYSTEM', 'SYS', 'SYSMAN')
and table_name not like '%$%' ;
7. 解除被锁的统计信息而后重新生成统计信息
exec dbms_stats.UNLOCK_TABLE_STATS('TREE_HIS','CK_BCLSB_MX') ;
exec dbms_stats.gather_table_stats('TREE_HIS','CK_BCLSB_MX') ;
hbjzt@TREE6> exec dbms_stats.UNLOCK_TABLE_STATS('TREE_HIS','CK_BCLSB_MX') ;
PL/SQL procedure successfully completed.
select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS from DBA_TAB_STATISTICS where owner= upper('TREE_HIS') and TABLE_NAME in ('CK_BCLSB_MX')
union all
select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS from DBA_TAB_STATISTICS where owner= upper('TREE') and TABLE_NAME in ('CK_BCLSB_MX')
union all
5 select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS from DBA_TAB_STATISTICS where owner= upper('TREE') and TABLE_NAME in ('CK_KPD_HZ')
6 /
OWNER TABLE_NAME LAST_ANALYZED STATTYPE_LOCKED STALE_STA
--------------- ------------------------------ ------------------ --------------- ---------
TREE_HIS CK_BCLSB_MX 26-MAR-14 NO
TREE CK_BCLSB_MX 14-JUL-16 NO
TREE CK_KPD_HZ 14-JUL-16 NO
hbjzt@TREE6> select count(1) from TREE_his.CK_BCLSB_MX ;
COUNT(1)
----------
38306
hbjzt@TREE6> exec dbms_stats.gather_table_stats('TREE_HIS','CK_BCLSB_MX') ;
PL/SQL procedure successfully completed.
select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS from DBA_TAB_STATISTICS where owner= upper('TREE_HIS') and TABLE_NAME in ('CK_BCLSB_MX')
union all
select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS from DBA_TAB_STATISTICS where owner= upper('TREE') and TABLE_NAME in ('CK_BCLSB_MX')
union all
5 select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS from DBA_TAB_STATISTICS where owner= upper('TREE') and TABLE_NAME in ('CK_KPD_HZ')
6 /
OWNER TABLE_NAME LAST_ANALYZED STATTYPE_LOCKED STALE_STA
--------------- ------------------------------ ------------------ --------------- ---------
TREE_HIS CK_BCLSB_MX 14-JUL-16 NO
TREE CK_BCLSB_MX 14-JUL-16 NO
TREE CK_KPD_HZ 14-JUL-16 NO
SELECT * FROM CK_BCLSB_MX TA
WHERE NOT EXISTS
(SELECT * FROM TREE_HIS.CK_BCLSB_MX
WHERE BOCI_NO = TA.BOCI_NO AND HANGHAO = TA.HANGHAO AND DANJ_NO = TA.DANJ_NO)
AND NOT EXISTS (SELECT 1
6 FROM CK_KPD_HZ WHERE DANJ_NO= TA.DANJ_NO AND ZUOY_STATE<'N2' )
7 /
SELECT * FROM CK_BCLSB_MX TA
*
ERROR at line 1:
ORA-00942: table or view does not exist
hbjzt@TREE6> alter session set current_schema=TREE ;
Session altered.
hbjzt@TREE6> SELECT count(1) FROM TREE.CK_BCLSB_MX TA WHERE NOT EXISTS (SELECT * FROM TREE_HIS.CK_BCLSB_MX WHERE BOCI_NO = TA.BOCI_NO AND HANGHAO = TA.HANGHAO AND DANJ_NO = TA.DANJ_NO) AND NOT EXISTS (SELECT 1 FROM CK_KPD_HZ WHERE DANJ_NO= TA.DANJ_NO AND ZUOY_STATE<'N2' )
2 /
COUNT(1)
----------
4836
原因找到了，问题就容易解决了。
扫一扫
3947
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
