【ORA-00600】ORA-00600: 內部錯誤碼, 參數: [5213], [], [], [], [], [], [], []

Question

操作系統：Windows Server 2003 R2
數據庫：Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production執行下面這段查詢SQL報錯：ORA-00600: 內部錯誤碼, 參數: [5213], [], [], [], [], [], [], []請問這個錯誤怎樣解決？Google了一下，沒有明確的方向.. 只感覺跟視圖、UNION有關係，請指點~

Answer 1

SQL太长了，只能分两段贴了：
SELECT   'a' TYPE, x.ID, x.process_pt, x.produce_no, x.grade, x.status,
         x.base, x.base * z.RIGHT effect, x.process_date_s,
         x.actual_finish_date, x.wait_time, x.print_min, x.std_wk_time,
         x.diff_time, x.code, x.code_text
    FROM (SELECT x.ID, x.produce_no, x.process_pt, x.grade, x.status, y.base,
                 y.pk_id, x.actual_finish_date, x.process_date_s, x.wait_time,
                 x.print_min, std_wk_time, diff_time, w.code, w.code_text
            FROM (SELECT b.ID, a.produce_no, a.process_pt, d.grade,
                         DECODE (a.status,
                                 '0', '未加工',
                                 '1', '加工完成',
                                 '2', '加工中',
                                 '3', '加工暫停',
                                 '4', '加工取消',
                                 ''
                                ) status,
                         a.actual_finish_date, a.process_date_s,
                         NVL (w.wait_time, 0) * 60 wait_time,
                           (NVL
                               ((    ((  NVL
                                            (  TO_DATE
                                                  (TO_CHAR
                                                          (a.pre_process_time,
                                                           'HH24:MI'
                                                          ),
                                                   'HH24:MI'
                                                  )
                                             - TO_DATE ('00:00', 'HH24:MI'),
                                             0
                                            )
                                       + (  a.actual_finish_date
                                          - a.process_date_s
                                         )
                                      )
                                     )
                                   * 24
                                 - NVL (w.wait_time, 0)
                                ),
                                0
                               )
                           )
                         * 60 print_min,
                         (d.std_wk_time),
                             (NVL
                                 ((    ((  NVL
                                              (  TO_DATE
                                                    (TO_CHAR
                                                          (a.pre_process_time,
                                                           'HH24:MI'
                                                          ),
                                                     'HH24:MI'
                                                    )
                                               - TO_DATE ('00:00', 'HH24:MI'),
                                               0
                                              )
                                         + (  a.actual_finish_date
                                            - a.process_date_s
                                           )
                                        )
                                       )
                                     * 24
                                   - NVL (w.wait_time, 0)
                                  ),
                                  0
                                 )
                             )
                           * 60
                         - (d.std_wk_time) diff_time,
                         a.CLASS
                    FROM sales.process_schedule_finish_n a,
                         personnel.v_performance_schedule b,
                         sales.produce_process d,
                         (SELECT   a.pk_item,
                                     SUM
                                        (  TO_DATE (TO_CHAR (wait_time,
                                                             'HH24:MI'
                                                            ),
                                                    'hh24:mi'
                                                   )
                                         - TO_DATE ('00:00', 'hh24:mi')
                                        )
                                   * 24 wait_time
                              FROM sales.process_break_time a,
                                   sales.process_schedule_finish_n b
                             WHERE b.actual_finish_date
                                      BETWEEN TO_DATE (:sday, 'yyyymmdd') - 10
                                          AND TO_DATE (:eday, 'yyyymmdd') + 10
                               AND a.pk_item = b.pk_item
                               AND a.CATEGORY = '1'
                          GROUP BY a.pk_item) w
                   WHERE a.member_id = b.ID
                     AND a.process_date_s BETWEEN b.p_sdate AND b.p_edate
                     AND a.process_pt IN ('1', '2', '3')
                     AND a.produce_no = d.produce_no
                     AND a.process_pt = d.process_id
                     AND w.pk_item(+) = a.pk_item
                     AND a.member_id BETWEEN :SID AND :eid
                     AND (   :p_rework = 'N'
                          OR (    :p_rework = 'Y'
                              AND EXISTS (
                                     SELECT 'x'
                                       FROM erpuser.rd041_i b
                                      WHERE produce_no = a.produce_no
                                        AND process_pt = a.process_pt
                                        AND member_id = a.member_id
                                        AND actual_finish_date =
                                                          a.actual_finish_date)
                             )
                         )) x,
                 sales.design_level y,
                 sales.design_delay_reason w
           WHERE x.process_pt = y.process_pt
             AND x.grade = y.grade
             AND x.process_pt = w.process_pt(+)
             AND x.CLASS = w.code(+)) x,
         sales.design_level_detail z
   WHERE x.pk_id = z.pk_id(+) AND TRUNC (x.diff_time) BETWEEN z.low(+) AND z.high(+)

