ORA-04091: table is mutating trigger

ORA-04091: table string.string is mutating, trigger/function may not see it

Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.

Action: Rewrite the trigger (or function) so it does not read that table.

ORACLE MUTATING TABLE PROBLEM

The mutating trigger error (ORA-04091) is a pretty common problem in Oracle that happens and frustrates many application developers and DBAs as the application developers bug them to find a solution. It happens when a trigger on a table tries to insert, update, or even select from the same table of whose trigger is being executed. Sometimes the inability to see the table causes standstill in the development.

The following explanation and the associated scripts attempt to present just one work-around for the problem. The work around may not be the best but rather does show that a work-around is possible and provides a solution that will be applicable in most cases. The approach is described by a case study.

SCENARIO

The mutating table problem comes when the trigger tries to select or modify any row of the same table. This situation comes when a table preserves some effective date.

To describe this method, the SALGRADE table will be used.

The table SALGRADE contains information on salary limits for each grade. The salary limits are also based on a time factor, i.e. the employees' salary is determined by checking which grade level was effective when they joined or reviewed, not necessarily the grade effective now.

So the table looks like this:

SQL> desc salgrade;

 Name                            Null?    Type

 ------------------------------- -------- ----

 GRADE                                    NUMBER

 LOSAL                                    NUMBER

 HISAL                                    NUMBER

 START_DATE                               DATE

 END_DATE                                 DATE

The table data looks like:

GRADE   LOSAL   HISAL   START_DATE       END_DATE

------- ------- ------- ----------       ---------

1       1000    2000    1-APR-94         3-AUG-95

1       1200    2200    3-AUG-95                   <---- Null

2       1500    3000    23-JUL-92        12-DEC-93

2       1600    3200    12-DEC-93        11-JAN-95

2       1800    3400    11-JAN-95                  <---- Null

This means the effective salary range of Grade 1 now is (1200-2200) not the employees who had review between 1-APR-94 to 3-AUG-95 will be in the range (1000-2000). This is a purely hypothetical scenario. The objective is to devise a trigger that does the following when a new record is inserted:

(1) Integrity checking for overlapping dates, i.e. the new record can't have a start date that is already covered.

