Interpreting Oracle Error Codes; Determining Oracle Object Errors

Resolving The Problem

To interpret an Oracle error code:

1. At the UNIX prompt, enter an oerr ora <Oracle error code>.
- oerr ora 1555

2. This returns the Oracle error, its cause, and action to correct.

Example:

01555, 00000, ?snapshot too old: rollback segment number %s with name \?%s\? too
small
// *Cause: rollback records needed by a reader for consistent read are
// overwritten by other writers
// *Action: Use larger rollback segments

DATABASE ERRORS WHICH NEEDS DBA INVOLVEMENT IMMEDIATELY
SQLCD 1547 Failed to extend
SQLCD 1631 Max extents on table exceeded
SQLCD 1632 Max extents on index exceeded
SQLCD 1653 Unable to extend table in tablespace
SQLCD 1654 Unable to extend index in tablespace
SQLCD 50 O/S error occurred while obtaining an enqueue.

To identify the tablespace if the Oracle error is 1653 or 1654 ?

Locate an associated line in the wms_alert.log in the form:

Oracle Err PK60512121.PR60512121 SqlCode 0 SQLCD -1654 SqlMessage
------------------------------------------------------------------ -------------------------------------------------------------------------------- ------------------------------
Use the below naming conventions to identify the associated index/table based on the error logged:

TABLES:
Tables have numbers starting with ?T074?.
e.g T074001
the last three digits uniquely identify each table. (001 in this example)

SYNONYMS:
Synonyms are defined for all tables. T074001, for example, has a synonym defined as WHSE_CODE.

PACKAGES/STORED PROCEDURES:
Stored procedures/packages are defined for each table . These use the following convention:
PK0010021
where PK indicates that this is a package/procedure. The next three digits indicate the related object.

INDEXES:
X0740011
Here X denotes that it is an index and the next 3 digits denote the object to which it is related to.

After determining the necessary information above from the log entries:

Query the Oracle system tables ALL_INDEXES and ALL_TABLES to identify the associated tablespace.
Query the Oracle system table DBA_DATA_FILES to determinethe files associated with the tablespace.