Answer 2

UNION
SELECT   'c' TYPE, x.ID, x.process_pt, x.produce_no, NULL grade, x.status,
         0 base, 0 effect, x.process_date_s, x.actual_finish_date,
         x.wait_time, x.print_min, 0 std_wk_time, 0 diff_time, w.code,
         w.code_text
    FROM (SELECT b.ID, a.produce_no,
                 DECODE (a.status,
                         '0', '未加工',
                         '1', '加工完成',
                         '2', '加工中',
                         '3', '加工暫停',
                         '4', '加工取消',
                         ''
                        ) status,
                 a.process_pt, a.actual_finish_date, a.process_date_s,
                 NVL (w.wait_time, 0) * 60 wait_time,
                   (NVL ((    ((  NVL
                                     (  TO_DATE (TO_CHAR (a.pre_process_time,
                                                          'HH24:MI'
                                                         ),
                                                 'HH24:MI'
                                                )
                                      - TO_DATE ('00:00', 'HH24:MI'),
                                      0
                                     )
                                + (a.actual_finish_date - a.process_date_s)
                               )
                              )
                            * 24
                          - NVL (w.wait_time, 0)
                         ),
                         0
                        )
                   )
                 * 60 print_min,
                 a.CLASS
            FROM sales.process_schedule_finish_n a,
                 personnel.v_performance_schedule b,
                 (SELECT   a.pk_item,
                             SUM (  TO_DATE (TO_CHAR (wait_time, 'HH24:MI'),
                                             'hh24:mi'
                                            )
                                  - TO_DATE ('00:00', 'hh24:mi')
                                 )
                           * 24 wait_time
                      FROM sales.process_break_time a,
                           sales.process_schedule_finish_n b
                     WHERE b.actual_finish_date BETWEEN   TO_DATE (:sday,
                                                                   'yyyymmdd'
                                                                  )
                                                        - 10
                                                    AND   TO_DATE (:eday,
                                                                   'yyyymmdd'
                                                                  )
                                                        + 10
                       AND a.pk_item = b.pk_item
                       AND a.CATEGORY = '1'
                  GROUP BY a.pk_item) w
           WHERE a.member_id = b.ID
             AND a.process_date_s BETWEEN b.p_sdate AND b.p_edate
             AND a.process_pt IN ('1', '2', '3')
             AND w.pk_item(+) = a.pk_item
             AND a.member_id BETWEEN :SID AND :eid
             AND a.produce_no LIKE 'c%'
             AND (   :p_rework = 'N'
                  OR (    :p_rework = 'Y'
                      AND EXISTS (
                             SELECT 'x'
                               FROM erpuser.rd041_i b
                              WHERE produce_no = a.produce_no
                                AND process_pt = a.process_pt
                                AND member_id = a.member_id
                                AND actual_finish_date = a.actual_finish_date)
                     )
                 )) x,
         sales.design_delay_reason w
   WHERE x.process_pt = w.process_pt(+) AND x.CLASS = w.code(+)
ORDER BY ID, TYPE, process_pt, process_date_s

Answer 3

我用11g時，如果嵌套层次比较多，而且内层有分组函数的话
有时候会出这个问题。只能改sql，减少层次，或在外层求和据说把表drop掉重构也能解决内部错误，就是oracle的bug，不好解决呀

Answer 4

