Tuesday, December 16, 2014

OERR utility in Oracle installed on Unix

 OERR utility in Oracle installed on Unix
Many Oracle products on UNIX have a utility called OERR that can be used to aid error investigation. The OERR utility is not documented so many new to Oracle on UNIX may not be aware of its existence.

The OERR utility is a shell script that is located in the ORACLE_HOME/bin directory. OERR requires the ORACLE_HOME to be set and two parameters when called: the facility which is the three to four letter code to the left of dash and the error number.
1
2
3
4
5
6
7
8
9
[oracle@prod ~]$ oerr
Usage: oerr facility error
 
Facility is identified by the prefix string in the error message.
For example, if you get ORA-7300, "ora" is the facility and "7300"
is the error.  So you should type "oerr ora 7300".
 
If you get LCD-111, type "oerr lcd 111", and so on.
[oracle@prod ~]$
OERR will extract the error description, possible cause and action steps the message text files. Below are some examples.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[oracle@prod ~]$ oerr ora 3113
03113, 00000, "end-of-file on communication channel"
// *Cause: The connection between Client and Server process was broken.
// *Action: There was a communication error that requires further investigation.
//          First, check for network problems and review the SQL*Net setup.
//          Also, look in the alert.log file for any errors. Finally, test to
//          see whether the server process is dead and whether a trace file
//          was generated at failure time.
[oracle@prod ~]$
 
[oracle@prod ~]$ oerr tns 03505
03505, 00000, "Failed to resolve name"
// *Cause:  The service name you provided could not be found in TNSNAMES.ORA,
//          an Oracle Names server, or a native naming service.
// *Action: Verify that you entered the service name correctly.  You may need
//          to ensure that the name was entered correctly into the network
//          configuration.
[oracle@prod ~]$
In many cases the information provided by OERR can set you down the correct path to problem resolution. Sometimes OERRmay not provide cause or action information.
1
2
3
4
5
[oracle@prod mesg]$ oerr ora 00576
00576, 00000, "in-band break protocol error"
// *Cause:
// *Action:
[oracle@prod mesg]$
If you are unfamiliar with the error OERR can provide a quick coming up to speed on identifying the problem to assist in resolution.
This section of code have been taken from the oracledistilled.com
 

Saturday, December 13, 2014

Compound Triggers , Avoid Mutating-Table Error

Using Compound Triggers to Avoid Mutating-Table Error

You can use compound triggers to avoid the mutating-table error (ORA-04091) described in Trigger Restrictions on Mutating Tables.
Scenario: A business rule states that an employee's salary increase must not exceed 10% of the average salary for the employee's department. This rule must be enforced by a trigger.
Solution: Define a compound trigger on updates of the table hr.employees, as in Example 9-4. The state variables are initialized each time the trigger fires (even when the triggering statement is interrupted and restarted).
Example 9-4 Compound Trigger that Avoids Mutating-Table Error
CREATE OR REPLACE TRIGGER Check_Employee_Salary_Raise
  FOR UPDATE OF Salary ON Employees
COMPOUND TRIGGER
  Ten_Percent                 CONSTANT NUMBER := 0.1;
  TYPE Salaries_t             IS TABLE OF Employees.Salary%TYPE;
  Avg_Salaries                Salaries_t;
  TYPE Department_IDs_t       IS TABLE OF Employees.Department_ID%TYPE;
  Department_IDs              Department_IDs_t;

  TYPE Department_Salaries_t  IS TABLE OF Employees.Salary%TYPE
                                INDEX BY VARCHAR2(80);
  Department_Avg_Salaries     Department_Salaries_t;

  BEFORE STATEMENT IS
  BEGIN
    SELECT               AVG(e.Salary), NVL(e.Department_ID, -1)
      BULK COLLECT INTO  Avg_Salaries, Department_IDs
      FROM               Employees e
      GROUP BY           e.Department_ID;
    FOR j IN 1..Department_IDs.COUNT() LOOP
      Department_Avg_Salaries(Department_IDs(j)) := Avg_Salaries(j);
    END LOOP;
  END BEFORE STATEMENT;

  AFTER EACH ROW IS
  BEGIN
    IF :NEW.Salary - :Old.Salary >
      Ten_Percent*Department_Avg_Salaries(:NEW.Department_ID)
    THEN
      Raise_Application_Error(-20000, 'Raise too big');
    END IF;
  END AFTER EACH ROW;
