Intro
Every Oracle DBA knows the 3am page. The alert log is screaming, users are getting errors, and somewhere in a stack trace is an ORA code you've seen before — but never quite at this severity, never in quite this configuration. This post covers the 10 ORA errors that cause the most pain in Oracle E-Business Suite production environments: what they mean, why EBS specifically amplifies each one, the diagnostic SQL you would run to confirm it, and how TuneVault's automated health check surfaces all of them before they become incidents.
ORA-01555: Snapshot Too Old
What it means: Oracle needs to reconstruct a consistent read of data as it existed when your query started. To do that, it reaches back into the undo tablespace for the "before image" of changed rows — but the undo data has already been overwritten. Oracle has no way to give you a consistent snapshot, so it aborts.
Why EBS makes it worse: EBS runs dozens of concurrent processes simultaneously — AP Invoice Import, GL posting, Inventory transactions, Workflow background engines, OPP (Output Post Processor), and more. Each of these generates a high volume of undo. When a long-running report (say, an AR Aging report or a custom extract) starts, the undo it needs is being actively overwritten by those concurrent programs. This is not rare in EBS — it's Tuesday morning.
The diagnostic SQL:
-- Check UNDO_RETENTION and tablespace sizing
SELECT d.tablespace_name,
d.bytes / 1024 / 1024 AS mb,
p.value AS undo_retention_seconds
FROM dba_data_files d
JOIN v$parameter p ON p.name = 'undo_retention'
WHERE d.tablespace_name = (
SELECT value FROM v$parameter WHERE name = 'undo_tablespace'
);How to fix it: Increase UNDO_RETENTION (start at 900–1800 seconds for EBS), add undo datafiles, and schedule long-running reports during low-activity windows.
How TuneVault catches it: TuneVault's health check queries v$undostat to calculate undo generation rate vs. retention and flags mismatches before they surface as ORA-01555 under load.
ORA-04031: Unable to Allocate Shared Memory
What it means: Oracle's shared pool (part of the SGA) has run out of contiguous free memory. It can't load a PL/SQL package, parse a new SQL statement, or complete an internal operation.
Why EBS makes it worse: EBS has hundreds of large PL/SQL packages: FND_STANDARD, AP_PKG, PO_DOCUMENT_ACTIONS_PVT, and dozens more. When the Concurrent Manager spins up worker processes, each one needs to load these packages into memory. If they're not pinned, Oracle evicts them under memory pressure and reloads them — fragmentation compounds until there's no contiguous free block large enough.
The diagnostic SQL:
-- Identify free memory and fragmentation in the shared pool
SELECT pool,
name,
bytes / 1024 / 1024 AS mb
FROM v$sgastat
WHERE pool IN ('shared pool', 'large pool')
ORDER BY bytes DESC;How to fix it: Pin the most-used EBS packages with DBMS_SHARED_POOL.KEEP. Increase SHARED_POOL_SIZE in 200–300 MB increments.
How TuneVault catches it: TuneVault checks v$sgastat for fragmentation and library cache miss rates — both precursors to ORA-04031 before it fires.
ORA-01652: Unable to Extend Temp Segment
What it means: Oracle tried to allocate space in the temporary tablespace (TEMP) for a sort, hash join, or global temporary table — and there wasn't enough room.
Why EBS makes it worse: EBS reports are notorious for large sorts. Discoverer reports, XML Publisher extracts, and custom PL/SQL jobs that do ORDER BY on multi-million-row tables all hammer TEMP. Multiple concurrent programs hitting large sorts at the same time can exhaust even a generously-sized TEMP tablespace in minutes.
The diagnostic SQL:
-- Check TEMP tablespace usage right now
SELECT tablespace_name,
total_blocks,
used_blocks,
free_blocks,
ROUND(used_blocks / total_blocks * 100, 1) AS pct_used
FROM v$sort_segment
ORDER BY tablespace_name;How to fix it: Add a TEMP datafile. Review which sessions are consuming the most temp space (v$tempseg_usage), identify the SQL, and tune it.
How TuneVault catches it: TuneVault tracks TEMP utilization trends and flags sessions with runaway sort activity.
ORA-00060: Deadlock Detected
What it means: Two sessions are each waiting on a lock held by the other. Oracle detects the cycle, picks one victim, and kills it with ORA-00060. The victim's transaction rolls back; the other session proceeds.
Why EBS makes it worse: EBS modules are tightly coupled. AP Payments, GL Posting, and Inventory Transactions all touch the same underlying tables (GL_JE_LINES, MTL_TRANSACTION_ACCOUNTS, etc.). When custom code and Oracle standard code both try to lock the same rows in different orders, deadlocks happen.
The diagnostic SQL:
-- Find current blocking sessions and what they are waiting on
SELECT w.sid AS waiting_sid,
w.serial#,
h.sid AS holding_sid,
h.serial#,
w.sql_id AS waiting_sql,
o.object_name AS locked_object
FROM v$lock h
JOIN v$lock w ON h.id1 = w.id1 AND h.id2 = w.id2
JOIN dba_objects o ON h.id1 = o.object_id
JOIN v$session s ON h.sid = s.sid
WHERE h.block = 1
AND w.request > 0
ORDER BY h.sid;How to fix it: Find the ORA-00060 trace file in $ORACLE_BASE/diag/rdbms, identify the two SQL statements, and fix the access order in the custom code conflicting with EBS standard processing.
How TuneVault catches it: TuneVault monitors v$lock for blocking chains and deadlock-prone patterns.
ORA-12541: TNS No Listener
What it means: The Oracle listener isn't running on the expected host/port. Applications can't establish new connections.
Why EBS makes it worse: EBS uses multiple connection points — the database listener, the apps-tier TNS configuration, and sometimes Oracle Connection Manager (CMAN). A listener crash is immediately catastrophic: all users lose connectivity, Concurrent Manager jobs fail.
How to fix it: Restart the listener. If it keeps dying, check the listener log for the cause. In EBS, verify tnsnames.ora and sqlnet.ora are consistent across all app tier nodes after any AutoConfig run.
How TuneVault catches it: TuneVault validates TNS connectivity and listener registration as part of every health check.
ORA-00257: Archiver Error
What it means: The archiver process (ARCH) can't write archived redo logs — usually because the archive destination is full. Oracle suspends all database writes. The database is still up, but nothing commits.
Why EBS makes it worse: EBS writes a massive volume of redo. An AP Payment run, a GL Period Close, or a large-scale Inventory transaction batch generates gigabytes of redo in minutes. When ARCH hangs, every user session that tries to commit sits frozen.
The diagnostic SQL:
-- Check archive destination status and usage
SELECT dest_name,
target,
archiver,
status,
error
FROM v$archive_dest
WHERE status != 'INACTIVE';How to fix it: Free up space in the archive destination immediately (delete old archives after confirming RMAN backup), or add a new archive destination.
How TuneVault catches it: TuneVault monitors archive destination free space and ARCH process status.
ORA-01017: Invalid Username/Password
What it means: A connection attempt failed due to incorrect credentials. In EBS, this often means something deeper — EBS stores database connection credentials for APPS, APPLSYS, and module schemas internally. When a DBA resets a password, AutoConfig must be re-run to propagate the change.
The diagnostic SQL:
-- Check password expiration status for EBS schemas
SELECT username,
account_status,
expiry_date,
lock_date
FROM dba_users
WHERE username IN ('APPS', 'APPLSYS', 'SYSTEM', 'DBSNMP')
ORDER BY expiry_date;How to fix it: Reset the password, run AutoConfig on all app tiers, and bounce the services. Set PASSWORD_LIFE_TIME = UNLIMITED for EBS application schemas (Oracle's own recommendation for EBS).
How TuneVault catches it: TuneVault checks all EBS schema password expiration dates and flags accounts expiring within 30 days.
ORA-04030: Out of Process Memory
What it means: The server process ran out of OS-level memory (PGA or virtual memory). Unlike ORA-04031 (SGA), this is about memory allocated to the individual session's process.
Why EBS makes it worse: EBS concurrent programs are memory-hungry. A poorly written PL/SQL program that builds large collections in memory (nested tables, associative arrays with millions of rows), combined with dozens of concurrent sessions, can exhaust the PGA allocation or hit OS ulimits.
The diagnostic SQL:
-- Check PGA usage per session
SELECT s.sid,
s.serial#,
s.username,
s.program,
p.pga_alloc_mem / 1024 / 1024 AS pga_alloc_mb,
p.pga_max_mem / 1024 / 1024 AS pga_max_mb
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE p.pga_alloc_mem > 500 1024 1024
ORDER BY p.pga_alloc_mem DESC;How to fix it: Identify the offending concurrent program, review its PL/SQL for bulk collection abuse, and refactor to use BULK COLLECT ... LIMIT with batch processing.
How TuneVault catches it: TuneVault monitors PGA allocation vs. target and flags sessions with runaway memory growth.
ORA-12154: TNS Could Not Resolve Service Name
What it means: Oracle can't find the service name in tnsnames.ora. The connection fails before it even reaches the database.
Why EBS makes it worse: EBS has a complex multi-tier architecture — web tier, app tier, database tier — each with its own TNS configuration. When AutoConfig runs after a patch or configuration change, it regenerates tnsnames.ora. If there's a misconfiguration in the context file, or if a node's AutoConfig fails partway through, you end up with inconsistent TNS across nodes.
How to fix it: Verify tnsnames.ora is consistent across all app tier nodes. After any EBS patch, re-run AutoConfig on all nodes and verify.
How TuneVault catches it: TuneVault validates TNS resolution for all configured database service names as part of the connectivity health check.
ORA-01403: No Data Found
What it means: A SELECT INTO statement returned zero rows. In EBS, this often surfaces as a missing setup row — a required profile option value, a deleted lookup code, or a workflow transition that references a non-existent agent.
The diagnostic SQL:
-- Check for missing critical profile option values
SELECT fpo.profile_option_name,
fpov.level_id,
fpov.profile_option_value
FROM fnd_profile_options fpo
LEFT JOIN fnd_profile_option_values fpov
ON fpo.profile_option_id = fpov.profile_option_id
AND fpov.level_id = 10001
WHERE fpo.profile_option_name IN (
'FND_PRINTER',
'CONC_COPIES',
'CONC_PP_ENABLED'
)
ORDER BY fpo.profile_option_name;How to fix it: Read the full stack trace from the concurrent request log to identify which SELECT INTO failed and on which table. Find the missing setup row and insert it.
How TuneVault catches it: TuneVault's EBS-specific checks validate critical setup data — profile options, Concurrent Manager queue configuration, OPP settings — and flags missing or invalid values before they cause runtime failures.
The Common Thread
Every one of these errors follows the same pattern: they're detectable before they become incidents. The undo tablespace was trending toward exhaustion for days before ORA-01555 fired. The shared pool fragmentation was growing for hours before ORA-04031 killed the Concurrent Manager. The APPS password had been expiring for 30 days before the credential error took down the app tier at midnight.
Manual monitoring catches these if someone checks the right query at the right time. TuneVault's health check runs 94 automated checks across all these vectors — every time you trigger it — and surfaces findings with the exact diagnostic context and remediation steps. You don't need to remember to check. You need to run the health check.
Ready to See What's Waiting in Your Oracle EBS Database?
TuneVault runs a full diagnostic — including checks for every error in this list — in minutes. No agents installed, no inbound firewall rules, credentials stay on your side.
Not sure yet? See what the report looks like before you connect anything.