(2) Update the record for the current grade to make the end_date equal to the start date of the new record (the new record's end_date must be null as that is the current record).

In both cases the table SALGRADE has to be selected and updated on the after insert row trigger on the same table. But the table will be mutating when the trigger fires and thus a run-time error will occur.

For the first requirement, consider the following trigger:

create or replace trigger taiudr_salgrade

after insert on salgrade

for each  row

declare

        hold_found      varchar2(1);

begin

        select 'Y' into hold_found

        from salgrade

        where grade = :new.grade

        and end_date is null

        and start_date > :new.start_date;

exception

        when NO_DATA_FOUND then

                raise_application_error(-20000,'Overlapping Dates');

end;

/

Although the trigger can be created with no errors, when a user tries to

insert into the table the following mutating table error is returned:

SQL> insert into salgrade values (2, 9000, 100000, '25-dec-95', null);

insert into salgrade values (2, 9000, 100000, '25-dec-93', null)

            *

ERROR at line 1:

ORA-04091: table SCOTT.SALGRADE is mutating, trigger/function may not see it

ORA-06512: at "SCOTT.TAIUDR_SALGRADE", line 4

ORA-04088: error during execution of trigger 'SCOTT.TAIUDR_SALGRADE'

SOLUTION

The following approach is another possibility for the task:

1. Create a package "SALGRADE_PKG" that contains PL/SQL tables for holding the

SALGRADE data. Here 3 tables are created - one for holding start_dates, one

for end_dates, and one for holding the change_flag that identifies the

updated row.

2. Create a BEFORE INSERT STATEMENT trigger that populates the PL/SQL table with the start dates, end_dates and changed_grades flag ('N'). Although this still executes a SELECT agaist the SALGRADE table, the mutating table restriction, applies to all triggers that use the FOR EACH ROW clause and this new trigger will be a STATEMENT or table level trigger.

3. Create an AFTER INSERT ROW trigger that compares the newly inserted row against this PL/SQL table not the Database table. This way the integrity check can be done. The same trigger should assign the new end_date value to the PL/SQL table and update the value of the flag to indicate that this

has to be changed.

4. Create a AFTER INSERT STATEMENT trigger to update the SALGRADE table with the values in the PL/SQL table after looking at the change flag.

All these programs can be created by the sources found below.

CODE:

Code to create test table and populate it with data:

drop table salgrade;

CREATE TABLE SALGRADE

      (GRADE NUMBER,

        LOSAL NUMBER,

        HISAL NUMBER,

        START_DATE DATE,

        END_DATE DATE);

INSERT INTO SALGRADE VALUES (1,1000,2000, '1-apr-94', '3-aug-95');

INSERT INTO SALGRADE VALUES (1,1200,2200, '3-aug-95', null);

INSERT INTO SALGRADE VALUES (2,1500,3000, '23-Jul-92', '12-dec-93');

INSERT INTO SALGRADE VALUES (2,1600,3200, '12-dec-93', '11-jan-95');

INSERT INTO SALGRADE VALUES (2,1800,3400, '11-jan-95', null);

Code for package STEP 1 above:

create or replace package salgrade_pkg as

type datetabtype        is table of date index by binary_integer;

type chartabtype        is table of char(1) index by binary_integer;

type rowidtabtype   is table of rowid  index by binary_integer;

start_date_tab          datetabtype;

end_date_tab            datetabtype;

rowid_tab                       rowidtabtype;

changed_grade           chartabtype;

start_date_tab_size     binary_integer;

end;

/

Code for before insert statement trigger STEP 2 above:

create or replace trigger tbiuds_salgrade

before insert on salgrade

declare

 hold_start_date  date;

 hold_end_date  date;

 hold_rowid   rowid;

 hold_grade   binary_integer;

 cursor start_date_cur is

  select rowid, grade, start_date

  from salgrade

  where end_date is null

  order by grade;

begin

 open start_date_cur;

 loop

  fetch start_date_cur into

   hold_rowid, hold_grade, hold_start_date;

  exit when start_date_cur%notfound;

  salgrade_pkg.start_date_tab(hold_grade) := hold_start_date;

  salgrade_pkg.end_date_tab(hold_grade) := hold_end_date;

  salgrade_pkg.rowid_tab(hold_grade) := hold_rowid;

  salgrade_pkg.changed_grade(hold_grade) := 'N';

 end loop;

 salgrade_pkg.start_date_tab_size := hold_grade;

 close start_date_cur;

end;

/

Code for after insert row trigger STEP 3 above:

create or replace trigger taiudr_salgrade

after insert on salgrade

for each  row

begin

 if (:new.grade <= salgrade_pkg.start_date_tab_size) then

  if salgrade_pkg.start_date_tab(:new.grade)

    > :new.start_date then

   raise_application_error(-20001,'Overlapping Dates');

  end if;

  salgrade_pkg.end_date_tab(:new.grade) := :new.start_date;

  salgrade_pkg.changed_grade(:new.grade) := 'Y';

 else

  salgrade_pkg.start_date_tab(:new.grade) := :new.start_date;

  salgrade_pkg.end_date_tab(:new.grade) := :new.end_date;

  salgrade_pkg.changed_grade(:new.grade) := 'N';

  salgrade_pkg.start_date_tab_size :=

    salgrade_pkg.start_date_tab_size + 1;

 end if;

end;

/

Code for after insert statement trigger STEP 4 above:

create or replace trigger taiuds_salgrade

after insert on salgrade

begin

        for i in 1..(salgrade_pkg.start_date_tab_size) loop

                if (salgrade_pkg.changed_grade(i) = 'Y') then

                        update salgrade

                        set end_date = salgrade_pkg.end_date_tab(i)

                        where rowid = salgrade_pkg.rowid_tab(i);

                end if;

        end loop;

end;

/

Once all the package and triggers are created, the same insert doesn't succeed but gives the user the a more appropriate error:

SQL> insert into salgrade values (2, 9000, 100000, '25-dec-93', null);

insert into salgrade values (2, 9000, 100000, '25-dec-93', null)

            *

ERROR at line 1:

ORA-20001: Overlapping Dates

ORA-06512: at "SCOTT.TAIUDR_SALGRADE", line 5

ORA-04088: error during execution of trigger 'JACK.TAIUDR_SALGRADE'

The above method is one possible solution to Mutating problem.

The following section provides more in depth details about this mutating problem and some other possible solutions and workarounds:

ORA-4091 Mutating Table Explanation and Workarounds

The purpose of this explanation is to illustrate to those customers who require one of the following functional capabilities whilst being able to maintain referential integrity among objects:

• Cascade Update
• Cascade Delete
• Cascade Insert

For cascade Update and Insert functions, using stored triggers and procedures will result in an ORA-04091 - "Table is mutating" error.

ORA-04091: "table %s.%s is mutating, trigger/function may not see it"
Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
Action: Rewrite the trigger (or function) so it does not read that table.

ORA-04091 is a very common error that occurs with triggers if triggers are not managed properly. A full understanding of triggers will help you avoid that error.

A mutating table is a table that is currently being modified by an update, delete, or insert statement. You will encounter the ORA-04091 error if you have a row trigger that reads or modifies the mutating table. For example, if your trigger contains a select statement or an update statement referencing the table it is triggering off of you will receive the error.

Another way that this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table the trigger is triggering from.

If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.

It must be stressed that this solution should ONLY be used to overcome DML restrictions imposed on triggers in order to maintain referential integrity. Whenever possible it is recommended that normal declarative integrity should be used to maintain foreign key integrity. Enforcing this integrity through stored triggers and procedures will have an effect on performance compared with declarative integrity.

For this solution to work correctly there must be no declarative integrity constraints between objects to enforce the foreign key constraint. The basic principle behind this solution is to suppress the validation checks performed as a result of inserting or updating a foreign key in the CHILD table triggered by a cascade Update or Insert. These checks would normally verify the existence of the new foreign key value in the PARENT record (by SELECTING from the parent table). The suppression of this check is only carried out as a direct result of Cascade Update or Delete, as we can be confident that this new value for the foreign key in the CHILD record does exist (i.e. a result of it being inserted or updated in the PARENT table). In all other circumstances no suppression will take place (e.g. when changing the DEPTNO of an employee or when inserting a new employee).

The following code illustrates how this is achieved for the cascade Update scenario, the code can easily be modified to add the other functionality and encapsulate it all within the same package. The EMP and DEPT table have been used, with the column EMP.DEPTNO in the EMP table referencing DEPT.DEPTNO in the DEPT table.



Conceptual Model

 -------------  1. Trigger on PARENT for cascade function

 |           |       SET global_variable = 'NO';

 |  PARENT   |           do cascade update;

 |           |       SET global_variable = 'YES';

 -------------     End;

       |                              -------------------

       |                              | GLOBAL_VARIABLE |

       |                              -------------------

      /|\

 ------------  2. Trigger on CHILD to maintain referential integrity

 |          |       CHECK global_variable;

 |  CHILD   |           IF global_variable = 'YES' THEN

 |          |              check existence of (fk) in PARENT;

 ------------           ELSE

                           null;

                  End;

EXAMPLE:

Oracle does not allow you the reading of a mutating table in a row trigger as it may lead to consistent data.

Attempt to do so, results in the following error:

ORA-04091 Table %s.%s is mutating, trigger/function may not see it

However, this operation can be performed within a statement trigger.

One way to work-around the mutating error within a row level trigger is to use a package PL/SQL table to store ROWIDs of updated records in a row trigger, and reprocess the updated records in a statement trigger.

Solution

Note that there are concurrency issues with this if more than one session tries to perform operations simultaneously. This is not intended as a total solution, but as the framework to help show one option.


The following is the sample work-around that used the EMP table.

Create the package to define the objects that will house the data.

create or replace package emp_pkg as
type emp_tab_type is table of rowid index by binary_integer;
emp_tab emp_tab_type;
emp_index binary_integer;
end emp_pkg;
/

Create the table level trigger to reset the index BEFORE any changes take place.

create or replace trigger emp_bef_stm_all before insert or update or delete on emp
begin
--Remember to reset the pl/sql table before each statement
emp_pkg.emp_index := 0;
end;
/

The following Row level trigger will fire for each row being modified and copy the RowID for each row to the EMP_TAB object defined within the package.

create or replace trigger emp_aft_row_all after insert or update or delete on emp
for each row
begin
--Store the rowid of updated record into global pl/sql table
emp_pkg.emp_index := emp_pkg.emp_index + 1;
emp_pkg.emp_tab(emp_pkg.emp_index) := :new.rowid;
end;
/

The following table level trigger will fire AFTER the others and will print out each RowID affected.

create or replace trigger emp_aft_stm_all after insert or update or delete on emp
begin
for i in 1 .. emp_pkg.emp_index loop
-- Re-process the updated records.
--There is no restriction here.
dbms_output.put_line(emp_pkg.emp_tab(i));
end loop;
emp_pkg.emp_index := 0;
end;
/

In order to see how this work from SQL*Plus, the output must be turned on.

SQL> SET SERVEROUTPUT ON

This first example, displays the RowIDs for Department 20 then issues a DELETE command. The trigger should fire and display the same RowIDs to confirm it is working. (The RowIDs will vary from machine to machine.)

SQL> SELECT ROWID, DEPTNO FROM EMP WHERE DEPTNO = 20;

ROWID DEPTNO
------------------ ----------
AAAM4iAAEAAAAG+AAA 20
AAAM4iAAEAAAAG+AAD 20
AAAM4iAAEAAAAG+AAH 20
AAAM4iAAEAAAAG+AAK 20
AAAM4iAAEAAAAG+AAM 20

SQL> DELETE EMP WHERE DEPTNO = 20;
AAAM4iAAEAAAAG+AAA
AAAM4iAAEAAAAG+AAD
AAAM4iAAEAAAAG+AAH
AAAM4iAAEAAAAG+AAK
AAAM4iAAEAAAAG+AAM

This second example, displays the RowIDs for Department 10 then issues an UPDATE command. The trigger should fire and display the same RowIDs to confirm it is working. (The RowIDs will vary from machine to machine.)

SQL> SELECT ROWID, DEPTNO FROM EMP WHERE DEPTNO=10;

ROWID DEPTNO
------------------ ----------
AAAM4iAAEAAAAG+AAG 10
AAAM4iAAEAAAAG+AAI 10
AAAM4iAAEAAAAG+AAN 10

SQL> UPDATE EMP SET JOB='Test' WHERE DEPTNO=10;
AAAM4iAAEAAAAG+AAG
AAAM4iAAEAAAAG+AAI
AAAM4iAAEAAAAG+AAN

3 rows updated.

To rollback the changes, issue the Rollback command.

SQL> ROLLBACK;

Rollback complete.

CONCLUSION :

The above methods are not submitted as solutions to all cases, however, those are provided as some of the possibilities for the Mutating problem. By following the above examples and customizing them to the individual needs , these Mutating problems can be avoided or fixed.

ORA-00936:missing expression

ORA-00936:missing expression

Cause:A required part of a clause or expression has been omitted. For example, a SELECT statement may have been entered without a list of columns or expressions or with an incomplete expression. This message is also issued in cases where a reserved word is misused, as in SELECT TABLE.

Action:Check the statement syntax and specify the missing component.

ORA-00936:missing expression

This Oracle error is mainly related to the SQL SELECT statements. One obvious reason is select column list is missing or expressions in the selected columns are incomplete.

Example:

One Simple example is

SELECT FROM EMPLOYEE; raise ORA-00936 error as the column list is missing..

This ORA-00936 may also caused with respective to the usage of the exponential operator “**” in the SQL statements. It may result the following errors:

ORA 936 missing expression
PLS-936
ORA 6550 line , column <>num:

ORA-00936 Exponential Operator Scenario:

Symptoms

Using the exponential operator '**' in a SQL statement results in ORA-936 or PLS-936 depending on whether the SQL statement is executed from SQLPLUS prompt or within a PL/SQL block.

SQL> select 2**2 from dual;
select 2**2 from dual
*
ERROR at line 1:
ORA-00936: missing expression

SQL> declare
2 x number := 2;
3 y number := 4;
4 z number;
5 begin
6 select x**y into z from dual;
7 end;
8 /
y number := 4;
*
ERROR at line 3:
ORA-06550: line 6, column 10:
PL/SQL: ORA-00936: missing expression
ORA-06550: line 6, column 1:
PL/SQL: SQL Statement ignored

Cause

'**' is a valid exponential character in PL/SQL and cannot be used in a SQL statement. The exponential operator '**' can be used in PL/SQL statements:

SQL> declare
2 x number := 2;
3 y number := 4;
4 z number;
5 begin
6 z := x**y;
7 end;
8 /

PL/SQL procedure successfully completed.

Fix

Use power() function instead of '**' in SQL statement'.

SQL> select power(2,2) from dual;

POWER(2,2)
----------
4

SQL> declare
2 x number := 2;
3 y number := 4;
4 z number;
5 begin
6 select power(x,y) into z from dual;
7 end;
8 /

PL/SQL procedure successfully completed.

Oracle Bugs related to ORA-00936 error:

There are also a few oracle bugs related to ORA-00936. Listed below are two bugs related to ORA-00936. You can find complete list of bugs at Oracle Metalink.

1) Bug#:4567818 base Bug#:4192148 (unpublished) on 9207

