Oracle ORA-00600

发布于 2024-07-13 08:16:13 字数 947 浏览 7 评论 0原文

我的 SQL SELECT 语句返回：

    Error: ORA-00600: internal error code, arguments: [qerpfAllocateR], [], [], [], [], [], [], []

如果我通过在 WHERE 子句中添加一个条件来缩小结果范围，则一切正常。

有人知道发生了什么事吗？

编辑：

    select * from ( select tbl1.col1, ..., tbl1.points
    from table1 tbl1, table2 tbl2
    where tbl1.tbl2FK = tbl2.PK and
          tbl2.col1 = someNumber and
          tbl1.dateColumn = to_date('27-10-2008','dd-mm-yyyy')
    order by tbl1.points desc ) s where rownum <= 3

编辑2：

我的数据库管理员建议的解决方案有效：

select * from (select rank() over (order by tbl1.points desc) rank,
                  tbl1.col1, ..., tbl1.points
           from table1 tbl1, table2 tbl2
           where tbl1.tbl2FK = tbl2.PK and
                 tbl2.col1 = someNumber and
                 tbl1.dateColumn = to_date('27-10-2008','dd-mm-yyyy')) s
     where s.rank <= 3

原文

I have SQL SELECT statement that returns:

    Error: ORA-00600: internal error code, arguments: [qerpfAllocateR], [], [], [], [], [], [], []

If I narrow my results by adding one more condition in WHERE clause everything is ok.

Anyone knows what is happening?

EDIT:

    select * from ( select tbl1.col1, ..., tbl1.points
    from table1 tbl1, table2 tbl2
    where tbl1.tbl2FK = tbl2.PK and
          tbl2.col1 = someNumber and
          tbl1.dateColumn = to_date('27-10-2008','dd-mm-yyyy')
    order by tbl1.points desc ) s where rownum <= 3

EDIT2:

My DB admin suggested solution that works:

select * from (select rank() over (order by tbl1.points desc) rank,
                  tbl1.col1, ..., tbl1.points
           from table1 tbl1, table2 tbl2
           where tbl1.tbl2FK = tbl2.PK and
                 tbl2.col1 = someNumber and
                 tbl1.dateColumn = to_date('27-10-2008','dd-mm-yyyy')) s
     where s.rank <= 3

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梓梦 2024-07-20 08:16:14

ORA-0600错误表示Oracle本身的内部错误。您很可能遇到了 Oracle 错误。

如果您访问 http://metalink.oracle.com，您可以查找确切的错误（如果是）通过将“参数”和完整堆栈跟踪粘贴到 ORA-0600 查找工具中来了解。请参阅 Metalink 注释 153788.1。

如果存在具有该描述的已知错误，则可能已经有补丁可供您下载和安装。如果这不是已知错误，请联系支持人员。

回复收藏 0 原文

剑心龙吟 2024-07-20 08:16:14

完整的查询是什么？

http://www.orafaq.com/wiki/ORA-00600 建议您应该将错误报告给oracle。

回复收藏 0 原文

灯下孤影 2024-07-20 08:16:14

祝你好运，获得 Oracle 的支持……

不过说真的，每次我遇到这个问题时，稍微重新安排一下查询通常会有所帮助。也许稍微摆弄一下索引。

回复收藏 0 原文

孤凫 2024-07-20 08:16:14

ORA-00600 基本上意味着您已经使 Oracle 服务器崩溃（不是实例，只是为您的请求提供服务的服务器）。

bdump 位置中几乎总会有一个跟踪文件。这可能不会对您有太大帮助，但对 Oracle 支持非常有帮助。

这通常是由 oracle bug 引起的，根据经验，除了通过 metalink 提出 SR（这是 Oracle 推荐的解决方案）之外，您对此无能为力。他们会尝试复制这个问题，如果幸运的话，如果它是一个错误，它最终会找到解决办法。

但在短期内（例如，几天到几个月），主要的现实解决方案是解决它。

虽然提高 SR 并不能真正给你带来多大帮助，而且可能会是一次令人沮丧的经历，但它值得这样做，因为一旦 bug 得到修复，它可能会节省其他人的时间。

回复收藏 0 原文

雨轻弹 2024-07-20 08:16:14

这些错误通常与优化器有关。我认为即使是查询中最小的更改（例如在 FROM 子句中切换 table1 和 table2 的顺序）也可能会迫使优化器选择不会遇到此错误的不同计划。

回复收藏 0 原文

埋情葬爱 2024-07-20 08:16:14

我的解决方案：

问题

在此处输入图像描述

