ORA-00600_内部错误代码_参数19004

最新推荐文章于 2021-08-03 22:41:20 发布

原创最新推荐文章于 2021-08-03 22:41:20 发布 · 1.2w 阅读

CC 4.0 BY-SA版权

监控信息显示报如下错误：
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


      原因找到了，问题就容易解决了。