END Check_Employee_Salary_Raise;

Mutating Oracle triggers

Avoiding Mutating triggers

The insert to the child table caused the foreign key to validate the data on the parent (which fired the trigger) causing the insert of the child table to result in a mutating table error on the parent table.
The Oracle mutating trigger error occurs when a trigger references the table that owns the trigger, resulting in the "ORA-04091: table name is mutating, trigger/function may not see it." message.
  • Don't use triggers - The best way to avoid the mutating table error is not to use triggers.  While the object-oriented Oracle provides "methods" that are associated with tables, most savvy PL/SQL developers avoid triggers unless absolutely necessary.
     
  • Use an "after" or "instead of" trigger - If you must use a trigger, it's best to avoid the mutating table error by using an "after" trigger, to avoid the currency issues associated with a mutating table.  For example, using a trigger ":after update on xxx", the original update has completed and the table will not be mutating.
     
  • Re-work the trigger syntax - Dr. Hall has some great notes on mutating table errors, and offers other ways to avoid mutating tables with a combination of row-level and statement-level triggers.
     
  • Use autonomous transactions - You can avoid the mutating table error by marking your trigger as an autonomous transaction, making it independent from the table that calls the procedure.
At the end of the day, the mutating table error is usually the result of a poor application design and mutating triggers should be avoided whenever possible.
Steve Callan has these notes on the ORA-04091 error:
"Here is a problem many developers run into: ORA-04091 table owner.table_name is mutating, trigger/function may not see it. In many cases, the cause of this error is due to code within a trigger that looks at or touches the data within the table the trigger is being called or invoked from.
The reason Oracle raises this error is related to one of Oracle's primary strengths as a relational database management system. The particular strength in question here is that of having a read consistent view of data.

It is worthwhile to note that this ORA-04091 error occurs not only in the "pure" database development environment, but also in the Oracle tools type of development environment such as Oracle SQL*Forms.

Another solution relies on using an INSTEAD-OF trigger instead of the trigger you meant to use when you received the error. Another solution is actually more of a preventative measure, namely, using the right type of trigger for the task at hand.
Perhaps the greatest strength or utility of an INSTEAD-OF trigger is its ability to update what would normally appear to be non-updateable views. Simple views (pretty much based on a single base table) generally are inherently updateable via DML statements issued against the view.
However, when a view becomes more complex (multiple tables or views used in various join conditions to create the new single view), there is a good chance that many columns, as referenced by the view, lose their "updateable-ness."
So, being the data dictionary view/table name trivia wizard that you are, you know to query the XXX_UPDATABLE_COLUMNS views, substituting USER, ALL or DBA for XXX as applicable.

There are exceptions to this rule about views being inherently updateable. The exceptions (or restrictions) include views that use aggregate functions; group functions; use of the DISTINCT keyword; use of GROUP BY, CONNECT BY or START WITH clauses; and use of some joins. In many cases, use of the INSTEAD-OF trigger feature allows you to work around these restrictions.

INSTEAD-OF triggers are also useful for Forms developers because forms are commonly based on views. The INSTEAD-OF trigger, being a "real" trigger, and not a true form trigger, is stored on the server." 

The above contents are being taken from
http://www.dba-oracle.com/t_avoiding_mutating_table_error.htm 

Wednesday, August 6, 2014

Check the Trace File from Oracle 10G/11G/12c Database.

Check the Trace File from Oracle 10G/11G/12c Database.

SELECT value
FROM   v$diag_info
WHERE  name = 'Default Trace File';