这不起作用，出现错误 [0600]

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1904054272 bytes
Fixed Size                  2404024 bytes
Variable Size             570425672 bytes
Database Buffers         1325400064 bytes
Redo Buffers                5824512 bytes
Database mounted.
SQL> recover database
Media recovery complete.
SQL> alter database open
  2
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[108], [615], [655], [], [], [], [], [], [], []`

这是我对问题的解决方案：

    SQL> Startup mount
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown abort
ORACLE instance shut down.
SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 1904054272 bytes
Fixed Size                  2404024 bytes
Variable Size             570425672 bytes
Database Buffers         1325400064 bytes
Redo Buffers                5824512 bytes
Database mounted.
SQL> Show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      C:\APP\USER\ORADATA\ORACLEDB\C
                                                 ONTROL01.CTL, C:\APP\USER\FAST
                                                 _RECOVERY_AREA\ORACLEDB\CONTRO
                                                 L02.CTL
SQL> select a.member,a.group#,b.status from v$logfile a ,v$log b where a.group#=
b.group# and b.status='CURRENT'
  2
SQL> select a.member,a.group#,b.status from v$logfile a ,v$log b where a.group#=
b.group# and b.status='CURRENT';

MEMBER
--------------------------------------------------------------------------------

    GROUP# STATUS
---------- ----------------
C:\APP\USER\ORADATA\ORACLEDB\REDO03.LOG
         3 CURRENT


SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1904054272 bytes
Fixed Size                  2404024 bytes
Variable Size             570425672 bytes
Database Buffers         1325400064 bytes
Redo Buffers                5824512 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 4234808 generated at 01/21/2014 18:31:05 needed for thread 1
ORA-00289: suggestion :
C:\APP\USER\FAST_RECOVERY_AREA\ORACLEDB\ARCHIVELOG\2014_01_22\O1_MF_1_108_%U_.AR

C
ORA-00280: change 4234808 for thread 1 is in sequence #108


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\APP\USER\ORADATA\ORACLEDB\REDO03.LOG
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

最终它有效：

在此处输入图像描述

My solution:

Problem

enter image description here

THIS DIDNT WORK WAS GETTING ERROR [0600]

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1904054272 bytes
Fixed Size                  2404024 bytes
Variable Size             570425672 bytes
Database Buffers         1325400064 bytes
Redo Buffers                5824512 bytes
Database mounted.
SQL> recover database
Media recovery complete.
SQL> alter database open
  2
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[108], [615], [655], [], [], [], [], [], [], []`

HERE IS MY SOLUTION TO THE PROBLEM:

    SQL> Startup mount
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown abort
ORACLE instance shut down.
SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 1904054272 bytes
Fixed Size                  2404024 bytes
Variable Size             570425672 bytes
Database Buffers         1325400064 bytes
Redo Buffers                5824512 bytes
Database mounted.
SQL> Show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      C:\APP\USER\ORADATA\ORACLEDB\C
                                                 ONTROL01.CTL, C:\APP\USER\FAST
                                                 _RECOVERY_AREA\ORACLEDB\CONTRO
                                                 L02.CTL
SQL> select a.member,a.group#,b.status from v$logfile a ,v$log b where a.group#=
b.group# and b.status='CURRENT'
  2
SQL> select a.member,a.group#,b.status from v$logfile a ,v$log b where a.group#=
b.group# and b.status='CURRENT';

MEMBER
--------------------------------------------------------------------------------

    GROUP# STATUS
---------- ----------------
C:\APP\USER\ORADATA\ORACLEDB\REDO03.LOG
         3 CURRENT


SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1904054272 bytes
Fixed Size                  2404024 bytes
Variable Size             570425672 bytes
Database Buffers         1325400064 bytes
Redo Buffers                5824512 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 4234808 generated at 01/21/2014 18:31:05 needed for thread 1
ORA-00289: suggestion :
C:\APP\USER\FAST_RECOVERY_AREA\ORACLEDB\ARCHIVELOG\2014_01_22\O1_MF_1_108_%U_.AR

C
ORA-00280: change 4234808 for thread 1 is in sequence #108


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\APP\USER\ORADATA\ORACLEDB\REDO03.LOG
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

FINALLY IT WORKED:

enter image description here

回复收藏 0 原文

迷鸟归林 2024-07-20 08:16:14

ORA-00600 通常意味着出现了非常意外的情况，并且可能与数据库损坏有关。症状可能是查询是否有效，具体取决于它的制定方式。

生活示例：

对于 id=的特定行，LOB 字段更新出错
使用 SELECT * FROM 时该行不可见
但是： SELECT * FROM ; WHERE id=; 执行失败并给出 ORA-006000。