2) Bug#:4212516 (unpublished) on 10.1.0.4.0.

With these bugs, ORA-00936 error is thrown when the SELECT ON view fails.

Basically, ORA-00936 is thrown when a SQL view is created from "create or replace view MY_VIEW as select t.*,other_tab_col from tab t, other_tab". This creates a view definition that is incorrect in the DBA_VIEWS, thus throwing ORA-00936 and possible core dumps.

In order to fix the bugs and resolve ORA-00936, MetaLink offers these solutions for the appropriate version:

Fix for 9.2.0.7 :

Patch 4192148 is available for Solaris (64bit) and AIX5L Based Systems (64-bit).

Fix for 10.1.0.4 :

Patch 4212516 is available for most of the platforms.

ORA-00936 Example from Oracle Forum:

Question:

When trying o use the code below, I keep getting ORA-00936. What is the problem?

Dim sSQL As String = _
"UPDATE TBLICPMETHODS SET Frequency = @Frequency, " & _
"Calibration = @Calibration, QCValue = @QCVAlue" & _
" WHERE Frequency = '" & Session("EditFrequency") & "' AND MethodNumber = " & ddlMethods.SelectedValue

Dim connConnection As New OleDbConnection(Application("ERSconnectionStr"))

connConnection.Open()

Dim cmdCommand As New OleDbCommand(sSQL, connConnection)