现在我的问题集中到了视图上：personnel.v_performance_schedule
视图查询SQL：
SELECT b.ID,
       b.cday,
       LEAST(NVL(MIN(d.o_sdate), b.c_sdate),
             DECODE(TO_CHAR(NVL(b.p_sdate, b.c_sdate), 'YYYYMMDD'),
                    '00010101',
                    b.c_sdate,
                    NVL(b.p_sdate, b.c_sdate))) p_sdate,
       DECODE(MAX(d.o_edate),
              NULL,
              DECODE(TO_CHAR(NVL(b.p_edate, b.c_edate), 'YYYYMMDD'),
                     '00010101',
                     b.c_edate,
                     NVL(b.p_edate, b.c_edate)),
              GREATEST(MAX(d.o_edate),
                       DECODE(TO_CHAR(NVL(b.p_edate, b.c_edate), 'YYYYMMDD'),
                              '00010101',
                              b.c_edate,
                              NVL(b.p_edate, b.c_edate)))) p_edate,
       decode(b.cstatus,'0',0,z.work_hour) normal_work_hour,
       decode(b.cstatus,'1',z.work_hour,
       LEAST(8,
             SUM(NVL(d.o_edate - d.o_sdate, 0) -
                 NVL(d.rest_minute, 0) / 1440) * 24))- NVL(c.d_hours, 0)work_hr,       decode(b.cstatus,'0',0,z.work_hour) +
       SUM(NVL(d.o_edate - d.o_sdate, 0) - NVL(d.rest_minute, 0) / 1440) * 24 -
       NVL(c.d_hours, 0) total_work_hr,
       SUM(NVL(d.o_edate - d.o_sdate, 0) - NVL(d.rest_minute, 0) / 1440) * 24 over_time
  FROM personnel.p_schedule b,
       personnel.p_overtime d,
       personnel.personnel x,
       personnel.CLASS z,
       (SELECT a.ID, b.cday, b.d_hours
          FROM personnel.p_absence           a,
               personnel.p_absence_detail    b,
               erpuser.temp_p_schedule_range s
         WHERE a.a_pk = b.a_pk
           AND b.cday BETWEEN s.sday AND s.eday
           AND NVL(a.a_cancel, 'N') <> 'Y') c,
       erpuser.temp_p_schedule_range s
 WHERE d.cday(+) = b.cday
   AND b.class_no = z.class_no
   AND b.c_edate IS NOT NULL
   AND d.o_cancel(+) <> 'Y'
   AND b.ID = x.ID
   AND NVL(x.punch, 'N') = 'Y'
   AND d.ID(+) = b.ID
   AND c.ID(+) = b.ID
   AND c.cday(+) = b.cday
   AND b.cday BETWEEN s.sday AND s.eday
 GROUP BY b.ID,
          b.cday,
          b.c_edate,
          b.c_sdate,
          b.p_sdate,
          p_edate,
          b.class_no,
          c.d_hours,
          b.cstatus,
          z.work_hour,
          s.eday
UNION
SELECT b.ID,
       b.cday,
       MIN(d.o_sdate) p_sdate,
       MAX(d.o_edate) p_edate,
       0 notmal_work_hour,
       LEAST(8,
             SUM(NVL(d.o_edate - d.o_sdate, 0) -
                 NVL(d.rest_minute, 0) / 1440) * 24) work_hr,
       SUM(NVL(d.o_edate - d.o_sdate, 0) - NVL(d.rest_minute, 0) / 1440) * 24 -
       NVL(c.d_hours, 0) total_work_hr,
       SUM(NVL(d.o_edate - d.o_sdate, 0) - NVL(d.rest_minute, 0) / 1440) * 24 over_time
  FROM personnel.p_schedule b,
       personnel.p_overtime d,
       personnel.personnel x,
       (SELECT a.ID, b.cday, b.d_hours
          FROM personnel.p_absence           a,
               personnel.p_absence_detail    b,
               erpuser.temp_p_schedule_range s
         WHERE a.a_pk = b.a_pk
           AND b.cday BETWEEN s.sday AND s.eday
           AND NVL(a.a_cancel, 'N') <> 'Y') c,
       erpuser.temp_p_schedule_range s
 WHERE b.c_edate IS NULL
   AND NVL(d.o_cancel, 'N') <> 'Y'
   AND b.ID = x.ID
   AND NVL(x.punch, 'N') = 'Y'
   AND d.ID = b.ID
   AND c.ID(+) = b.ID
   AND c.cday(+) = b.cday
   AND d.cday = b.cday
   AND b.cday BETWEEN s.sday AND s.eday
 GROUP BY b.ID, b.cday, c.d_hours
