Oracle Error Reference - Common ORA- Errors & Solutions

Oracle Error Reference - Common ORA- Errors & Solutions

This comprehensive reference provides detailed explanations and solutions for 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

Data & Constraint Errors

• ORA-01403: No Data Found - SELECT INTO exceptions
• ORA-01422: Exact Fetch Returns More - Multiple row returns
• ORA-00001: Unique Constraint Violated - Duplicate key violations
• ORA-02291: Integrity Constraint Violated - Foreign key issues

Security & Access Errors

• ORA-01017: Invalid Username/Password - Authentication failures
• ORA-28000: Account Locked - Locked user accounts
• ORA-01031: Insufficient Privileges - Permission denied errors
• ORA-00942: Table or View Does Not Exist - Object access issues

📊 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

Memory-Related Errors

1. Analyze memory usage

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

2. Adjust memory parameters

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

Space-Related Errors

1. 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;

2. Add space or reorganize

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

Connection-Related Errors

1. Verify listener status

   lsnrctl status
   lsnrctl services

2. 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

1. Regular Maintenance

   • Monitor space usage daily
   • Review alert logs hourly
   • Analyze AWR reports weekly
   • Update statistics regularly

2. Capacity Planning

   • Set up space alerts at 80% full
   • Monitor connection pool usage
   • Track memory consumption trends
   • Plan for growth

3. 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.