Dim prmFrequency As OleDbParameter = _
New OleDbParameter("@Frequency", OleDbType.VarChar, 75)
prmFrequency.Value = sFrequency
cmdCommand.Parameters.Add(prmFrequency)

Dim prmCalibration As OleDbParameter = _
New OleDbParameter("@Calibration", OleDbType.Double, 75)
prmCalibration.Value = CDbl(sCalibration)
cmdCommand.Parameters.Add(prmCalibration)

Dim prmQCValue As OleDbParameter = _
New OleDbParameter("@QCValue", OleDbType.Double, 75)
prmQCValue.Value = CDbl(sQCValue)

cmdCommand.Parameters.Add(prmQCValue)
' MessageBox.show(cmdCommand.CommandText + ":Frequency=" + e.Item.Cells(2).Text + ":Calibration=" + e.Item.Cells(3).Text + ":QCValue=" + e.Item.Cells(4).Text)
Try
cmdCommand.ExecuteNonQuery()
Catch ex As OleDbException
MessageBox.show(ex.Message)
End Try

Answer:

What you are forgetting is that in OLEDB you cannot use named parameters. This being, your use of @parameter is throwing ORA-00936. Trying taking out the @ and using a question mark instead to clear up ORA-00936. Here is an example, instead of:

"UPDATE TBLICPMETHODS SET Frequency = @Frequency, " & _
"Calibration = @Calibration, QCValue = @QCVAlue"