UNION
SELECT b.ID,
       b.cday,
       LEAST(NVL(b.c_sdate, TO_DATE(s.eday, 'yyyymmdd')), d.beg_date) p_sdate,
       GREATEST(NVL(b.c_edate, b.cday), d.end_date) p_edate,
       z.work_hour normal_work_hour,
       DECODE(b.c_edate,
              NULL,
              LEAST(8, (d.end_date - d.beg_date) * 24 - 1.5),
              z.work_hour - NVL(c.d_hours, 0)) work_hr,
       DECODE(b.c_edate, NULL, 999, z.work_hour - NVL(c.d_hours, 0)) total_work_hr,
       DECODE(b.c_edate,
              NULL,
              0,
              (GREATEST(b.c_edate, d.end_date) - b.c_edate) * 24) over_time
  FROM personnel.p_schedule b,
       personnel.CLASS z,
       (SELECT member_id,
               b.cday,
               MIN(process_date_s) beg_date,
               MAX(actual_finish_date) end_date
          FROM (SELECT f.member_id, a.process_date_s, a.actual_finish_date
                  FROM sales.process_worker            f,
                       sales.process_schedule_finish_n a,
                       personnel.personnel             x,
                       erpuser.temp_p_schedule_range   s
                 WHERE a.pk_item = f.pk_item
                   AND NVL(a.confirm, 'Y') <> 'N'
                   AND x.ID = f.member_id
                   AND NVL(x.punch, 'N') <> 'Y'
                   AND SUBSTR(x.dept, 1, 1) = '7'
                   AND x.job_title <> '1112'
                   AND process_date_s BETWEEN s.sday AND s.eday
                UNION
                SELECT member_id, process_date_s, actual_finish_date
                  FROM sales.process_schedule_finish_n a,
                       personnel.personnel             x,
                       erpuser.temp_p_schedule_range   s
                 WHERE process_date_s BETWEEN s.sday AND s.eday
                   AND x.ID = a.member_id
                   AND NVL(a.confirm, 'Y') <> 'N'
                   AND NVL(x.punch, 'N') <> 'Y'
                   AND x.job_title <> '1112'
                   AND SUBSTR(x.dept, 1, 1) = '7') x,
               personnel.p_schedule b,
               erpuser.temp_p_schedule_range s
         WHERE b.cday BETWEEN s.sday AND s.eday
           AND x.process_date_s BETWEEN NVL(c_sdate, cday + 7 / 24) AND
               NVL(c_edate, cday + 18 / 24)
           AND x.member_id = b.ID
         GROUP BY member_id, b.cday) d,
       (SELECT a.ID, b.cday, b.d_hours
          FROM personnel.p_absence           a,
               personnel.p_absence_detail    b,
               erpuser.temp_p_schedule_range s
         WHERE a.a_pk = b.a_pk
           AND b.cday BETWEEN s.sday AND s.eday
           AND NVL(a.a_cancel, 'N') <> 'Y') c,
       erpuser.temp_p_schedule_range s
 WHERE b.cday BETWEEN s.sday AND s.eday
   AND c.ID(+) = b.ID
   AND c.cday(+) = b.cday
   AND d.member_id = b.ID
   AND d.cday = b.cday
   AND b.class_no = z.class_no(+)
UNION
SELECT b.ID,
       b.cday,
       b.p_sdate,
       b.p_edate,
       (b.p_edate - b.p_sdate) * 24 - NVL(b.rest_time, 0) / 60 normal_work_hour,
       (b.p_edate - b.p_sdate) * 24 - NVL(b.rest_time, 0) / 60 work_hr,
       (b.p_edate - b.p_sdate) * 24 - NVL(b.rest_time, 0) / 60 total_work_hr,
       0 over_time
  FROM personnel.p_presnet_unscheduled b,
       personnel.personnel             x,
       erpuser.temp_p_schedule_range   s
 WHERE b.ID = x.ID
   AND x.job_title = '1112'
   AND b.cday BETWEEN s.sday AND s.eday
改成UNION ALL就能通过,使用UNION就不可以.. 这是怎么回事呢？..

Answer 5

表drop掉重构的方法试过了，行不通..

Answer 6

视图查询结果发现查询出来资料：ORA-01426: 數值發生上限溢位，可能是导致出现内部错误的根源，仍需进一步求证。参考牛人贴：有趣的数值溢出（一） 

Answer 7

   ORA-00600: 內部错误，数据的一个bug，的确不好解决！

Answer 8

哎，BUG问题难说清楚啊，我还没遇到过

Answer 9

UNION時系統會重新排序,所以如果 SORT AREA SIZE 太小時,可能就會發生這樣的錯誤,SQL改為UNION ALL就沒有問題了,但是資料重複該怎麼處理？..