Oracle common error diagnosis

The ORACLE tutorial you are looking at is :Oracle common error diagnosis. This kind of error of ORACLE is explained in detail in ORALCE's documentation, but the reasons and measures are not detailed. This article focuses on how to solve this kind of error.

1. ORA-12571, ORA-03113, ORA-03114, ORA-01041

Features: client (proxy or application server) has this kind of disconnect error

Reason: if it occurs once in a while, it may be due to network reasons or abnormal user termination. If it occurs frequently, it may be because the client side and the server side have different character sets.

Action: add 1 line to the server's protocol profile PROTOCOL.ORA if it appears occasionally

TCP. NODELAY = YES;

If it occurs frequently, it is due to the difference between the client and server character sets or network reasons.

The client's character set defined in registries: HKEY__LOCAL__MACHINE/SOFTWARE/ORACLE/NLS__LANG

Set TCPMAXDATARETRANSMITIONS=20 in the TCP parameter entry in the client registry.

2, ORA - 01000

Feature: the maximum number of cursors allowed for the session is reached

Reason: the maximum number of cursors allowed for the session is reached

Measures: there are two solutions:

(1) increase the number of OPEN_CURSORS in the initialization file INIT.ORA, 1 is generally greater than 200.

(2) at the application level, it is related to development tools, such as setting MAXOPEN_CURSORS, etc.

3, ORA - 01545

Feature: a rollback segment is not available

Reason :(1) when the rollback segment ONLINE is enabled, but the rollback segment is not available, such as OFFLINE of the table space where the rollback segment is located;

(2) when the rollback segment ONLINE is enabled, but the rollback segment has ONLINE. For example, the rollback segment is used twice. In a typical case such as OPS, the rollback segment cannot be public;

(3) when the rollback segment is deleted, there are active transactions in the rollback segment;

Action :(1) ensure that the rollback segment is available

(2) delete the specified rollback segment from the initialization file INIT.ORA parameter ROLLBACK)SEGMENTS.

(3) the table space where the rollback segment is located can be deleted to cancel the UNDO transaction

4, x ORA - 0165

Characteristics: table Spaces do not have enough space to allocate

Reason: the table space is full; Storage parameters are not reasonable, NEXT is too small; There is no continuous interval

Action: if the table space is full, add files for the table space; If the storage parameters are not reasonable, INITIAL and NEXT should be added. If there are no contiguous intervals, you need to merge the free table Spaces.

View space debris using DBA_FREE_SPACE

5, ORA - 01555

Feature: the current session cannot read the previous version of the data

Reasons: there are many reasons, the main reasons are as follows: rollback segment is too small, too little; Rollback segment conflict; Cross-commit (FETCH_ACROSS)

Measures: increase the number of rollback segments;

6, ORA - 04031

Characteristics: the Shared pool memory area is out of memory, or memory fragmentation

Reason: when trying to load a large package or execute a large stored procedure, the Shared pool does not have contiguous memory space.

Action: if there is not enough memory, increase it.

If it is memory fragmentation, alter system flush share_pool is executed

7, ORA - 04091

Features: trigger does not work properly

Reason: this error occurs when a row triggers a read or modification of a changed table (being modified, inserted).

Action: check the trigger script to ensure referential integrity

8. ORA-01242, ORA-01113

Features: media failure leads to database downtime

Reason: medium failure.

Measures: check for hardware faults; Modify the dbshut script to change the STARTUP command to:

Startup open recover

Alter database open