use:

"UPDATE TBLICPMETHODS SET Frequency = ?, " & _
"Calibration = ?, QCValue = ?"

- By Mendus

ORA-00604: error occurred at recursive SQL level string

ORA-00604: error occurred at recursive SQL level string

Cause: An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables).

Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Support.

ORA-00604 error can occur in so many different contexts. The following is one such scenario

Connecting as DB User gives ORA-00604, ORA-01986: OPTIMIZER_GOAL is Obsolete and ORA-6512 Errors

This error applies to Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.3
This problem can occur on any platform

When you try to log-in to the database as a particular user, you get disconnected with the error : ORA-01986

SQL> conn dvamin/dvamin

ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-01986: OPTIMIZER_GOAL is obsolete

ORA-06512: at line 2

Warning: You are no longer connected to ORACLE.

You check the init.ora / spfile and verify that you haven't set any parameter : OPTIMIZER_GOAL
still the database login errors out indicating that OPTIMIZER_GOAL is obsolete.
In the alert.log, you find :

Errors in file $ORACLE_BASE/admin/hrupg/udump/_ora_.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-01986: OPTIMIZER_GOAL is obsolete

ORA-06512: at line 2

Why this error is occurring ? Troubleshoot

Please check the tracefile : $ORACLE_BASE/admin/hrupg/udump/_ora_.trc
This shows :
-----------------------------------------------------------------------------------------------------------
*** SERVICE NAME:(SYS$USERS) 2006-11-21 08:20:41.909
*** SESSION ID:(104.17) 2006-11-21 08:20:41.909
Error in executing triggers on connect internal
*** 2006-11-21 08:20:41.911
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-01986: OPTIMIZER_GOAL is obsolete
ORA-06512: at line 2
-----------------------------------------------------------------------------------------------------------

