Oracle Error Reference - Common ORA- Errors & Solutions

This comprehensive reference provides detailed explanations and solutions for 60+ common Oracle database errors. Each error entry includes root causes, diagnostic steps, and proven resolution methods tested in production environments.

🚨 Critical Production Errors

System & Instance Errors

ORA-00600: Internal Error - Oracle internal errors requiring support
ORA-07445: Exception Encountered - Core dump errors and crashes
ORA-04031: Unable to Allocate Memory - Shared pool memory issues
ORA-04030: Out of Process Memory - PGA memory exhaustion

Connection & Network Errors

ORA-12154: TNS Could Not Resolve - TNS name resolution failures
ORA-12514: TNS Listener Does Not Know - Service registration issues
ORA-12541: TNS No Listener - Listener connectivity problems
ORA-12519: No Appropriate Service Handler - Connection pool exhaustion

Space & Storage Errors

ORA-01653: Unable to Extend Table - Tablespace space issues
ORA-01654: Unable to Extend Index - Index tablespace problems
ORA-01688: Unable to Extend Table Partition - Partition space errors
ORA-01652: Unable to Extend Temp Segment - Temporary tablespace full

Performance & Resource Errors

ORA-01555: Snapshot Too Old - Undo retention issues
ORA-00054: Resource Busy - Lock conflicts and busy resources
ORA-00060: Deadlock Detected - Transaction deadlocks
ORA-01000: Maximum Open Cursors - Cursor leak problems

PL/SQL & Development Errors

ORA-06512: At Line - PL/SQL error stack traces and debugging

📊 Error Categories by Severity

🔴 Critical (Immediate Action Required)

Error Code	Description	Business Impact
ORA-00600	Internal error	Database instability
ORA-07445	Exception/core dump	Instance crash risk
ORA-04031	Shared pool exhausted	Connection failures
ORA-01652	Temp space full	Query failures

🟡 High (Urgent Resolution Needed)

Error Code	Description	Business Impact
ORA-01555	Snapshot too old	Report failures
ORA-00054	Resource busy	Transaction delays
ORA-12519	No service handler	New connections blocked
ORA-01653	Cannot extend table	Insert/update failures

🟢 Medium (Plan Resolution)

Error Code	Description	Business Impact
ORA-01000	Max cursors exceeded	Application errors
ORA-00060	Deadlock detected	Transaction rollback
ORA-00001	Unique constraint	Data integrity
ORA-00942	Object not found	Query failures

🔍 Quick Diagnosis Tools

Error Pattern Analysis

-- Recent errors from alert log
SELECT originating_timestamp, message_text
FROM x$dbgalertext
WHERE message_text LIKE 'ORA-%'
  AND originating_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY
ORDER BY originating_timestamp DESC;

-- Session errors
SELECT sid, serial#, username, sql_id,
       event, seconds_in_wait, state
FROM v$session
WHERE status = 'ACTIVE'
  AND wait_class != 'Idle';

-- Error frequency analysis
SELECT SUBSTR(message_text, 1, 9) as error_code,
       COUNT(*) as occurrences,
       MAX(originating_timestamp) as last_occurred
FROM x$dbgalertext
WHERE message_text LIKE 'ORA-%'
  AND originating_timestamp > SYSTIMESTAMP - INTERVAL '7' DAY
GROUP BY SUBSTR(message_text, 1, 9)
ORDER BY occurrences DESC;

Automated Error Monitoring

-- Create error tracking table
CREATE TABLE dba_error_log (
  error_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP,
  error_code VARCHAR2(10),
  error_message VARCHAR2(4000),
  username VARCHAR2(30),
  program VARCHAR2(100),
  sql_id VARCHAR2(13),
  alert_sent CHAR(1) DEFAULT 'N'
);