（可能）用于上述示例的解决方案

导出所有可访问的表格内容
删除表格
重新导入内容

祝你好运！

回复收藏 0 原文

执着的年纪 2024-07-20 08:16:14

当表中有 XMLTYPE 列时，我使用 PL/SQL Developer 看到过这样的错误。如果我让 PL/SQL Developer 为我创建查询框架，这种情况就不会发生，因为它会向 XMLTYPE 列请求添加一些语法，但我不记得具体是什么。

回复收藏 0 原文

故事和酒 2024-07-20 08:16:14

我在记录类型涉及 USER_TAB_COLS 的情况下遇到了这种情况。我对记录中应该保存 USER_TAB_COLS.DATA_DEFAULT 的字段使用了 VARCHAR2(128)。好吧，无论出于什么原因，这都是很长的，虽然当我在命令窗口（PL/SQL Developer）中运行代码时此映射工作正常，但当我将相同的代码嵌入到执行各种任务的伞式脚本中时它失败了。一旦我正确设置记录的数据类型以使用锚定类型 (USER_TAB_COLS.DATA_DEFAULT%TYPE) 而不是 VARCHAR2(128)，执行该工作的 sp 将在任一上下文中运行。我对它“独立”运行但从另一个脚本调用时不起作用的事实感到震惊。
这是我使用的记录类型：

TYPE PON_DICT_RECORD_TYPE Is RECORD(
    TABLE_NAME USER_TAB_COLS.TABLE_NAME%TYPE,
    PK         NUMBER(1),
    REQUIRED   NUMBER(1),
    COL_ORDER  NUMBER(3),
    COL_NAME   USER_TAB_COLS.COLUMN_NAME%TYPE,
    DATA_TYPE  USER_TAB_COLS.DATA_TYPE%TYPE,
    LENGTH     NUMBER(4),
    SCALE      NUMBER(2),
    DEF_VALUE  USER_TAB_COLS.data_default%TYPE, -- a LONG in U T C
    ID         NUMBER(1),
    FORCE_DEF  NUMBER(1),
    ACTIVE_PK NUMBER(1),
    NEW_COL_OLD_TABLE NUMBER(1),
    REPLACEMENT_KEY NUMBER(1)
    );

  TYPE PON_DICT_TABLE_TYPE IS TABLE OF PON_DICT_RECORD_TYPE;

  pdtab PON_DICT_TABLE_TYPE;

您可以在其中看到字段 DEF_VALUE 的定义现在是锚定类型。

我不得不说，这很奇怪。我本以为它在这两种情况下都会失败。

I encountered this in a situation with a record type that involved USER_TAB_COLS. I had used VARCHAR2(128) for the field in the record that was supposed to hold USER_TAB_COLS.DATA_DEFAULT. Well, that's a LONG, for whatever reason, and while this mapping worked OK when I ran the code in a command window (PL/SQL Developer), it failed when I embedded the very same in an umbrella script that did all sorts of tasks. Once I set the data type of the record correctly to use an anchored type (USER_TAB_COLS.DATA_DEFAULT%TYPE) instead of VARCHAR2(128), the sp that does the work runs in either context. I was buffaloed by the fact that it worked 'stand-alone' but not when called from another script.
Here is the record type I was using:

TYPE PON_DICT_RECORD_TYPE Is RECORD(
    TABLE_NAME USER_TAB_COLS.TABLE_NAME%TYPE,
    PK         NUMBER(1),
    REQUIRED   NUMBER(1),
    COL_ORDER  NUMBER(3),
    COL_NAME   USER_TAB_COLS.COLUMN_NAME%TYPE,
    DATA_TYPE  USER_TAB_COLS.DATA_TYPE%TYPE,
    LENGTH     NUMBER(4),
    SCALE      NUMBER(2),
    DEF_VALUE  USER_TAB_COLS.data_default%TYPE, -- a LONG in U T C
    ID         NUMBER(1),
    FORCE_DEF  NUMBER(1),
    ACTIVE_PK NUMBER(1),
    NEW_COL_OLD_TABLE NUMBER(1),
    REPLACEMENT_KEY NUMBER(1)
    );

  TYPE PON_DICT_TABLE_TYPE IS TABLE OF PON_DICT_RECORD_TYPE;

  pdtab PON_DICT_TABLE_TYPE;

where you can see the definition of field DEF_VALUE is now an anchored type.

This was bizarre, I have to say. I would have expected it to fail in both contexts.

回复收藏 0 原文

~没有更多了~