You have configured some Database Login Triggers.
These triggers are trying to set a parameter that is obsolete in 10.2
Hence errors.

You might have configured a login trigger of the type :
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE OR REPLACE TRIGGER LoginTrigger AFTER LOGON ON xxxxxx.SCHEMA
BEGIN
execute immediate
'alter session set OPTIMIZER_GOAL=FIRST_ROWS';
...
...
END;
/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SIMPLE TEST-CASE :
+++++++++++++++++++++++++++++++++++++++++++++++++++++
#############################################################
SQL> create user dvamin identified by dvamin quota 5M on users;
User created.

SQL> grant connect to dvamin;
Grant succeeded.

SQL> CREATE OR REPLACE TRIGGER LoginTrigger AFTER LOGON ON dvamin.schema
2 BEGIN
3 execute immediate
4 'alter session set OPTIMIZER_GOAL=FIRST_ROWS';
5 END;
6 /
Trigger created.

SQL> conn dvamin/dvamin
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01986: OPTIMIZER_GOAL is obsolete
ORA-06512: at line 2
Warning: You are no longer connected to ORACLE.
#############################################################

What is the Solution?

(i) Log-in as a user with ADMINISTER DATABASE TRIGGER privilege
(ii) Drop this trigger.
(iii) Log in as the required user successfully.

SQL> conn / as sysdba

Connected.

SQL> drop trigger LoginTrigger;

Trigger dropped.

SQL> conn dvamin/dvamin

Connected.

Rewrite the trigger as necessary if required. Please refer to the Oracle Administrator's Guide 10g as available in OTN if needed.

- Mendus

ORA-00600: internal error code arguments:

ORA-00600: internal error code, arguments: [string], [string], [string], [string], [string], [string], [string], [string]

Cause: This is the generic internal error number for Oracle program exceptions. This indicates that a process has encountered an exceptional condition.

Action: Report as a bug - the first argument is the internal error number

Most of the Oracle database administrators face the ORA-00600 Internal Error one or other time and it is very intricate resolving this error. So what is this ORA-600 error anyway?

ORA-600 errors are raised from the kernel code of the Oracle RDBMS software when an internal inconsistency is detected or an unexpected condition is met. This situation is not necessarily a bug as it might be caused by problems with the Operating System, lack of resources, hardware failures, etc.