-- Error capture trigger (example)
CREATE OR REPLACE TRIGGER capture_errors
AFTER SERVERERROR ON DATABASE
BEGIN
  IF ora_is_servererror(1555) OR
     ora_is_servererror(4031) OR
     ora_is_servererror(600) THEN
    INSERT INTO dba_error_log (
      error_code, error_message, username, program
    ) VALUES (
      'ORA-' || ora_server_error(1),
      ora_server_error_msg(1),
      ora_login_user,
      ora_client_ip_address
    );
    COMMIT;
  END IF;
END;
/

🛠️ Generic Resolution Strategies

Analyze memory usage

SELECT * FROM v$sgastat WHERE pool = 'shared pool' ORDER BY bytes DESC;
SELECT * FROM v$process_memory ORDER BY allocated DESC;

Adjust memory parameters

ALTER SYSTEM SET shared_pool_size = 2G SCOPE=BOTH;
ALTER SYSTEM SET pga_aggregate_target = 4G SCOPE=BOTH;

Check tablespace usage

SELECT tablespace_name,
       ROUND(used_space * 8192 / 1024 / 1024, 2) AS used_mb,
       ROUND(tablespace_size * 8192 / 1024 / 1024, 2) AS total_mb,
       ROUND(used_percent, 2) AS used_percent
FROM dba_tablespace_usage_metrics
ORDER BY used_percent DESC;

Add space or reorganize

ALTER TABLESPACE users ADD DATAFILE '/u01/oradata/users02.dbf' SIZE 10G;
ALTER TABLE large_table MOVE COMPRESS;

Verify listener status
Terminal window
```
lsnrctl status
lsnrctl services
```

Check connection limits

SELECT resource_name, current_utilization, max_utilization, limit_value
FROM v$resource_limit
WHERE resource_name IN ('processes', 'sessions');

📈 Proactive Error Prevention

Monitoring Scripts

-- Space monitoring
CREATE OR REPLACE PROCEDURE check_tablespace_usage AS
BEGIN
  FOR ts IN (
    SELECT tablespace_name, used_percent
    FROM dba_tablespace_usage_metrics
    WHERE used_percent > 85
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('WARNING: ' || ts.tablespace_name ||
                        ' is ' || ts.used_percent || '% full');
  END LOOP;
END;
/

-- Memory monitoring
CREATE OR REPLACE PROCEDURE check_memory_usage AS
  v_shared_pool_free NUMBER;
BEGIN
  SELECT bytes INTO v_shared_pool_free
  FROM v$sgastat
  WHERE pool = 'shared pool' AND name = 'free memory';

  IF v_shared_pool_free < 100*1024*1024 THEN -- Less than 100MB
    DBMS_OUTPUT.PUT_LINE('CRITICAL: Shared pool free memory below 100MB');
  END IF;
END;
/

Best Practices for Error Prevention

Regular Maintenance
- Monitor space usage daily
- Review alert logs hourly
- Analyze AWR reports weekly
- Update statistics regularly
Capacity Planning
- Set up space alerts at 80% full
- Monitor connection pool usage
- Track memory consumption trends
- Plan for growth
Configuration Standards
- Set appropriate initialization parameters
- Configure automatic memory management
- Enable resumable space allocation
- Implement resource manager

🔗 Error Resolution Resources

Oracle Support Resources

My Oracle Support - Official knowledge base and patches
Oracle Documentation - Error message reference
Oracle Community - Peer support and discussions

Diagnostic Tools

ADRCI - Automatic Diagnostic Repository analysis
AWR Reports - Performance analysis around errors
Alert Log - Chronological error history
Trace Files - Detailed error diagnostics

Emergency Contacts

Oracle Support - For ORA-00600 and ORA-07445 errors
DBA Team - Internal escalation procedures
Management - Business impact assessment

📚 Using This Reference

Each error page includes:

Error explanation - What the error means
Common causes - Why it occurs
Diagnostic queries - How to investigate
Resolution steps - How to fix it
Prevention tips - How to avoid recurrence
Related errors - Similar issues to check

Navigate to specific error pages for detailed resolution guides tailored to each error scenario.