With the ORA-600 error comes a list of arguments in square brackets. The first of these arguments tells us from where in the code the error was caught and thus is the key information in identifying the problem. This argument is either a number or a character string. The remaining arguments are used to supply further information eg. values of internal variables etc.

Whenever an ORA-600 error is raised a trace file is generated in either USER_DUMP_DEST or BACKGROUND_DUMP_DEST depending on whether the error was caught in a user or a background process. The error is also written in the alert log and so is the name of the trace file. The trace file contains vital information about what led to the error condition.

The following are some in depth details about the ORA-00600 Oracle error

Error:  ORA 600

Text:   internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s]

-------------------------------------------------------------------------------

Cause:  This is the generic internal error number for Oracle program

        exceptions.    This indicates that a process has encountered an

        exceptional condition.

Action: Report as a bug - the first argument is the internal error number

*** Important: The notes below are for experienced users - See Note 22080.1

Explanation:

        Any ORA-600 error indicates Oracle has detected an internal 

        inconsistency or a problem which it doesnt know how best to 

        address. These are *NOT* necessarily bugs and can occur for

        reasons such as Operating System IO problems, running out of some

        resource etc..

Diagnosis:

        Oracle support need more information to determine what caused the

        error. The most useful items are:

        a) The alert log for the instance (and any other instances in

           a Parallel Server environment) 

           These should be in BACKGROUND_DUMP_DEST.

        b) The trace file for the failing session and any other

           relevant trace files.

        c) Information on what action was being performed.

        d) Whether the problem is reproducible or was a one off

           occurance.

        e) Anything that has recently been changed in the system

           environment. Eg: Has the OS been upgraded, Have any 

           database files just been restored, etc...

Articles:

        What information to collect for ORA 600                    

        ORA-600/ORA-7445 Troubleshooter   Note 153788.1

Where can you find more information about the ORA-600 error?

You can find further information on ORA-600 errors using MetaLink Search or by accessing the Oracle Internal Errors Technical Library (select "Top Tech Docs" in the main menu, locate the "Data Server" section. Here you will find the library dealing with Internal Errors).

Now you know this ORA-00600 error is more complex than any other Oracle error, so how to deal with these ORA-600 Internal Errors?

Unless you are able to identify the cause and possible fix for an ORA-600 error using the references mentioned below, it should be considered as a potentially serious issue and reported to Oracle Support for identification.

As mentioned in Note 146580.1 every ORA-600 error will generate a trace file. The name and location of the trace file is also written to the alert.log. To help Oracle Support determine what caused the ORA-600 you should open a TAR and supply the following information:

1. The database alert log located in BACKGROUND_DUMP_DEST (for Oracle Real Application Clusters supply the alert log for all instances in the environment).
2. The trace file mentioned in the alert log.
• In case of multiple occurrences of the same ORA-600 error (ie. identical first arguments) you should only supply the first 2 or 3 trace files but do not delete any of the remaining traces as they might be required at a later stage.
• In case of multiple different ORA-600 errors you should only supply the trace file from the first error. Oracle Support will request any other file that may be needed.
3. Recent changes to the system environment, eg. hardware modifications, operating system upgrades, restore of datafiles, power failures, etc.
4. Use RDA (Remote Diagnostic Agent) to collect important information:
   Note 314422.1 "Remote Diagnostic Agent (RDA) 4.0 - Overview"

The following is partial list of the ORA-00600 known bugs and issues. You can find complete list and details about these bugs at Oracle Metalink.

Partial list of ORA-00600 known bugs and issues:

1. An Oracle ORA-00600 [729] : indicates a memory leak in the UGA. Setting diagnostic events 10262 ignores this error, that is, no ORA-00600 is produced even if a memory leak is detected.
2. ORA-00600 [16365] : indicates a Half Duplex Violation. Shared servers and dispatchers cannot both be writing to the same virtual curcuit at the same time because they communicate with a half-duplex protocol (TTC protocol).
3. ORA-00600 [kghpih:ds] : and ORA-00600 [17175] may occur in oracle 9.2.0.6 when two processes work on the same library cache object. fixed in oracle 9.2.0.7.. Note:300604.1
4. An Oracle ORA-00600 [3020] : Bug.2322620 - RECOVERY HALTED ON PHYSICAL STANDBY WITH ORA-600 AND ORA-10564 Details: ORA-600 [3020] can occur on recovery of LOB data.Fixed in 8.1.7.5 PAtch available. This error occurred on our database recovery on the primary server.
5. ora-00600 [12333] : Most probably a network error that may have caused bad data to be received or the client application may have sent the wrong data in the network buffer.
6. Oracle ORA-00600 [12700] : Indicates corruption in index, table or the mapping between them. Upon this discovery, oracle marks the whole block as corrupt and users will not have normal access to the data from the corrupted block. This can also break referntial integrity and should be referred to oracle.
7. ORA-00600 [lxhcsn] : Shutdown may dump ORA-7445. Known Bug 2311529

Conclusion:

In many cases ORA-00600 error indicates an existing corruption in the oracle database OR it can be a indication of events leading to data or database corruption.

Oracle database administrators should take an ORA-00600 error very seriously, and check any known fixes available in the Oracle MetlaLink and raise tars if no known fixes and work with oracle support to resolve the ORA-600 issue.

ORA-12154: TNS:could not resolve the connect identifier specified - Oracle Error

ORA-12154: TNS:could not resolve the connect identifier specified - ORA-12154 Error/ Oracle Error

Cause: A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured. For example, if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository, or the repository could not be located or reached.

Action:

- If you are using local naming (TNSNAMES.ORA file):

o Make sure that "TNSNAMES" is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA)

o Verify that a TNSNAMES.ORA file exists and is in the proper directory and is accessible.

o Check that the net service name used as the connect identifier exists in the TNSNAMES.ORA file.

o Make sure there are no syntax errors anywhere in the TNSNAMES.ORA file. Look for unmatched parentheses or stray characters. Errors in a TNSNAMES.ORA file may make it unusable.

- If you are using directory naming:

o Verify that "LDAP" is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).

o Verify that the LDAP directory server is up and that it is accessible.

o Verify that the net service name or database name used as the connect identifier is configured in the directory.

o Verify that the default context being used is correct by specifying a fully qualified net service name or a full LDAP DN as the connect identifier

- If you are using easy connect naming:

o Verify that "EZCONNECT" is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).

o Make sure the host, port and service name specified are correct.

o Try enclosing the connect identifier in quote marks. See the Oracle Net Services Administrators Guide or the Oracle operating system specific guide for more information on naming.

This Oracle error ORA-12154 is thrown during the logon process to a database. This ORA-12154 error indicates that the communication software in Oracle ( SQL *Net ) did not recognize the host/service name specified in the connection parameters.

For a database name to be recognized, it must be resolved by Oracle Client. To resolve a name, Oracle client uses either a TNSNames.ora, A Directory Server or an Oracle Names Server.

ORA-12154: TNS:could not resolve service name - ORA-12154 Error / Oracle Error

If the cause for this Oracle error is Oracle Net could not locate the net service name specified in the tnsnames.ora configuration file the perform the following steps to resolve this ora error / tns error:

· Verify that a tnsnames.ora file exists.

· Verify that there are not multiple copies of the tnsnames.ora file.

· In the tnsnames.ora file, verify that the net service name specified in your connect string is mapped to a connect descriptor.

· Verify that there are no duplicate copies of the sqlnet.ora file.

· If you are using domain names, verify that your sqlnet.ora file contains a NAMES.DEFAULT_DOMAIN parameter. If this parameter does not exist, you must specify the domain name in your connect string.

· If you are not using domain names, and this parameter exists, delete it or disable it by commenting it out.

· If you are connecting from a login dialog box, verify that you are not placing an "@" symbol before your connect net service name.

· Activate client tracing and repeat the operation.

If the cause for the ORA-12514 Oracle error is " Oracle Net could not locate the database service name or net service name specified in the directory server" then perform the following steps for resolving this ora error or tns error:

· Verify that the database service or net service name entry exists in the directory that this computer was configured to use.

· Verify that the sqlnet.ora file includes the following entry:

· NAMES.DIRECTORY_PATH=(ldap, other_naming_methods)