100+ most common Oracle ORA errors encountered in real-world DBA environments — with meanings and root causes.
Meaning: An INSERT or UPDATE attempted to store a duplicate value in a UNIQUE or PRIMARY KEY constrained column.
Root Cause: Duplicate data being inserted; application logic bug or missing deduplication check.
Meaning: The database has reached the maximum concurrent sessions defined by the SESSIONS parameter.
Root Cause: Too many concurrent connections; SESSIONS parameter set too low relative to workload demands.
Meaning: A DDL statement could not obtain the required lock because another session holds a conflicting lock.
Root Cause: Active uncommitted DML transactions on the target table preventing DDL execution.
Meaning: Two or more sessions are waiting for resources held by each other, creating a circular dependency.
Root Cause: Poor application transaction ordering; multiple sessions locking rows in different orders causing a deadlock cycle.
Meaning: The archiver process cannot archive redo logs, halting all DML operations in the database.
Root Cause: Archive log destination full; FRA space exhausted; disk failure or permission issue on archive destination directory.
Meaning: Oracle's generic internal error indicating a problem within the Oracle kernel itself.
Root Cause: Oracle software bug, corrupted data blocks, or environment instability. Always requires trace file analysis and possible Oracle Support SR.
Meaning: An error occurred during execution of recursive SQL within Oracle internals or a trigger.
Root Cause: Faulty logon trigger; broken SYS-level object; recursive operation failing due to space or privilege issues.
Meaning: MEMORY_TARGET is set but the OS does not support the required /dev/shm mounted filesystem.
Root Cause: /dev/shm tmpfs not mounted or too small; required for AMM (Automatic Memory Management) on Linux.
Meaning: The referenced table or view was not found or the user lacks SELECT privilege on it.
Root Cause: Missing synonym; wrong schema qualifier; object not created; insufficient privileges granted to the connecting user.
Meaning: A column in the SELECT list is not in the GROUP BY clause and not wrapped in an aggregate function.
Root Cause: Incorrect SQL syntax; non-aggregated column in SELECT without a corresponding GROUP BY entry.
Meaning: The session has opened more cursors than allowed by the OPEN_CURSORS parameter.
Root Cause: Cursor leak in application code (cursors not closed after use); OPEN_CURSORS parameter set too low.
Meaning: Authentication failed because the supplied username or password is incorrect.
Root Cause: Wrong credentials; case-sensitive password mismatch (11g+); account locked due to repeated failures; expired password.
Meaning: The user attempted an operation requiring a privilege that has not been granted.
Root Cause: Missing system or object privilege; role-based grants not active in current session; attempting SYSDBA without OS group membership.
Meaning: The database is either starting up or shutting down and not yet available for connections.
Root Cause: Database instance in MOUNT or NOMOUNT state; ongoing startup/shutdown; abnormal instance restart in progress.
Meaning: The Oracle instance is not running or has crashed, making the database unavailable.
Root Cause: Instance not started; background process crash (PMON, SMON, DBWn); ORA-00600 or ORA-07445 caused instance abort.
Meaning: Oracle could not read or process the SPFILE/PFILE during database startup.
Root Cause: Corrupted or missing SPFILE; incorrect ORACLE_SID; invalid parameter syntax in init.ora or spfile.
Meaning: A datafile requires media recovery before it can be opened or brought online.
Root Cause: Datafile restored from backup but not yet recovered; database crashed with an open datafile that has not been recovered.
Meaning: The database cannot identify or lock a datafile during startup, preventing the database from opening.
Root Cause: Datafile physically missing; permissions issue for oracle OS user; ASM disk group unavailable.
Meaning: A SELECT INTO in PL/SQL returned no rows, raising NO_DATA_FOUND exception.
Root Cause: Query returned zero rows; missing data; wrong WHERE clause. Handle with EXCEPTION WHEN NO_DATA_FOUND.
Meaning: A SELECT INTO in PL/SQL returned more than one row, not allowed for scalar assignment.
Root Cause: WHERE clause not selective enough; use a cursor or aggregate function when multiple rows are expected.
Meaning: A DML operation failed because the index on the target table is UNUSABLE.
Root Cause: Index made unusable after direct-path load or partition operation. Rebuild with ALTER INDEX REBUILD.
Meaning: The user's tablespace quota has been exhausted and no more space can be allocated.
Root Cause: ALTER USER quota too low; no UNLIMITED TABLESPACE privilege; excessive inserts or LOB storage by application.
Meaning: Oracle could not reconstruct a consistent read image because the required undo data was overwritten.
Root Cause: UNDO_RETENTION too low; undo tablespace undersized; long-running queries on a high-DML system consuming undo rapidly.
Meaning: A logical or physical block corruption has been detected in a datafile.
Root Cause: Storage hardware failure; I/O subsystem error; OS bug; memory corruption during write. Requires RMAN block recovery.
Meaning: A sort or hash operation ran out of space in the temporary tablespace.
Root Cause: Temp tablespace full; concurrent sorts competing for temp space; large unoptimized query; tempfile AUTOEXTEND disabled.
Meaning: A table segment could not be extended because the tablespace has insufficient free space.
Root Cause: Tablespace full; AUTOEXTEND disabled; no filesystem space for datafile growth.
Meaning: Oracle attempted to convert a character string to a number, but the string is not a valid numeric value.
Root Cause: Implicit conversion from VARCHAR2 to NUMBER on a non-numeric value; dirty data in VARCHAR2 columns.
Meaning: The month portion of a date string could not be recognized during date conversion.
Root Cause: NLS_DATE_FORMAT mismatch; incorrect month abbreviation; date format string does not match the DB setting.
Meaning: An operation (DROP USER, GRANT) was attempted on a user that doesn't exist in the database.
Root Cause: Typo in username; wrong database/PDB targeted; user already dropped or never created.
Meaning: The DROP USER command failed because the user has active sessions in the database.
Root Cause: Active sessions using the account. Kill all user sessions first with ALTER SYSTEM KILL SESSION.
Meaning: An INSERT or UPDATE on a child table failed because the referenced parent key does not exist.
Root Cause: Child record references a parent value not yet inserted; data load sequence error; wrong FK value.
Meaning: A DELETE on a parent table failed because related child records exist in a FOREIGN KEY constrained table.
Root Cause: Attempting to delete a parent row without removing related child rows; missing CASCADE DELETE.
Meaning: The connection between client and Oracle server was unexpectedly terminated.
Root Cause: Instance crash; network disconnection; firewall killing idle connections; server process killed by OOM killer.
Meaning: The Oracle SGA has insufficient contiguous free memory in the Shared Pool, Large Pool, or Java Pool.
Root Cause: Shared Pool undersized; memory fragmentation; excessive hard parsing; too many SQL statements with literals instead of bind variables.
Meaning: An Oracle server process ran out of virtual memory while trying to allocate memory for an operation.
Root Cause: PGA_AGGREGATE_TARGET too small; concurrent large operations; OS swap exhausted; memory leak in session.
Meaning: A DML operation failed because a trigger on the table is in an invalid state and could not be recompiled.
Root Cause: Trigger references a dropped or modified object; PL/SQL compilation error in trigger body.
Meaning: A PL/SQL variable assignment failed due to data type mismatch, overflow, or string too long.
Root Cause: VARCHAR2 variable too small; NUMBER overflow; implicit conversion failure between incompatible types.
Meaning: A stack trace message indicating the line number where an unhandled PL/SQL exception propagated.
Root Cause: Always accompanies another ORA error; points to the exact failing line in PL/SQL code. Not an error itself — use it to locate the root cause.
Meaning: An unexpected OS-level exception (signal) was received within an Oracle process, potentially causing a core dump.
Root Cause: Oracle software bug or environment issue; check alert log and trace files. Usually requires Oracle Support SR.
Meaning: The connection string (service name) could not be found in the TNS configuration.
Root Cause: Incorrect or missing entry in tnsnames.ora; wrong ORACLE_HOME or TNS_ADMIN path; service name not registered with the listener.
Meaning: No Oracle listener process is running on the specified host and port.
Root Cause: Listener process stopped or crashed; wrong port in tnsnames.ora; firewall blocking the listener port; listener not started after OS reboot.
Meaning: The Oracle listener does not recognize the service name being requested.
Root Cause: Service not registered; database not open; wrong SERVICE_NAME; listener needs reload after service name change.
Meaning: A generic TNS/network protocol error preventing Oracle connection establishment.
Root Cause: Windows: Oracle service not running. Linux: ORACLE_SID not set; listener not running; ORACLE_HOME environment incorrect.
Meaning: A value being inserted or updated exceeds the maximum byte length of the target column.
Root Cause: Data exceeds VARCHAR2 column size; NLS character set multibyte expansion; incorrect column sizing.
Meaning: A DML operation failed because the database is open in READ ONLY mode.
Root Cause: Connected to a Data Guard Active Data Guard standby (read-only); wrong database targeted for write operations.
Meaning: The Fast Recovery Area (FRA) has exceeded its allocated disk quota.
Root Cause: DB_RECOVERY_FILE_DEST_SIZE too small; old backups and archivelogs not being purged; retention policy not deleting obsolete files.
Meaning: The Fast Recovery Area has reached 100% capacity and Oracle cannot create new recovery files.
Root Cause: Archivelog accumulation without deletion; RMAN retention policy not cleaning up; FRA size not increased as archive rate grew.
Meaning: The user account has been locked due to too many failed login attempts or explicit locking.
Root Cause: FAILED_LOGIN_ATTEMPTS exceeded in user profile; account manually locked with ALTER USER ACCOUNT LOCK.
Meaning: The user's password has expired per the PASSWORD_LIFE_TIME setting in their profile.
Root Cause: PASSWORD_LIFE_TIME exceeded; Oracle default profile has PASSWORD_LIFE_TIME=180 days.
Meaning: The client and server cannot agree on an authentication protocol due to version mismatch.
Root Cause: Old client (11g) connecting to 12c+ server with strict ALLOWED_LOGON_VERSION_SERVER; adjust SQLNET.ALLOWED_LOGON_VERSION_SERVER.
Meaning: The undo tablespace cannot extend to accommodate the current transaction's undo data.
Root Cause: Undo tablespace full; AUTOEXTEND disabled; large bulk DML generating excessive undo; undo retention competition.
Meaning: One or more initialization parameters in the SPFILE/PFILE are obsolete in the current Oracle version.
Root Cause: Parameters valid in older Oracle versions that have been deprecated; typically encountered after an Oracle upgrade.
Meaning: A Flashback Database operation was attempted but Flashback logging is not enabled.
Root Cause: ALTER DATABASE FLASHBACK ON never executed; flashback logs location not configured; FRA not set up.
Meaning: A Data Pump export or import operation received an invalid parameter value.
Root Cause: Incorrect syntax in expdp/impdp parameter file; unsupported option combination; wrong schema name or directory.
Meaning: The Data Pump job could not create or open its log file in the specified directory.
Root Cause: Directory full or permissions denied; wrong LOGFILE parameter; OS-level write restriction on the export directory.
Meaning: An operation referenced a PDB name that does not exist in the CDB.
Root Cause: Typo in PDB name; PDB dropped or unplugged; wrong CDB being queried; PDB name case sensitivity.
Meaning: A command that must be run from CDB$ROOT was attempted from within a PDB session.
Root Cause: Connected to wrong container; certain startup/shutdown and CDB-wide operations must be performed from the root container.
Meaning: A user/role creation in the CDB root failed because the name does not follow the required C## naming convention.
Root Cause: In CDB$ROOT, common users must start with C## prefix by default; attempting CREATE USER without C## prefix.
Meaning: A row being inserted has a partition key that doesn't match any defined partition range or list.
Root Cause: Missing partition for the incoming data range/list value; need MAXVALUE/DEFAULT partition or to create the missing partition.
Meaning: The temporary tablespace has no tempfiles, so sort and temporary operations cannot proceed.
Root Cause: Tempfile missing after database creation/migration; TEMP tablespace created without adding a tempfile.
Meaning: Oracle cannot resolve a column name, alias, or expression used in the SQL statement.
Root Cause: Typo in column name; referencing a SELECT-list alias in a WHERE clause (aliases not allowed there); column does not exist in the referenced table; case-mismatch on a quoted identifier.
Meaning: A column alias defined in SELECT was referenced in the WHERE clause, which Oracle evaluates before the SELECT list.
Root Cause: SQL processing order: WHERE is evaluated before SELECT alias assignment. Wrap query in a subquery or inline view to reference the alias, or repeat the expression in WHERE.
Meaning: An object created with double-quoted mixed-case identifiers must always be referenced with the exact same case and quotes.
Root Cause: Column created as "firstName" (mixed case) but queried as FIRSTNAME. Oracle uppercases unquoted identifiers by default; always use "firstName" in every reference.
Meaning: Oracle's SQL parser expected a specific keyword at a certain position but found something else or nothing.
Root Cause: Incomplete DML/DDL syntax; missing INTO in INSERT; missing SET in UPDATE; missing AS in CREATE VIEW; accidental use of reserved words as unquoted identifiers.
Meaning: Oracle's parser detected an unmatched opening parenthesis or found unexpected content before a required closing parenthesis.
Root Cause: Unbalanced parentheses in complex SQL; trailing comma before closing paren in CREATE TABLE; subquery parenthesis omitted; function call malformed.
Meaning: The SQL statement contains a character that is not valid at that position in Oracle SQL syntax.
Root Cause: Semicolon included at end of SQL when executing via JDBC or OCI (not SQL*Plus); non-printable or Unicode character pasted into SQL text from an editor; curly brace or bracket misuse.
Meaning: Oracle expected a valid expression (column, literal, function) at a certain point in the SQL but found nothing.
Root Cause: Trailing comma after last column in SELECT list; empty IN() clause like col IN (); missing column after SELECT keyword; incomplete CASE WHEN expression.
Meaning: A SELECT list mixes aggregate functions (SUM, COUNT) with non-aggregated columns without a GROUP BY clause.
Root Cause: Attempting SELECT col, SUM(amount) FROM table without GROUP BY col; add GROUP BY for all non-aggregated columns or remove them from SELECT.
Meaning: A CREATE statement failed because an object with the same name already exists in the current schema.
Root Cause: Attempting to CREATE TABLE/VIEW/SEQUENCE that already exists; use CREATE OR REPLACE for views/procedures; for tables use DROP first or rename the existing object.
Meaning: An INSERT statement omitted or explicitly set a NOT NULL constrained column to NULL.
Root Cause: Application not supplying a required column value; column added as NOT NULL after application was deployed; missing DEFAULT clause on a required column.
Meaning: A CHAR or VARCHAR2 column received a value longer than its defined byte/character length.
Root Cause: Data length exceeds column definition; NLS character set causes single character to expand to multiple bytes; column needs ALTER TABLE MODIFY to increase size.
Meaning: An attempt to create an index failed because an identical index (same columns, same order) already exists on the table.
Root Cause: Duplicate index creation; primary key or unique constraint automatically creates an index; DBA script re-run without idempotency check.
Meaning: A subquery used where a single value is expected (e.g., col = (SELECT ...)) returned multiple rows.
Root Cause: Non-unique subquery result; use IN instead of = for multi-row subqueries; or add aggregate (MAX, MIN) to ensure scalar result; or add WHERE to make subquery return one row.
Meaning: A division operation in SQL or PL/SQL attempted to divide a number by zero.
Root Cause: Division expression lacks zero-guard; use NULLIF(denominator, 0) to return NULL instead of dividing; or add CASE WHEN denominator = 0 THEN NULL ELSE numerator/denominator END.
Meaning: A concatenation operation produced a result exceeding the 4000-byte SQL limit or 32767-byte PL/SQL VARCHAR2 limit.
Root Cause: Concatenating many large strings; aggregate string building in PL/SQL loop; use CLOB datatype for results exceeding VARCHAR2 limits.
Meaning: The format string passed to TO_CHAR or TO_NUMBER contains characters that are not valid number format elements.
Root Cause: Incorrect format mask (e.g., 'DD-MON-YYYY' used for a number); mixing date and number format elements; non-standard separator in format string.
Meaning: The DB_NAME initialization parameter does not match the database name recorded in the controlfile.
Root Cause: SPFILE DB_NAME changed after controlfile creation; wrong controlfile used after restore; duplicate/clone database not reconfigured correctly.
Meaning: An operation requiring a mounted database was attempted while the instance is only in NOMOUNT state.
Root Cause: Controlfile not read during startup; STARTUP NOMOUNT used and DBA forgot to issue ALTER DATABASE MOUNT; missing or corrupt controlfile preventing mount stage.
Meaning: Media recovery completed successfully but the database must be opened with RESETLOGS to reset the log sequence.
Root Cause: Normal consequence of incomplete recovery or UNTIL TIME/SCN recovery; run ALTER DATABASE OPEN RESETLOGS to complete the process and take a fresh backup immediately after.
Meaning: A large DELETE or UPDATE operation exhausted undo space, overwriting undo needed by concurrent read-consistent queries.
Root Cause: Bulk DML generating massive undo without committing; run large DELETEs in smaller batches with intermediate COMMITs; increase undo tablespace or UNDO_RETENTION.
Meaning: A PL/SQL cursor loop that COMMITs inside the loop may invalidate the read-consistent snapshot for the open cursor.
Root Cause: Committing inside a FOR LOOP over a cursor; Oracle must maintain a consistent read image from cursor open time; restructure logic to avoid committing mid-cursor or use BULK COLLECT with LIMIT.
Meaning: A row or table is locked by a distributed transaction that is stuck in the in-doubt state after a two-phase commit failure.
Root Cause: Network failure during two-phase commit over a DB link; remote database unavailable at commit time; manually force commit/rollback via DBA_2PC_PENDING and DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY.
Meaning: A table segment reached its MAXEXTENTS storage parameter limit and cannot allocate additional extents.
Root Cause: Legacy MAXEXTENTS setting on a table; common in older DICTIONARY-managed tablespaces; use ALTER TABLE STORAGE (MAXEXTENTS UNLIMITED) or migrate to locally managed tablespace.
Meaning: An index segment could not grow because the tablespace hosting the index has insufficient free space.
Root Cause: Index tablespace full; AUTOEXTEND disabled on index datafiles; large data load causing rapid index growth; add datafile or enable AUTOEXTEND on existing datafile.
Meaning: Oracle could not find a contiguous free block to satisfy the INITIAL extent requirement for a new segment.
Root Cause: Tablespace has free space in total but no single contiguous chunk large enough; fragmentation issue; coalesce the tablespace or add a new datafile.
Meaning: A LOB segment (CLOB, BLOB, NCLOB) cannot extend because its tablespace has run out of space.
Root Cause: LOB tablespace full; AUTOEXTEND disabled; unexpected LOB data growth; add a datafile to the LOB tablespace or enable AUTOEXTEND on existing datafile.
Meaning: Oracle attempted an implicit VARCHAR2-to-NUMBER conversion in a WHERE clause predicate and failed due to non-numeric data.
Root Cause: Column defined as VARCHAR2 but queried as a number (col = 123); dirty data containing letters or spaces; always use explicit TO_NUMBER with error handling or fix the data model.
Meaning: During a data migration or ETL load, source data contains non-numeric values in a column mapped to a NUMBER target column.
Root Cause: Source system allowed free-text in a numeric field; use VALIDATE_CONVERSION (12.2+) or REGEXP_LIKE to pre-validate data before loading; trap with EXCEPTIONS INTO clause.
Meaning: A string literal in the SQL statement was opened with a single quote but never properly closed.
Root Cause: Unescaped single quote inside a string literal (use '' to escape); line continuation error; copy-paste from a word processor introducing smart quotes instead of standard single quotes.
Meaning: The format mask passed to TO_DATE is shorter than the actual date string being converted, leaving unconverted characters.
Root Cause: Format mask 'DD-MON-YYYY' used for a string like '01-JAN-2024 10:30:00'; add HH24:MI:SS to the format mask or truncate the input string; use FX modifier for exact matching.
Meaning: The month name in the date string is not recognized because it belongs to a different NLS_DATE_LANGUAGE than the session is configured for.
Root Cause: Month name 'JAN' expected but session NLS_DATE_LANGUAGE is set to a non-English language; use NLS_DATE_LANGUAGE='AMERICAN' parameter in TO_DATE call: TO_DATE('01-JAN-2024','DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN').
Meaning: Oracle expected a numeric digit in the date string at a position specified by the format mask but found a letter or symbol instead.
Root Cause: Format mask 'DD/MM/YYYY' used for a string like '01-JAN-2024'; mismatch between separator characters in format and data; verify format mask exactly matches the input string structure.
Meaning: A database link query failed because the service name defined in the link's USING clause cannot be resolved.
Root Cause: TNS alias used in CREATE DATABASE LINK USING clause missing from tnsnames.ora on the DB server; listener on remote DB not running; verify with SELECT * FROM USER_DB_LINKS and test tnsping from the server.
Meaning: A distributed transaction (involving a database link) waited beyond the DISTRIBUTED_LOCK_TIMEOUT limit for a remote lock.
Root Cause: Lock contention on the remote database; long-running remote transaction; network latency; increase DISTRIBUTED_LOCK_TIMEOUT or investigate locking on the remote database.
Meaning: The GLOBAL_NAMES parameter enforces that the database link name must match the remote database's global name, but they differ.
Root Cause: GLOBAL_NAMES=TRUE set but DB link name does not match remote DB's GLOBAL_NAME; either set GLOBAL_NAMES=FALSE or recreate the link with the correct global name of the remote database.
Meaning: A stateful firewall silently dropped the Oracle TCP connection after an idle period, causing the next operation to fail with a broken pipe.
Root Cause: Network firewall or load balancer with idle connection timeout shorter than Oracle session inactivity; fix by setting SQLNET.EXPIRE_TIME in sqlnet.ora to enable Oracle dead connection detection before firewall timeout.
Meaning: The Oracle session has been terminated or the connection to the server process has been lost.
Root Cause: Server process killed (ALTER SYSTEM KILL SESSION); instance restart; network interruption; OOM killer terminated the server process; application must reconnect and reissue the transaction.
Meaning: Two sessions deadlocked while attempting to lock a PL/SQL object in the library cache (e.g., simultaneous recompile of mutually-dependent packages).
Root Cause: Concurrent recompilation of interdependent packages; circular dependency between PL/SQL objects; schedule recompilations serially or use DBMS_UTILITY.COMPILE_SCHEMA.
Meaning: An ALTER PROCEDURE/PACKAGE recompile or DDL statement could not acquire a library cache lock on the named object within the timeout period.
Root Cause: Object actively being used by another session; long-running PL/SQL referencing the object; identify blocking session via V$ACCESS and V$SESSION, then kill it before recompiling.
Meaning: Oracle attempted to automatically revalidate an invalid PL/SQL object but the recompilation failed.
Root Cause: Dependent object (table, package, type) was dropped or altered; the PL/SQL object references a column or object that no longer exists in its original form; check USER_ERRORS after manual recompile.
Meaning: The package body was recompiled or replaced while a session had initialized package-level state (variables), invalidating that state.
Root Cause: DBA recompiled a package while application sessions held package state; all existing sessions must reconnect or call the package again to reinitialize; design stateless packages where possible.
Meaning: A call to a packaged procedure or function failed because the package body is in an INVALID state due to compilation errors.
Root Cause: Compilation errors in the package body; underlying table or type changed; check errors with SELECT * FROM USER_ERRORS WHERE NAME='PACKAGE_NAME' and fix the body then recompile.
Meaning: Package-level variables (global state) were discarded because the package was recompiled mid-session.
Root Cause: Online package redeployment while users are connected; the second call to the package after recompile triggers this; applications should handle ORA-04068 by retrying the operation once to reinitialize state.
Meaning: A row-level trigger tried to query or modify the same table that fired the trigger while it is in the middle of being changed.
Root Cause: FOR EACH ROW trigger on TABLE_A querying TABLE_A; redesign using a compound trigger (11g+) with AFTER STATEMENT section, or use a package-level collection to cache values and process after the statement.
Meaning: PL/SQL ran out of memory to complete the current operation, often when working with large collections or deep recursion.
Root Cause: Unbounded BULK COLLECT filling a collection beyond PGA limits; deep recursive PL/SQL call stack; process PGA_AGGREGATE_TARGET too low; use LIMIT clause with BULK COLLECT to process in batches.
Meaning: A PL/SQL program attempted to access an attribute of an object type or record variable that was never initialized.
Root Cause: Object type variable declared but never constructed with its constructor function; access attribute before calling obj_type(); always initialize object variables before attribute access.
Meaning: A nested table or varray PL/SQL variable was declared but never initialized before being accessed.
Root Cause: Nested table declared as TYPE t IS TABLE OF VARCHAR2(100) but not initialized with t := type_constructor(); always call the collection constructor or use EXTEND before populating elements.
Meaning: A PL/SQL collection was accessed at an index position beyond the number of existing elements.
Root Cause: Accessing collection(i) where i > collection.COUNT; off-by-one error in loop; use col.LAST or col.COUNT to guard loop boundaries; always check collection.EXISTS(i) before access.
Meaning: A PL/SQL block, procedure, function, or package failed to compile due to a syntax or semantic error at the indicated line and column.
Root Cause: Syntax error in PL/SQL body; referencing non-existent objects; type mismatch in assignment; check exact line number indicated and query USER_ERRORS for detailed messages after CREATE OR REPLACE.
Meaning: The sequence has reached its MAXVALUE and NOCYCLE is set, so NEXTVAL cannot produce further values.
Root Cause: Sequence exhausted; MAXVALUE set too low; use ALTER SEQUENCE MAXVALUE 9999999999999 to extend it, or ALTER SEQUENCE CYCLE if repeating values are acceptable for the use case.
Meaning: A reference to a sequence (NEXTVAL or CURRVAL) failed because the sequence has been dropped.
Root Cause: Sequence accidentally dropped; schema migration removed sequence; triggers or application code referencing a sequence that no longer exists; recreate with correct START WITH value.
Meaning: The listener found the service but could not route the connection because all available handlers (dedicated servers or dispatchers) are busy or exhausted.
Root Cause: PROCESSES or SESSIONS parameter limit reached; too many concurrent connections; connection pool not returning connections; increase PROCESSES parameter and bounce the instance.
Meaning: The database instance is registered with the listener but is in a state (MOUNT, RESTRICT) that blocks new user connections.
Root Cause: Database opened with STARTUP RESTRICT; instance in middle of startup or shutdown; normal connections blocked by DBA activity; wait for OPEN state or connect as SYSDBA internally.
Meaning: Oracle cannot resolve the hostname specified in the connection descriptor because DNS resolution failed or the host does not exist.
Root Cause: Incorrect hostname or IP in tnsnames.ora; DNS entry removed or changed after server migration; firewall blocking DNS; verify with ping and nslookup from the application server.
Meaning: The connection was established at the TCP level but the Oracle server process died or failed to complete the authentication handshake.
Root Cause: Server process immediately crashed after spawn; check alert log for ORA-00600 or ORA-07445 at connection time; OS-level ulimit issues for the oracle user; trace file in $ORACLE_BASE/diag.
Meaning: A value being inserted falls beyond all defined range partition boundaries and there is no MAXVALUE catch-all partition.
Root Cause: Date partitioned table with future dates not yet defined; no PARTITION FOR (MAXVALUE); add a new partition with ALTER TABLE ADD PARTITION or include a MAXVALUE partition at creation time.
Meaning: A value being inserted into a LIST-partitioned table is not present in any partition's list of values and no DEFAULT partition exists.
Root Cause: New list value introduced in the data without adding a corresponding partition; add the new value to an existing partition or create a new partition; include a DEFAULT partition to capture unmapped values.
Meaning: An RMAN hot (online) backup was attempted but the database is in NOARCHIVELOG mode, which does not support online backups.
Root Cause: Hot backup requires ARCHIVELOG mode because Oracle needs redo logs to make the backup consistent; enable ARCHIVELOG mode or take a cold (consistent) backup after SHUTDOWN IMMEDIATE.
Meaning: RMAN cannot identify or open the specified datafile, possibly because it has been renamed, moved, or is missing from the OS.
Root Cause: Datafile renamed at OS level without updating Oracle; ASM disk group rebalanced the file path; use ALTER DATABASE RENAME FILE to update Oracle's controlfile reference.
Meaning: RMAN encountered an error while reading from a backup piece during a restore operation.
Root Cause: Backup piece corrupt or incomplete; tape read error; backup piece expired or deleted from catalog but still referenced; validate backup with RMAN VALIDATE BACKUPSET before restore.
Meaning: An attempt was made to read or write a LOB using a locator that is no longer valid in the current session or transaction context.
Root Cause: LOB locator fetched in one session/transaction and used in another; LOB locator stale after a COMMIT when not using SELECT FOR UPDATE; re-fetch the LOB locator in the same transaction before accessing it.
Meaning: A SELECT over a database link returned a LOB column, but Oracle cannot maintain LOB locators across DB link boundaries.
Root Cause: Querying a CLOB or BLOB column from a remote table via database link; workaround: use DBMS_LOB.SUBSTR to read partial content, or use a remote procedure to convert LOB to VARCHAR2/RAW chunks before transferring.
Meaning: One or more statements in a FORALL bulk DML operation failed, and the SAVE EXCEPTIONS clause was used to capture them.
Root Cause: Mixed valid/invalid data in the bulk collection; check SQL%BULK_EXCEPTIONS array after the FORALL block to identify which rows failed and why; each element has ERROR_INDEX and ERROR_CODE.
Meaning: UTL_FILE.PUT_LINE or UTL_FILE.FFLUSH failed when attempting to write to a file on the server filesystem.
Root Cause: Filesystem full; OS-level write permissions denied for the oracle user on the directory; directory quota exceeded; file handle not properly opened; verify the Oracle directory object points to a writable OS path.
Meaning: UTL_FILE.FOPEN failed because the OS directory does not exist, the Oracle Directory object is invalid, or the oracle user lacks read/write permissions.
Root Cause: Oracle DIRECTORY object path does not match an existing OS directory; oracle user not in the OS group with access; directory was renamed or deleted; verify with SELECT DIRECTORY_PATH FROM DBA_DIRECTORIES.
Meaning: A MERGE statement cannot proceed because the source query returns multiple rows that match the same target row, making the outcome non-deterministic.
Root Cause: Source of MERGE produces duplicate key values for the join condition; MERGE requires each target row to match at most one source row; deduplicate the source with ROW_NUMBER() OVER (PARTITION BY key) before the MERGE.
Meaning: An attempt to attach to or monitor a Data Pump job by name failed because that job does not exist or has already completed.
Root Cause: Wrong job name specified in expdp ATTACH= parameter; job completed and was auto-dropped; check active jobs with SELECT JOB_NAME, STATE FROM DBA_DATAPUMP_JOBS WHERE STATE != 'NOT RUNNING'.
Meaning: A Data Pump export job completed with no objects exported because the filter criteria matched nothing.
Root Cause: TABLES= or SCHEMAS= parameter specified a non-existent object; QUERY= filter excluded all rows; wrong PDB targeted; verify object names with SELECT TABLE_NAME FROM DBA_TABLES before running expdp.
Meaning: During impdp, a specific object (table, index, constraint, grant) could not be created in the target database.
Root Cause: Target schema missing required tablespace; name collision with existing object; insufficient privileges for the import user; check the Data Pump log file for the exact DDL that failed and the ORA error it triggered.
Meaning: Data Pump could not create its internal master control table in the schema of the user running the job.
Root Cause: User running expdp/impdp lacks CREATE TABLE privilege in their own schema; user's tablespace quota exhausted; tablespace containing the schema is full; grant CREATE TABLE or increase quota.
Meaning: A user with DBA privilege attempted to access an object protected by an Oracle Database Vault realm without being an authorized participant.
Root Cause: Oracle Database Vault realm restricts access even to DBA accounts; the connecting user is not added to the realm authorization; request realm access from the Database Vault Owner or AV Admin.
Meaning: An initialization parameter was set at the PDB level that can only be set at the CDB level or has been deprecated in multitenant architecture.
Root Cause: Attempting to set CDB-wide parameters from within a PDB session; certain parameters like DB_BLOCK_SIZE, DB_NAME cannot be set per-PDB; connect to CDB$ROOT as SYSDBA to modify CDB-level parameters.
Meaning: An ALTER SESSION SET CONTAINER command or a direct PDB service connection failed because the target PDB is not in an open state.
Root Cause: Target PDB is in MOUNTED or RESTRICTED state; PDB was not opened after a CDB restart; run ALTER PLUGGABLE DATABASE pdb_name OPEN from CDB$ROOT to open the PDB first.
Meaning: The PROCESSES initialization parameter limit has been reached and Oracle cannot spawn another server process for a new connection.
Root Cause: PROCESSES parameter set too low; connection pool misconfiguration causing connection leak; too many concurrent users; increase PROCESSES (requires restart), kill idle sessions, or implement connection pooling.
Meaning: A session targeted by ALTER SYSTEM KILL SESSION is waiting for a resource and cannot be immediately terminated — it is marked for deferred killing.
Root Cause: The session is waiting for a lock or I/O to complete before it can be killed; use ALTER SYSTEM KILL SESSION '...' IMMEDIATE for faster termination; alternatively kill the OS process with ORAKILL (Windows) or kill -9 (Linux) using the SPID from V$SESSION.
Meaning: Oracle Resource Manager terminated a database call because the session exceeded the MAX_ACTIVE_SESS_TARGET or active call time limit defined in the active Resource Plan.
Root Cause: Resource Manager consumer group with strict CPU/time limits; runaway query exceeded defined execution time; review DBMS_RESOURCE_MANAGER plan directives and adjust SWITCH_TIME or MAX_ACTIVE_SESS_TARGET parameters.
Meaning: An ALTER SYSTEM SET command specified an invalid value for an initialization parameter.
Root Cause: Value out of allowed range; wrong data type for parameter (string vs integer); dependent parameter constraint violated (e.g., setting SGA_TARGET larger than SGA_MAX_SIZE); check valid range in Oracle documentation.
Meaning: Oracle attempted to create a new controlfile (CREATE CONTROLFILE) but failed to write to the specified location.
Root Cause: Target directory does not exist or is not writable by oracle user; disk full; wrong path specified in CONTROLFILE parameter; verify OS path permissions before executing CREATE CONTROLFILE script.
Meaning: Oracle encountered an I/O error while reading the controlfile, potentially indicating corruption or a storage-level issue.
Root Cause: Storage hardware failure; controlfile on a failed disk; use a multiplexed controlfile copy to recover; restore controlfile from RMAN backup using RESTORE CONTROLFILE FROM AUTOBACKUP.
Meaning: Oracle detected a corrupt block within the controlfile, indicating the controlfile is damaged and cannot be trusted.
Root Cause: Disk corruption; abrupt OS shutdown during controlfile write; restore controlfile from the multiplexed copy or from RMAN backup; never proceed with a corrupt controlfile.
Meaning: Oracle's block read layer (kcbz*) detected an inconsistency in a data block that should not occur under normal operation.
Root Cause: Possible block corruption; memory subsystem error; note the first argument in brackets (e.g., [kcbz_check_objd_typ]) and immediately run RMAN VALIDATE DATABASE to check all blocks; open an Oracle SR with the trace file.
Meaning: Oracle's transaction layer (ktb*) found an inconsistency while performing block cleanout using undo data.
Root Cause: Often associated with block corruption or undo segment corruption; run DBVERIFY on the affected datafile; check alert log for associated ORA-01578; may require block-level recovery with RMAN RECOVER BLOCK.
Meaning: This message always accompanies another error and identifies the specific datafile by number and path that is involved in the error condition.
Root Cause: Not a standalone error; provides context for ORA-01157, ORA-01113, or ORA-01578; use the file# and filename to target RMAN recovery or OS-level investigation for the root-cause error above it in the stack.
Meaning: A datafile passed to an operation (backup, restore, recovery) failed Oracle's internal consistency checks and cannot be used.
Root Cause: Datafile header corrupt; wrong datafile restored to wrong location; file is not a valid Oracle datafile; verify datafile headers with DBVERIFY utility: dbv file=/path/to/datafile.dbf blocksize=8192.
Meaning: A datafile cannot be brought online because its checkpoint SCN is ahead of what the current redo stream provides, indicating incomplete recovery.
Root Cause: Trying to open the database after incomplete recovery without RESETLOGS; missing archived logs needed to advance the datafile checkpoint; apply all required archivelogs or use UNTIL SCN/TIME to recover to a consistent point.
Meaning: An attempt to query a user table or view failed because the database is in MOUNT state and only V$ fixed views are accessible.
Root Cause: Database not opened (still in MOUNT); DBA connected as SYSDBA but forgot to open; use ALTER DATABASE OPEN or investigate why OPEN is failing (check V$RECOVER_FILE for files needing recovery).
Meaning: An index creation failed because the combined byte length of the indexed columns exceeds the maximum allowed index key size.
Root Cause: Oracle limits index key size to approximately 75% of the DB_BLOCK_SIZE (e.g., 6398 bytes for 8K blocks); composite index on multiple large VARCHAR2 columns; reduce indexed column widths or use a function-based index on a hash.
Meaning: A bind variable containing a string larger than 4000 bytes was used in a context other than inserting into a LONG column.
Root Cause: Passing a >4000 byte string via JDBC or OCI to a VARCHAR2 column without using CLOB binding; use setClob/CLOB bind type for large text values; or switch the target column to CLOB datatype.
Meaning: A session was automatically disconnected because it exceeded the IDLE_TIME limit defined in the Oracle user profile or Resource Manager plan.
Root Cause: IDLE_TIME parameter in user profile set too low; long-running application holding a connection while idle; increase IDLE_TIME in the profile or implement keep-alive pings from the connection pool.
Meaning: A database call was terminated because it requested more PGA memory than the session-level PGA limit enforced by Resource Manager.
Root Cause: Resource Manager consumer group with PGA_MAX_USE_SIZE limit; large hash join or sort exceeding the per-session PGA allocation; tune the query to reduce memory demand or increase the consumer group's PGA limit.
Meaning: A single query performing a large sort or hash join exhausted the OS virtual memory available to the Oracle server process.
Root Cause: PGA_AGGREGATE_TARGET set too high relative to available OS memory causing overcommit; many concurrent memory-intensive operations; increase OS swap; reduce PGA_AGGREGATE_TARGET; optimize queries to reduce in-memory sort/join size.
Meaning: The archiver process (ARC) failed to archive a redo log because a mandatory secondary archive destination (LOG_ARCHIVE_DEST_2) is full or unreachable.
Root Cause: LOG_ARCHIVE_DEST_2 set to MANDATORY pointing to a standby or NFS mount that is full or unavailable; change to OPTIONAL or clean up the destination; DML is blocked until archiving can proceed.
Meaning: The Remote File Server (RFS) process on the standby rejected an archivelog from the primary database.
Root Cause: Log sequence gap between primary and standby; archivelog already applied on standby; DB_UNIQUE_NAME mismatch; check V$ARCHIVE_GAP on the primary and V$ARCHIVED_LOG on the standby to identify and resolve the gap.
Meaning: The Managed Recovery Process (MRP) on a physical standby has stopped applying redo, causing the standby to fall behind the primary.
Root Cause: Archived log gap; missing archive log; ORA-01578 block corruption on standby; MRP process crash; check standby alert log and V$DATAGUARD_STATUS; restart apply with ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT.
Meaning: One or more DBMS_JOB scheduled jobs failed to execute and may have been automatically marked as broken.
Root Cause: PL/SQL in the job raised an unhandled exception; object referenced by the job is invalid; job marked BROKEN after 16 consecutive failures; check DBA_JOBS.FAILURES and DBA_JOBS.WHAT; use DBMS_JOB.BROKEN to reset and DBMS_JOB.RUN to test manually.
Meaning: Oracle could not create the IPC semaphore sets required for inter-process communication during database startup.
Root Cause: OS kernel semaphore limits too low (SEMMNS, SEMMNI parameters); previous Oracle instance not cleanly shutdown leaving stale IPC resources; run ipcs -s to list semaphores, clean with ipcrm, and adjust kernel.sem in /etc/sysctl.conf.
Meaning: Oracle could not allocate the shared memory segment required for the SGA during instance startup.
Root Cause: OS kernel SHMMAX or SHMALL parameter too low to accommodate the configured SGA_TARGET; not enough physical RAM; set kernel.shmmax and kernel.shmall in /etc/sysctl.conf to values larger than SGA size and run sysctl -p.
Meaning: Oracle could not write a mandatory audit record to the OS audit trail directory (AUDIT_FILE_DEST).
Root Cause: AUDIT_FILE_DEST directory full; oracle user lacks write permissions; filesystem mounted read-only after storage issue; free space in the audit directory or redirect AUDIT_FILE_DEST to a larger filesystem.
Meaning: Oracle has hit the hard limit of 255 recursive SQL call levels, preventing any further nesting of SQL or PL/SQL calls.
Root Cause: Mutually recursive PL/SQL procedures calling each other indefinitely; a trigger on a table firing another trigger on the same or related table in a circular chain; recursive view definitions without a proper termination condition.
Solution: Trace the call chain with DBMS_UTILITY.FORMAT_CALL_STACK inside the top-level handler; redesign recursive PL/SQL with an explicit depth counter and EXIT WHEN depth > N; flatten circular trigger chains using conditional logic: IF INSERTING AND :NEW.source != 'TRIGGER' THEN ... END IF.
Meaning: An AFTER INSERT trigger on TABLE_A inserts into TABLE_B, which has an AFTER INSERT trigger that inserts back into TABLE_A, creating an infinite recursive call chain.
Root Cause: Bidirectional trigger logic between two or more tables forming a closed loop; audit or replication triggers that themselves perform DML on monitored tables; missing guard conditions to detect trigger-initiated DML.
Solution: Use a package-level boolean flag: IF NOT pkg_guard.in_trigger THEN pkg_guard.in_trigger := TRUE; ... pkg_guard.in_trigger := FALSE; END IF; alternatively redesign the data model to eliminate circular triggers.
Meaning: A PL/SQL procedure calls itself recursively without a valid base case, consuming all 255 available recursive SQL levels.
Root Cause: Tree-traversal or hierarchical data processing procedure missing the EXIT WHEN leaf-node condition; incorrect termination logic allowing infinite self-invocation; always add a maximum depth parameter: PROCEDURE traverse(p_id IN NUMBER, p_depth IN NUMBER DEFAULT 0) and EXIT WHEN p_depth > 50.
Solution: Rewrite deep tree traversals using iterative loops with an explicit stack collection (DBMS_SQL or a PL/SQL TABLE) instead of recursive calls; verify base case logic covers all possible leaf values including NULL.
Meaning: Oracle failed to acquire an OS-level IPC lock (enqueue) because the operating system returned an error during the lock request.
Root Cause: OS semaphore table exhausted (SEMMNS/SEMMNI kernel parameters too low); previous Oracle instance not cleanly shut down leaving orphaned IPC resources; OS user limits (ulimits) restricting IPC access for the oracle user.
Solution: Run ipcs -s | grep oracle to list orphaned semaphores; use ipcrm -s <semid> to remove stale sets; increase kernel.sem in /etc/sysctl.conf: kernel.sem = 250 32000 100 128; apply with sysctl -p and restart the instance.
Meaning: After an abnormal Oracle instance termination (kill -9 or OOM kill), the OS IPC semaphore sets used by the previous instance remain allocated, blocking the new startup.
Root Cause: Oracle background processes killed abruptly without SHUTDOWN ABORT; PMON did not clean up IPC resources before termination; run: ps -ef | grep ora_ to confirm no lingering oracle processes, then: ipcs -s and ipcrm to clear stale semaphores owned by the oracle OS user.
Solution: Use the Oracle-provided sysresv utility to list and clean Oracle IPC resources: $ORACLE_HOME/bin/sysresv; or script: for s in $(ipcs -s | awk '/oracle/{print $2}'); do ipcrm -s $s; done; then retry STARTUP.
Meaning: Oracle's internal enqueue wait mechanism timed out before the requested lock or resource could be granted.
Root Cause: A DDL statement waiting for table-level lock blocked by active DML; distributed lock timeout across RAC nodes; LOCK TABLE statement waiting beyond the defined timeout threshold.
Solution: Query V$ENQUEUE_STATISTICS and V$SESSION_WAIT to identify the blocker: SELECT BLOCKING_SESSION, SID, WAIT_CLASS, SECONDS_IN_WAIT FROM V$SESSION WHERE WAIT_CLASS != 'Idle'; kill the blocking session or increase enqueue timeout via ENQUEUE_RESOURCES parameter.
Meaning: In a RAC environment, a node timed out waiting for the Global Cache Service (GCS) to transfer a block from another node, exceeding the gc_block_lost wait threshold.
Root Cause: Interconnect network latency or packet loss between RAC nodes; gc buffer busy acquire waits indicating hot block contention; verify interconnect health with: SELECT * FROM V$CLUSTER_INTERCONNECTS; check gc cr block lost and gc current block lost in AWR.
Solution: Investigate AWR Top Events for gc waits; tune application to reduce hot block access; ensure private interconnect is on dedicated NIC at 10GbE+ with jumbo frames enabled; consider partitioning hot tables to reduce cross-node block bouncing.
Meaning: The ENQUEUE_RESOURCES initialization parameter limit has been reached and Oracle cannot allocate additional enqueue resources for locking.
Root Cause: High-volume concurrent DML transactions locking many distinct rows and tables simultaneously; ENQUEUE_RESOURCES set too low for the workload; applications not committing frequently enough, holding locks for extended periods.
Solution: Increase dynamically: ALTER SYSTEM SET ENQUEUE_RESOURCES = 10000; monitor current usage: SELECT * FROM V$RESOURCE_LIMIT WHERE RESOURCE_NAME = 'enqueue_resources'; also review DML_LOCKS parameter which indirectly controls enqueue usage.
Meaning: Oracle has reached the DML_LOCKS parameter limit, preventing additional table-level DML locks from being acquired.
Root Cause: Large number of concurrent transactions each locking many different tables; DML_LOCKS set too low (default = 4 × TRANSACTIONS); nightly batch jobs running thousands of concurrent DML statements across many tables simultaneously.
Solution: Increase DML_LOCKS: ALTER SYSTEM SET DML_LOCKS = 10000 SCOPE=SPFILE; requires bounce. Monitor: SELECT RESOURCE_NAME, CURRENT_UTILIZATION, MAX_UTILIZATION FROM V$RESOURCE_LIMIT WHERE RESOURCE_NAME='dml_locks'; alternatively set DML_LOCKS=0 to disable table-level locks entirely if application design supports it.
Meaning: A parallel DML operation spawned many slave processes each acquiring DML locks on the target table, collectively exhausting the DML_LOCKS pool.
Root Cause: INSERT /*+ PARALLEL */ with high degree of parallelism; each parallel slave acquires its own table lock; DML_LOCKS parameter not sized for parallel execution workloads; reduce DOP or temporarily disable PDML: ALTER SESSION DISABLE PARALLEL DML.
Solution: Calculate required DML_LOCKS: (number of concurrent transactions) × (average tables per transaction) × 1.5; for PDML-heavy environments add: (max DOP) × (number of parallel DML statements); increase parameter and bounce, or reduce DOP to stay within limits.
Meaning: The total number of datafiles across all tablespaces has reached the DB_FILES initialization parameter limit, preventing new datafiles from being added.
Root Cause: Database grown organically over years with many small tablespaces and datafiles; DB_FILES not reviewed during capacity planning; increasing MAX_DATAFILES in the controlfile is needed alongside DB_FILES.
Solution: Increase DB_FILES: ALTER SYSTEM SET DB_FILES=2000 SCOPE=SPFILE; restart required. Also recreate controlfile with higher MAXDATAFILES: ALTER DATABASE BACKUP CONTROLFILE TO TRACE; modify and run the CREATE CONTROLFILE script with MAXDATAFILES 2000. Monitor: SELECT COUNT(*) FROM V$DATAFILE.
Meaning: In an ASM environment with Oracle Managed Files (OMF), automatic file creation during partition operations created more datafiles than the DB_FILES limit allows.
Root Cause: Interval partitioning with automatic partition addition creates new segments and OMF datafiles automatically; DB_FILES limit not adjusted to account for interval partition growth; review V$DATAFILE count after interval partition-heavy ETL runs.
Solution: Pre-empt by sizing DB_FILES = 5 × expected maximum datafile count; consolidate multiple small datafiles into larger ones using DBMS_SPACE for capacity planning; consider bigfile tablespaces to reduce datafile count while maintaining size.
Meaning: A DDL operation requiring a full table lock (such as ALTER TABLE or TRUNCATE) cannot be executed because DML_LOCKS=0 disables all table-level locks.
Root Cause: DMA_LOCKS deliberately set to 0 to improve throughput (disables TM enqueue for DML), but this prevents DDL statements that require an exclusive table lock from executing; tradeoff between DML performance and DDL capability.
Solution: Temporarily enable table locks for the session if possible: LOCK TABLE table_name IN EXCLUSIVE MODE; for ALTER TABLE operations, set DML_LOCKS to a non-zero value in SPFILE and restart; alternatively schedule DDL during maintenance windows when DML_LOCKS can be briefly re-enabled.
Meaning: A LOCK TABLE statement failed because the target table has table locking explicitly disabled via ALTER TABLE DISABLE TABLE LOCK.
Root Cause: DBA or application previously disabled table locks (ALTER TABLE t DISABLE TABLE LOCK) to improve DML performance; this prevents all DDL and explicit LOCK TABLE commands on that object; commonly seen on high-throughput tables in OLTP systems.
Solution: Re-enable table locking: ALTER TABLE table_name ENABLE TABLE LOCK; verify which tables have locking disabled: SELECT OWNER, TABLE_NAME FROM DBA_TABLES WHERE TABLE_LOCK = 'DISABLED'; enable locks only during the maintenance window when DDL is needed, then disable again after.
Meaning: A session waiting for a lock during Parallel DML execution timed out after the PDML deadlock detection threshold was exceeded.
Root Cause: Two parallel DML operations on overlapping data sets; parallel slave processes locking the same row partitions in conflicting orders; lack of proper partitioning alignment causing cross-slave row contention during concurrent parallel INSERT/UPDATE.
Solution: Check V$PQ_SLAVE and V$SESSION for parallel slave lock waits; consider serial execution for conflicting DML: ALTER SESSION DISABLE PARALLEL DML; redesign parallel DML to operate on non-overlapping data partitions; use hash partitioning to align parallel slave assignments with partition boundaries.
Meaning: An OCI or JDBC application received the 100 status code (equivalent to ORA-01403 in PL/SQL) indicating the cursor fetch returned no rows.
Root Cause: Application not handling empty result sets; SELECT statement in application code returns zero rows; OCI programs must check the return code after OCIStmtFetch; JDBC programs must check ResultSet.next() before accessing column values.
Solution: In JDBC: wrap ResultSet access in if (rs.next()) { ... } else { handle empty }; in OCI: check the return value of OCIStmtFetch2 for OCI_NO_DATA; in Pro*C: WHENEVER NOT FOUND DO handle_empty; never assume a query will return rows without explicit verification.
Meaning: In a Shared Server (MTS) environment, all available shared server processes are blocked waiting for resources held by other shared servers, causing a system-wide deadlock.
Root Cause: Circular dependency between shared server sessions; one session holds a lock and waits for a server process that is held by another locked session; SHARED_SERVERS value too low for the workload; requests piling up in dispatcher queues.
Solution: Increase SHARED_SERVERS: ALTER SYSTEM SET SHARED_SERVERS=50; review DISPATCHERS and MAX_SHARED_SERVERS; identify blocking queries via V$SESSION and V$QUEUE; consider switching latency-sensitive or long-running transactions to dedicated server connections.
Meaning: The database is in NOARCHIVELOG mode with ARCHIVE LOG MANUAL set, requiring a DBA to manually archive redo logs before they can be reused.
Root Cause: ARCHIVE LOG MANUAL parameter set in older configurations; redo log cannot be reused until manually archived; database stalls on log switches until the DBA issues ARCHIVE LOG ALL; typically a legacy configuration issue.
Solution: Issue: ALTER SYSTEM ARCHIVE LOG ALL; to archive pending logs manually; to prevent future stalls, switch to automatic archiving: ALTER SYSTEM ARCHIVE LOG START; long term, evaluate enabling ARCHIVELOG mode for proper recoverability; NOARCHIVELOG with MANUAL archiving is an anti-pattern in production.
Meaning: Oracle could not open one or more members of an online redo log group during instance startup or log switch.
Root Cause: Redo log member file deleted or moved at OS level; filesystem containing redo logs unmounted or full; disk failure affecting redo log storage path; verify physical existence: ls -la /path/to/redo_log_member.log.
Solution: If other members in the group are intact: ALTER DATABASE CLEAR LOGFILE GROUP n; if the group is current and needed for recovery: restore from backup. Add multiplexed members: ALTER DATABASE ADD LOGFILE MEMBER '/new/path/redo.log' TO GROUP n; always maintain at least 3 multiplexed members across separate disk paths.
Meaning: All multiplexed members of an online redo log group are inaccessible, preventing Oracle from completing the instance startup or performing a log switch.
Root Cause: Entire storage volume containing redo logs went offline; SAN LUN unmounted; all redo members on same disk array that failed; ASM diskgroup containing redo logs dismounted; critical design failure — never store all redo members on the same physical storage.
Solution: If the group is not the current redo group and not needed for recovery: ALTER DATABASE CLEAR LOGFILE GROUP n; if recovery is needed from this group: mount database, apply available archivelogs up to gap, then RESETLOGS; always store redo members on separate physical storage paths to prevent this scenario.
Meaning: Oracle encountered an I/O error while writing to or reading from an online redo log member, potentially indicating storage subsystem failure.
Root Cause: Storage path failure (HBA, switch, SAN); filesystem full preventing redo log writes; NFS mount timeout for a redo log member on NFS storage; OS-level I/O error reported in dmesg or /var/log/messages.
Solution: Check OS logs: dmesg | grep -i error; if the affected member's group has other healthy members, drop the bad member: ALTER DATABASE DROP LOGFILE MEMBER '/bad/path/redo.log'; add a replacement: ALTER DATABASE ADD LOGFILE MEMBER '/healthy/path/redo.log' TO GROUP n; investigate and fix the underlying storage issue immediately.
Meaning: Oracle wrote redo data to the log but the number of blocks written did not match what was expected, indicating a partial write or storage error.
Root Cause: Storage subsystem acknowledged writes that were not fully committed to disk (write-behind caching without proper flush); disk firmware bug; battery-backed write cache failure; ensure storage write cache is properly configured with power-protected cache.
Solution: Verify storage controller write cache battery status; disable write caching on redo log storage if unbacked: hdparm -W0 /dev/sdX; for ASM, use FAILGROUP configuration to spread redo across failure domains; after fixing storage, restart instance and clear affected redo group.
Meaning: A redo log block header checksum failed, indicating the redo block is corrupt and Oracle cannot safely use this data for recovery.
Root Cause: Partial block write during crash (torn write); storage hardware issue; memory corruption during redo buffer flush; file system inconsistency; if this is a non-current archived log copy an intact member; if current log, the instance must abort.
Solution: If archived log: copy intact archived log member to replace corrupt copy; if online log with available alternate member: ALTER DATABASE CLEAR LOGFILE GROUP n (if not needed for recovery); run RMAN: RESTORE ARCHIVELOG FROM SCN n to recover from backup; if current log is corrupt, incomplete recovery may be necessary up to last good SCN.
Meaning: Oracle cannot access a datafile because it has been taken offline (deliberately or due to I/O error) and is not available for read operations.
Root Cause: Datafile automatically taken offline by Oracle after repeated I/O errors (in ARCHIVELOG mode); DBA manually took it offline; tablespace containing the file is offline; storage path for the file is unavailable.
Solution: Check status: SELECT FILE#, STATUS, NAME FROM V$DATAFILE WHERE STATUS != 'ONLINE'; if storage is restored: RMAN> RESTORE DATAFILE n; RECOVER DATAFILE n; ALTER DATABASE DATAFILE n ONLINE; if permanently unavailable and ARCHIVELOG mode enabled, restore from most recent backup.
Meaning: On a physical standby database, MRP attempted to apply a redo change to a datafile that is not present or is in an incompatible state.
Root Cause: New datafile added on primary but not yet received by standby; datafile added while redo gap exists on standby; standby missing the archived logs containing the file creation; check V$DATAFILE_HEADER on standby for files in UNNAMED state.
Solution: On standby: SELECT HXFIL, HXNM FROM X$KCVFH WHERE HXNM LIKE '%UNNAMED%'; restore missing datafile: RMAN> RESTORE DATAFILE n FROM SERVICE primary_service; then resume MRP: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION.
Meaning: The archiver process was too slow to archive an online redo log before Oracle needed to reuse it for the next log switch, causing an archiving failure.
Root Cause: Archive destination I/O throughput too low for the rate of log switches; archive destination disk under heavy load; too few redo log groups causing rapid reuse; LGWR writing faster than ARCH can copy logs to archive destination.
Solution: Add more redo log groups: ALTER DATABASE ADD LOGFILE GROUP n ('/path/redo.log') SIZE 500M; increase redo log size to slow down log switches; optimize archive destination I/O; add a second archive process: ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4; monitor log switch rate in AWR.
Meaning: Oracle detected that a write to a datafile was acknowledged by the storage system but the data was not actually persisted — a "lost write" or silent data corruption scenario.
Root Cause: Storage controller or firmware bug; SAN/NAS caching layer dropped a write without reporting an error; RAID controller battery failure causing incomplete cache flush; VM snapshot rollback causing file system state regression.
Solution: Enable DB_LOST_WRITE_PROTECT=TYPICAL or FULL to detect lost writes via redo log SCN validation; restore and recover the affected datafile from the last known good backup: RMAN> RESTORE DATAFILE n; RECOVER DATAFILE n; engage storage vendor to identify and fix the root cause in the storage stack.
Meaning: Oracle encountered an error while reading redo data during media or instance recovery, possibly from a corrupted or truncated redo log.
Root Cause: Archived log physically truncated or corrupted; log sequence gap preventing continuous redo application; attempting recovery using a redo log from a different database incarnation; check RMAN catalog for archivelog completeness.
Solution: Verify archivelog completeness: RMAN> LIST ARCHIVELOG ALL; restore missing archivelogs: RMAN> RESTORE ARCHIVELOG FROM SEQUENCE n; if log is truly corrupt, perform incomplete recovery: RECOVER DATABASE UNTIL SEQUENCE n THREAD 1; then OPEN RESETLOGS and take a full backup immediately.
Meaning: During media recovery, Oracle found corruption in an archived or online redo log near a specific block number and SCN, and cannot safely apply further redo changes beyond that point.
Root Cause: Archive log corruption from disk failure; log copied incompletely during an archive failure; NFS I/O interruption during archiving left the file truncated; the archived log file on disk differs from the original redo content.
Solution: Attempt recovery using multiplexed archive copies: RMAN> RESTORE ARCHIVELOG SEQUENCE n FROM COPY; if no valid copy exists, perform point-in-time recovery up to the last uncorrupted change: RECOVER DATABASE UNTIL CHANGE n; then OPEN RESETLOGS and validate with RMAN VALIDATE DATABASE.
Meaning: A fetch was performed on a cursor that has already been closed, exhausted, or whose result set was invalidated by a COMMIT or ROLLBACK issued while the cursor was open.
Root Cause: FOR UPDATE cursor invalidated after COMMIT within the loop; explicit cursor fetched after it was closed; ROLLBACK issued while a cursor was still open in PL/SQL; application JDBC code calling ResultSet.next() after ResultSet.close().
Solution: In PL/SQL FOR UPDATE cursors: do not COMMIT inside the loop; collect rows into a collection first (BULK COLLECT), then commit after the loop; in JDBC: never close a ResultSet and then call next(); always reopen cursors after transactions that modify cursor result sets.
Meaning: An EXECUTE or FETCH was attempted on a cursor that has not yet had a statement parsed (prepared) into it.
Root Cause: OCI or Pro*C application calling OCIStmtExecute or FETCH without first calling OCIStmtPrepare; DBMS_SQL.EXECUTE called before DBMS_SQL.PARSE; dynamic SQL cursor in wrong state; application code flow skipping the parse step in error handling path.
Solution: Always follow the correct cursor lifecycle: OPEN CURSOR → PARSE → BIND → EXECUTE → FETCH → CLOSE; in DBMS_SQL: v_cursor := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(v_cursor, sql_text, DBMS_SQL.NATIVE); add state validation before execute calls; use DBMS_SQL.IS_OPEN to verify cursor state.
Meaning: A DBMS_SQL.COLUMN_VALUE or DEFINE_COLUMN call referenced a column position that does not exist in the SELECT list of the parsed statement.
Root Cause: Column position index exceeds the number of columns in SELECT; DEFINE_COLUMN called with position 5 but SELECT only has 4 columns; dynamic SQL statement changed (fewer columns) but the define loop was not updated to match.
Solution: Always derive column count dynamically: DBMS_SQL.DESCRIBE_COLUMNS(v_cursor, v_col_count, v_col_desc_table); loop from 1 to v_col_count; never hardcode column positions in DBMS_SQL define calls; validate column count after parsing dynamic SQL before defining output variables.
Meaning: An operation referencing an ASM diskgroup by name failed because the diskgroup does not exist in the ASM instance or has not been mounted.
Root Cause: ASM diskgroup name typo in DB_CREATE_FILE_DEST; diskgroup was dropped or renamed; ASM instance restarted without auto-mounting the diskgroup; cluster rebalance operation moved disks leaving diskgroup in unmounted state.
Solution: Connect to ASM instance: sqlplus / as sysasm; check diskgroups: SELECT NAME, STATE FROM V$ASM_DISKGROUP; mount the diskgroup: ALTER DISKGROUP dgname MOUNT; verify DB_CREATE_FILE_DEST matches exact diskgroup name: SELECT VALUE FROM V$PARAMETER WHERE NAME='db_create_file_dest'.
Meaning: The database instance cannot mount because its ASM diskgroup (containing the datafiles) is not mounted in the ASM instance.
Root Cause: ASM instance started but diskgroup not mounted due to CSS vote disk failure; one or more ASM disks failed causing the diskgroup to go offline; CRS dependency order issue — database started before ASM diskgroup was fully mounted; normal disk redundancy broken.
Solution: On the ASM instance: ALTER DISKGROUP dgname MOUNT RESTRICTED; check disk status: SELECT NAME, PATH, STATE, MODE_STATUS FROM V$ASM_DISK; if disk missing: add replacement disk and rebalance: ALTER DISKGROUP dgname ADD DISK '/dev/sdX' REBALANCE POWER 11; monitor: SELECT * FROM V$ASM_OPERATION.
Meaning: An operation (datafile creation, file addition, rebalance) cannot proceed because the ASM diskgroup does not have enough free space to complete the allocation.
Root Cause: Diskgroup at or near capacity; NORMAL redundancy effectively doubles the space required; large datafile creation or RMAN backup to the diskgroup exceeded available space; rebalance operation requiring temporary additional space.
Solution: Check space: SELECT NAME, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB FROM V$ASM_DISKGROUP; add disks to increase capacity: ALTER DISKGROUP dgname ADD DISK '/dev/sdX'; delete obsolete RMAN backups: RMAN> DELETE OBSOLETE; archive old data to release space; monitor with alerts at 80% usage threshold.
Meaning: An ASM operation referenced a specific disk that is no longer found as a member of the diskgroup, indicating the disk has been removed or has failed.
Root Cause: Physical disk failure removing it from ASM visibility; disk accidentally removed from the disk discovery path (ASM_DISKSTRING misconfiguration); LUN revoked from the host by storage team without prior notification; UDEV rule change altered device path.
Solution: Check disk discovery: SELECT PATH, HEADER_STATUS FROM V$ASM_DISK WHERE GROUP_NUMBER=n; verify OS visibility: ls -la /dev/oracleasm/disks/; update ASM_DISKSTRING if paths changed: ALTER SYSTEM SET ASM_DISKSTRING='/dev/oracleasm/disks/*' SCOPE=BOTH; replace failed disk: ALTER DISKGROUP dgname ADD DISK '/new/disk' NAME new_disk_name.
Meaning: A disk cannot be added to the ASM diskgroup because it has reached the maximum number of disks allowed per diskgroup (511 for EXTERNAL, 504 for NORMAL, 336 for HIGH redundancy).
Root Cause: Diskgroup grown organically over years by adding many small LUNs; reached the Oracle ASM disk count limit; design should favor fewer, larger LUNs rather than many small ones to avoid this limit.
Solution: Create a new diskgroup for additional capacity; migrate data using RMAN: BACKUP DATABASE FORMAT '+NEW_DG/%U'; restore to new diskgroup; retire the old full diskgroup; for future: provision fewer larger ASM disks (1TB+) rather than many small ones to conserve disk slots.
Meaning: The ASM diskgroup's on-disk compatibility setting is higher than the COMPATIBLE.ASM attribute supported by the current ASM instance version.
Root Cause: Diskgroup created on a newer Oracle version with higher COMPATIBLE.ASM set; downgrade scenario; ASM software version rollback after diskgroup was upgraded; check: SELECT NAME, COMPATIBILITY, DATABASE_COMPATIBILITY FROM V$ASM_DISKGROUP.
Solution: Upgrade ASM instance software to match the diskgroup's COMPATIBLE.ASM setting; COMPATIBLE.ASM attribute cannot be lowered once set; if downgrade is required, create a new diskgroup with lower compatibility setting and migrate all data; always plan ASM compatibility settings before upgrading.
Meaning: An attempt to add a disk to an ASM diskgroup failed because that disk is already a member of a different diskgroup on the same or another host.
Root Cause: Disk LUN presented to multiple hosts; accidental reuse of an in-use LUN; disk header still shows membership from a previous diskgroup that was force-dropped; the disk has a valid ASM header from another diskgroup.
Solution: Check disk ownership: SELECT PATH, GROUP_NUMBER, NAME, HEADER_STATUS FROM V$ASM_DISK; to force use a disk from a dropped group: ALTER DISKGROUP dgname ADD DISK '/dev/sdX' FORCE; WARNING: only use FORCE if you are absolutely certain the disk is no longer needed by its previous diskgroup; never present the same LUN to multiple hosts simultaneously.
Meaning: A CREATE DISKGROUP statement failed because an ASM diskgroup with the specified name already exists in the ASM instance.
Root Cause: Attempting to create a diskgroup that already exists; idempotent script not checking existence first; diskgroup name collision in RAC environment where another node already created the group; disk header still contains the old diskgroup name after incomplete drop.
Solution: Check existing diskgroups: SELECT NAME, STATE FROM V$ASM_DISKGROUP; if diskgroup exists but is in DISMOUNTED state, mount it instead: ALTER DISKGROUP dgname MOUNT; if the old diskgroup must be replaced: ALTER DISKGROUP old_name DROP DISKS IN FAILGROUP name; then CREATE DISKGROUP with new disks; validate disk headers are clean before reuse.
Meaning: The ASM diskgroup cannot be fully mounted because one or more member disks are unavailable, leaving the diskgroup in an INCOMPLETE state.
Root Cause: Disk failure causing NORMAL or HIGH redundancy diskgroup to lose a failure group; too many simultaneous disk failures exceeding redundancy tolerance; LUN path failure in multi-path configuration; INCOMPLETE state means data may be at risk.
Solution: Check which disks are offline: SELECT PATH, FAILGROUP, MODE_STATUS, STATE FROM V$ASM_DISK WHERE GROUP_NUMBER=n; if disk is temporarily unavailable: fix the storage path and re-present the LUN; if disk is permanently failed: ALTER DISKGROUP dgname ADD DISK '/new/disk'; ASM will rebalance and restore redundancy; monitor: SELECT * FROM V$ASM_OPERATION.
Meaning: The ASM diskgroup has no remaining free space to fulfill a file allocation request, causing the operation to fail immediately.
Root Cause: Diskgroup completely full; RMAN backup filling ASM diskgroup; large datafile growth consuming remaining space; redundancy overhead consuming more space than anticipated; no AUTOEXTEND monitoring in place.
Solution: Immediate relief: delete obsolete RMAN backups: RMAN> DELETE OBSOLETE; long-term: add disks to the diskgroup: ALTER DISKGROUP dgname ADD DISK '/dev/sdX'; enable Oracle Cloud Control threshold alerts at 75%/85%/95% diskgroup utilization; review file sizes: SELECT SUM(BYTES)/1024/1024/1024 GB FROM V$ASM_FILE WHERE GROUP_NUMBER=n.
Meaning: An RMAN command requires an allocated channel of a specific device type (DISK or SBT_TAPE) but no such channel has been configured.
Root Cause: RMAN script missing ALLOCATE CHANNEL command; default channel type conflicts with backup piece location (backup on tape but only DISK channel allocated); CONFIGURE DEFAULT DEVICE TYPE not set to match backup location.
Solution: Allocate the correct channel type: RUN { ALLOCATE CHANNEL c1 DEVICE TYPE DISK; RESTORE DATABASE; } or for tape: ALLOCATE CHANNEL c1 DEVICE TYPE SBT_TAPE PARMS 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)'; configure default device: CONFIGURE DEFAULT DEVICE TYPE TO DISK; CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/%U'.
Meaning: RMAN attempted to back up the current controlfile but the database is in an incompatible state or the backup controlfile copy is already in use by another process.
Root Cause: Concurrent RMAN backup session already performing a controlfile backup; database in NOMOUNT state when controlfile backup attempted; RMAN CONFIGURE CONTROLFILE AUTOBACKUP ON but backup destination unavailable; controlfile snapshot in use by another process.
Solution: Ensure only one active RMAN backup is running: SELECT * FROM V$RMAN_BACKUP_JOB_DETAILS; wait for concurrent backup to complete or cancel it: DELETE NOPROMPT EXPIRED BACKUP; for manual controlfile backup: BACKUP CURRENT CONTROLFILE; for autobackup: CONFIGURE CONTROLFILE AUTOBACKUP ON; SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/%F'.
Meaning: RMAN restored a datafile but the header content does not match what the controlfile expects, indicating the wrong backup piece was used or the file was overwritten.
Root Cause: Wrong backup set selected for restore (e.g., backup from different database clone used accidentally); datafile restored to wrong location and another database's file already exists there; backup catalog out of sync with actual backup content.
Solution: Verify backup piece content: RMAN> LIST BACKUP OF DATAFILE n; validate the backup: RMAN> VALIDATE BACKUPSET n; restore with explicit backup set designation: RMAN> RESTORE DATAFILE n FROM BACKUPSET '/path/to/specific.bkp'; always verify database DBID matches: SELECT DBID FROM V$DATABASE before restoring.
Meaning: RMAN encountered more corrupt blocks than the configured MAXCORRUPT threshold during a backup or validation operation, causing the job to fail.
Root Cause: Physical corruption in datafile exceeding the tolerance level; RMAN CONFIGURE MAXSETSIZE or SET MAXCORRUPT limiting acceptable corruption; storage hardware degradation spreading block corruption across many blocks.
Solution: First identify corrupt blocks: RMAN> VALIDATE DATABASE; SELECT * FROM V$DATABASE_BLOCK_CORRUPTION; attempt block-level recovery: RMAN> RECOVER CORRUPTION LIST; if MAXCORRUPT threshold needs temporary raising: SET MAXCORRUPT FOR DATAFILE n TO 50; engage storage team to replace failing hardware; investigate with DBVERIFY: dbv file=datafile.dbf blocksize=8192 logfile=dbv_output.log.
Meaning: RMAN requires an exclusive lock on a datafile for a restore or recovery operation but cannot obtain it because another process holds a conflicting lock.
Root Cause: Another RMAN job is simultaneously restoring the same datafile; the database instance has the datafile open for normal use and an incompatible restore is requested; concurrent DUPLICATE or CLONE operation using the same datafile.
Solution: Verify no other RMAN sessions are active: SELECT SID, OPNAME, STATUS FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%'; check V$BACKUP for files in ACTIVE backup mode; cancel conflicting RMAN session if confirmed: ALTER SYSTEM KILL SESSION 'sid,serial#'; ensure only one RMAN instance restores each datafile at a time.
Meaning: RMAN attempted to access a backup set or backup piece that is not physically present at the expected location.
Root Cause: Backup piece deleted from disk/tape but RMAN catalog still references it; tape rotated out of storage library; backup piece moved to different path without updating catalog; backup expired but not removed from catalog with CROSSCHECK.
Solution: Run crosscheck to sync catalog: RMAN> CROSSCHECK BACKUP; mark expired pieces: RMAN> DELETE EXPIRED BACKUP; restore from an available earlier backup: RMAN> LIST BACKUP SUMMARY; LIST BACKUP OF DATABASE COMPLETED AFTER 'SYSDATE-7'; implement backup verification: RMAN> VALIDATE BACKUPSET n NOCHECKSUM; test restores regularly in a separate environment.
Meaning: An internal PL/SQL runtime error occurred that does not map to a specific PL/SQL exception; Oracle's PL/SQL engine encountered an unexpected condition.
Root Cause: PL/SQL program raising an exception that has no associated EXCEPTION_INIT pragma mapping; generic PROGRAM ERROR from a deep internal PL/SQL runtime condition; check alert log for associated ORA-600 or trace files; often accompanies other Oracle internal errors.
Solution: Add comprehensive EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); to expose the full stack; check alert log and trace files in $ORACLE_BASE/diag for associated internal errors; if recurring, open an Oracle Support SR with the full trace output.
Meaning: A PL/SQL function reached the end of its body or an exception handler without executing a RETURN statement, violating the requirement that functions must return a value.
Root Cause: IF/THEN/ELSIF chain does not cover all possible conditions and one branch exits without a RETURN; exception handler in the function does not include a RETURN; function has a RETURN in the normal path but not in the EXCEPTION WHEN OTHERS path.
Solution: Add a RETURN NULL; or RETURN default_value; as the final statement of the function body after all IF/CASE branches; add RETURN to all EXCEPTION handlers: EXCEPTION WHEN OTHERS THEN RETURN NULL; use compile-time checking; set PLSQL_WARNINGS='ENABLE:ALL' to catch missing RETURN paths at compile time.
Meaning: A strongly-typed REF CURSOR variable was assigned an open cursor whose column types or count do not match the cursor's declared return type.
Root Cause: Stored procedure returning a strongly-typed SYS_REFCURSOR with a specific rowtype, but the calling program expects different columns; query in the cursor body was modified but the cursor type declaration was not updated; type mismatch between %ROWTYPE definition and actual query columns.
Solution: Use weakly-typed SYS_REFCURSOR for maximum flexibility when column types may vary; for strongly-typed cursors: ensure the RETURN type's %ROWTYPE exactly matches the SELECT column list; test cursor type compatibility: OPEN v_cursor FOR SELECT * FROM target_table; verify after any table structure changes.
Meaning: Oracle cannot locate the PL/SQL procedure, function, or package that is being called, even though it was valid at parse time.
Root Cause: Package or procedure was dropped between the time the calling code was parsed and when it was executed; package specification was replaced, invalidating cached execution plans; object dropped and recreated in a different schema making the synonym stale.
Solution: Verify the called object exists: SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE OBJECT_NAME='PKG_NAME'; if synonym pointing to wrong schema: DROP SYNONYM s; CREATE SYNONYM s FOR correct_schema.pkg_name; recompile any invalidated dependents: EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'MYSCHEMA').
Meaning: A user-defined PL/SQL exception was raised with RAISE but was not caught by any EXCEPTION WHEN handler in the call stack, propagating to the top level.
Root Cause: Exception defined in one package raised in another without the caller having the correct exception reference; exception raised in a nested block propagating beyond the scope of any handler; missing WHEN OTHERS clause in the outermost exception handler.
Solution: Always define shared application exceptions in a dedicated package: PACKAGE app_exceptions AS e_validation EXCEPTION; PRAGMA EXCEPTION_INIT(e_validation, -20100); END; reference consistently: RAISE app_exceptions.e_validation; add WHEN app_exceptions.e_validation THEN log_and_handle; in outermost block always include WHEN OTHERS THEN handle_gracefully.
Meaning: An attempt was made to OPEN a cursor that is already in an OPEN state, which is not permitted in PL/SQL.
Root Cause: Code path opens the same cursor twice without closing it in between; exception handler re-opens a cursor that was already opened before the exception occurred; loop re-opens an explicit cursor without a corresponding CLOSE at the end of each iteration.
Solution: Always check cursor state before opening: IF NOT v_cursor%ISOPEN THEN OPEN v_cursor; END IF; ensure every OPEN has a matching CLOSE; in exception handlers: IF v_cursor%ISOPEN THEN CLOSE v_cursor; END IF; prefer implicit cursors (FOR loop) over explicit cursors to avoid manual state management.
Meaning: A procedure or function declared with PRAGMA AUTONOMOUS_TRANSACTION exited without committing or rolling back its autonomous transaction, causing Oracle to automatically roll it back.
Root Cause: Autonomous procedure performs DML but does not include a COMMIT or ROLLBACK before returning; exception in the autonomous procedure prevents reaching the COMMIT statement; design error where the autonomous transaction scope is misunderstood.
Solution: Ensure every code path in an autonomous procedure ends with COMMIT or ROLLBACK: BEGIN autonomous_dml; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE; END; common use cases: audit logging (always COMMIT), error logging (always COMMIT regardless of main transaction state); never use PRAGMA AUTONOMOUS_TRANSACTION for performance — only use it when true transactional independence is needed.
Meaning: DBMS_SCHEDULER.STOP_JOB was called on a job that is not currently in a running state, making the stop operation invalid.
Root Cause: Job already completed, failed, or was stopped before the STOP_JOB call was made; wrong job name specified; monitoring script calling STOP_JOB without first verifying job status with DBA_SCHEDULER_RUNNING_JOBS.
Solution: Check job state before stopping: SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME='MY_JOB'; verify running jobs: SELECT JOB_NAME, RUNNING_INSTANCE FROM DBA_SCHEDULER_RUNNING_JOBS; wrap in conditional: IF EXISTS (SELECT 1 FROM DBA_SCHEDULER_RUNNING_JOBS WHERE JOB_NAME='MY_JOB') THEN DBMS_SCHEDULER.STOP_JOB('MY_JOB'); END IF.
Meaning: A DBMS_SCHEDULER job of type EXECUTABLE (running an OS-level script or binary) failed to execute or returned a non-zero exit code.
Root Cause: Shell script not found at specified path; oracle OS user lacks execute permission on the script; script returned non-zero exit code indicating failure; OS resource limits prevent execution; path not accessible from Oracle's process environment.
Solution: Check job error details: SELECT LOG_DATE, STATUS, ADDITIONAL_INFO FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME='MY_JOB' ORDER BY LOG_DATE DESC; verify script path: SELECT PROGRAM_ACTION FROM DBA_SCHEDULER_PROGRAMS WHERE PROGRAM_NAME='MY_PROG'; ensure execute permission: chmod +x /path/to/script.sh; test manually as oracle user: su - oracle -c '/path/to/script.sh'.
Meaning: A DBMS_SCHEDULER operation (ENABLE, DISABLE, DROP, RUN, STOP) referenced a job name that does not exist in the database.
Root Cause: Typo in job name (scheduler names are case-sensitive by default); wrong schema context — job exists in SCHEMA_A but code connected as SCHEMA_B; job was dropped previously; job name requires schema prefix: 'SCHEMA.JOB_NAME'.
Solution: Verify job existence: SELECT OWNER, JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME=UPPER('my_job'); scheduler objects are stored in UPPERCASE by default: DBMS_SCHEDULER.RUN_JOB('SCHEMA_NAME.MY_JOB'); for cross-schema operations: DBMS_SCHEDULER.RUN_JOB(job_name => 'HR.PAYROLL_JOB', use_current_session => FALSE).
Meaning: DBMS_SCHEDULER.CREATE_JOB failed because a job with the same name already exists in the current schema.
Root Cause: Deployment script running CREATE_JOB without first dropping the existing job; idempotent deployment not handling job existence gracefully; job from a previous failed deployment left in BROKEN state but still registered.
Solution: Use defensive creation: BEGIN DBMS_SCHEDULER.DROP_JOB('MY_JOB', force => TRUE); EXCEPTION WHEN OTHERS THEN NULL; END; DBMS_SCHEDULER.CREATE_JOB(...); or check existence first: SELECT COUNT(*) INTO v_count FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME='MY_JOB'; IF v_count > 0 THEN DBMS_SCHEDULER.DROP_JOB('MY_JOB'); END IF; DBMS_SCHEDULER.CREATE_JOB(...).
Meaning: An operation referencing a DBMS_SCHEDULER window by name failed because that window does not exist in DBA_SCHEDULER_WINDOWS.
Root Cause: Window name typo; window was dropped or recreated with a different name; referencing a default Oracle-provided window (WEEKNIGHT_WINDOW, WEEKEND_WINDOW) that was accidentally dropped during maintenance.
Solution: List existing windows: SELECT WINDOW_NAME, ENABLED, DURATION FROM DBA_SCHEDULER_WINDOWS; recreate dropped default windows: EXEC DBMS_AUTO_TASK_ADMIN.RESTORE_WINDOW('WEEKNIGHT_WINDOW'); for custom windows: DBMS_SCHEDULER.CREATE_WINDOW(window_name => 'MAINT_WINDOW', duration => numtodsinterval(4,'hour'), repeat_interval => 'FREQ=DAILY;BYHOUR=22;BYMINUTE=0;BYSECOND=0').
Meaning: The current user does not have the required privileges to run, enable, disable, or manage the specified scheduler job.
Root Cause: Job owned by another schema; user lacks MANAGE SCHEDULER privilege; running a job in another user's schema requires CREATE ANY JOB or EXECUTE ON the job; non-DBA user attempting to run SYS-owned maintenance jobs.
Solution: Grant scheduler privileges: GRANT MANAGE SCHEDULER TO my_user; for specific job access: GRANT EXECUTE ON DBMS_SCHEDULER TO my_user; to allow cross-schema job management: GRANT CREATE ANY JOB TO my_user; check current privileges: SELECT PRIVILEGE FROM SESSION_PRIVS WHERE PRIVILEGE LIKE '%SCHEDULER%' OR PRIVILEGE LIKE '%JOB%'.
Meaning: A job was created or updated with a JOB_CLASS parameter referencing a scheduler job class that does not exist in DBA_SCHEDULER_JOB_CLASSES.
Root Cause: Custom job class dropped or renamed; typo in job class name; referencing DEFAULT_JOB_CLASS which is always available but mistyping its name; migration to new environment without recreating custom job classes that existed on the source.
Solution: List available job classes: SELECT JOB_CLASS_NAME, RESOURCE_CONSUMER_GROUP FROM DBA_SCHEDULER_JOB_CLASSES; create missing class: DBMS_SCHEDULER.CREATE_JOB_CLASS(job_class_name => 'BATCH_CLASS', resource_consumer_group => 'BATCH_GROUP', logging_level => DBMS_SCHEDULER.LOGGING_FULL); assign to job: DBMS_SCHEDULER.SET_ATTRIBUTE('MY_JOB', 'JOB_CLASS', 'BATCH_CLASS').
Meaning: The new password supplied does not meet the complexity requirements enforced by the profile's PASSWORD_VERIFY_FUNCTION.
Root Cause: Password too short; missing uppercase, number, or special character; password contains the username; Oracle's default ORA12C_STRONG_VERIFY_FUNCTION or custom verify function rejecting the password; verify function criteria documented in sys.verify_function_11g or custom implementation.
Solution: Check profile's verification function: SELECT PROFILE, LIMIT FROM DBA_PROFILES WHERE RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION' AND PROFILE='DEFAULT'; inspect the function logic to understand requirements; temporarily disable for service accounts: ALTER PROFILE app_profile LIMIT PASSWORD_VERIFY_FUNCTION NULL; then set a compliant password and re-enable the verification function.
Meaning: A custom PASSWORD_VERIFY_FUNCTION enforcing corporate password policy rejected the proposed password, returning a specific business rule error message.
Root Cause: Organization-specific password policy (e.g., no sequential characters, no dictionary words, minimum 3 character types) implemented in a custom verify function; password reuse history check embedded in custom function; blacklisted passwords list in the verification function.
Solution: Read the custom verify function: SELECT TEXT FROM DBA_SOURCE WHERE NAME=upper('custom_verify_fn') ORDER BY LINE; ensure the new password meets all criteria; work with security team to understand policy; for application service accounts use DBMS_RANDOM.STRING to generate compliant passwords; document password requirements in onboarding runbooks.
Meaning: The proposed new password was used previously by this account within the timeframe or count defined by PASSWORD_REUSE_TIME or PASSWORD_REUSE_MAX in the user's profile.
Root Cause: PASSWORD_REUSE_MAX=10 preventing reuse of the last 10 passwords; PASSWORD_REUSE_TIME=365 preventing reuse within 365 days; application service accounts attempting to cycle back to well-known passwords; automated rotation scripts cycling through a small password set.
Solution: User must choose a genuinely new password not in their history; check profile limits: SELECT LIMIT FROM DBA_PROFILES WHERE RESOURCE_NAME IN ('PASSWORD_REUSE_TIME','PASSWORD_REUSE_MAX') AND PROFILE='DEFAULT'; for service account rotation: use a password vault (CyberArk, HashiCorp Vault) that tracks and generates truly unique passwords each rotation cycle.
Meaning: An attempt to connect as SYS without specifying the AS SYSDBA or AS SYSOPER clause was rejected; SYS cannot connect with normal user authentication.
Root Cause: Application connection string using username=SYS without the SYSDBA or SYSOPER privilege qualifier; JDBC URL with user=SYS but missing the privilege role designation; monitoring tool configured with SYS credentials but without proper connection mode.
Solution: Correct connection syntax: CONNECT SYS/password AS SYSDBA; in JDBC: Properties props = new Properties(); props.setProperty("user","sys"); props.setProperty("password","pwd"); props.setProperty("internal_logon","sysdba"); best practice: never use SYS for application connections; create a dedicated monitoring/application user with only required privileges.
Meaning: ALTER USER EXPIRE ACCOUNT or ALTER USER PASSWORD EXPIRE was attempted on an externally-authenticated (OS auth) or globally-authenticated (LDAP/Kerberos) account, which is not supported.
Root Cause: User created with IDENTIFIED EXTERNALLY or IDENTIFIED GLOBALLY; password management operations on these accounts are controlled by the external authentication mechanism (OS, LDAP, Active Directory), not Oracle; scripted password rotation tool targeting all users including external ones.
Solution: Filter out external/global accounts in maintenance scripts: SELECT USERNAME FROM DBA_USERS WHERE AUTHENTICATION_TYPE NOT IN ('EXTERNAL','GLOBAL','NONE') before applying password operations; manage external account lifecycle through the external system (AD, LDAP); use AUTHENTICATION_TYPE column in DBA_USERS to identify account type.
Meaning: This is a warning message, not a hard error; it alerts the connecting user that their password will expire within a configured grace period.
Root Cause: PASSWORD_LIFE_TIME in the user profile is approaching expiration; PASSWORD_GRACE_TIME allows continued login with a warning; typically appears in application logs when the warning threshold is reached; many applications treat this warning as a connection error and fail unexpectedly.
Solution: Applications should handle ORA-28011 as a warning, not an error; for service accounts: set PASSWORD_LIFE_TIME=UNLIMITED in their profile or implement automated rotation before expiry; proactively monitor: SELECT USERNAME, EXPIRY_DATE FROM DBA_USERS WHERE EXPIRY_DATE BETWEEN SYSDATE AND SYSDATE+30; alert DBA team when expiry within 30 days.
Meaning: The MAX_ENABLED_ROLES initialization parameter limit has been reached; no additional roles can be enabled for the user's session.
Root Cause: User granted many individual roles; role nesting adding indirect role grants counting toward the limit; MAX_ENABLED_ROLES parameter set to the default (148) which sounds high but can be reached with deeply nested roles; privilege proliferation over years of application growth.
Solution: Check current utilization: SELECT COUNT(*) FROM SESSION_ROLES; check Max: SELECT VALUE FROM V$PARAMETER WHERE NAME='max_enabled_roles'; increase: ALTER SYSTEM SET MAX_ENABLED_ROLES=200 SCOPE=SPFILE; consolidate roles: audit and merge individual role grants into application-level composite roles; run privilege analysis: DBMS_PRIVILEGE_CAPTURE to find unused privileges.
Meaning: A Virtual Private Database (VPD) policy function returned an invalid predicate string that Oracle cannot apply to the SQL WHERE clause.
Root Cause: VPD policy function returning a malformed SQL predicate (syntax error); function returning NULL in a context that requires a non-null predicate; predicate referencing columns that don't exist in the protected table; complex predicate exceeding size limits.
Solution: Debug the policy function: SELECT DBMS_RLS.get_policy_predicate('SCHEMA','TABLE','FUNCTION') FROM DUAL; test the predicate standalone in SQL; ensure function returns valid SQL WHERE clause text or empty string '': IF condition THEN RETURN '1=0'; ELSE RETURN 'dept_id = ' || SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER'); END IF; validate predicate syntax before returning.
Meaning: The partition name specified in a CREATE TABLE PARTITION clause or ALTER TABLE operation contains invalid characters or violates Oracle naming conventions.
Root Cause: Partition name starting with a number; special characters (hyphen, space, slash) used without double-quoting; partition name exceeding 128 characters; using an Oracle reserved word as an unquoted partition name.
Solution: Use valid Oracle identifiers: start with a letter, use only letters/numbers/underscores; prefix date-based partition names: P_2024_01, P_JAN_2024; for special characters use double quotes (not recommended for maintenance): PARTITION "2024-01"; prefer descriptive names like: PARTITION p_sales_2024q1 VALUES LESS THAN (DATE '2024-04-01').
Meaning: A new range partition being added has a boundary value that is less than or equal to the highest existing partition boundary, violating the requirement that partition ranges must be in ascending order.
Root Cause: ALTER TABLE ADD PARTITION specifying a VALUES LESS THAN bound that falls within or before an existing partition range; partition list built in wrong order during initial CREATE TABLE; attempting to insert a historical partition after a MAXVALUE partition already exists.
Solution: Check current highest boundary: SELECT PARTITION_NAME, HIGH_VALUE FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='MY_TABLE' ORDER BY PARTITION_POSITION DESC FETCH FIRST 1 ROW ONLY; add partition with bound STRICTLY greater than the current maximum: ALTER TABLE MY_TABLE ADD PARTITION p_2025_01 VALUES LESS THAN (DATE '2025-02-01').
Meaning: A partition boundary value specified in VALUES LESS THAN or VALUES IN is too large to fit in the datatype of the partition key column.
Root Cause: Partition key is NUMBER(6) but boundary value exceeds 999999; VARCHAR2(10) partition key with a boundary value longer than 10 characters; number literal in partition bound exceeding the precision of the partitioning column definition.
Solution: Verify column datatype: SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION FROM DBA_TAB_COLUMNS WHERE TABLE_NAME='MY_TABLE' AND COLUMN_NAME='PARTITION_KEY'; ensure boundary values fit within column constraints; if column is too small: ALTER TABLE ... MODIFY column_name NUMBER(10) before adding the partition.
Meaning: An ALTER TABLE MODIFY on a column that is used as a partition key was rejected because changing its datatype or length would invalidate the existing partition definitions.
Root Cause: Attempting to change VARCHAR2(50) to VARCHAR2(100) on a partition key column; trying to change NUMBER to VARCHAR2 on the partitioning column; partition boundary values are stored against the column type and cannot be revalidated after type change.
Solution: Workaround requires table recreation: Create new table with desired column definition and correct partitioning; use INSERT INTO new_table SELECT * FROM old_table; rename tables; alternatively use CREATE TABLE AS SELECT with the new schema; this is a significant operation — plan for downtime or online redefinition using DBMS_REDEFINITION.
Meaning: An ALTER TABLE EXCHANGE PARTITION command failed because the non-partitioned table being swapped in contains rows whose partition key values do not fall within the target partition's boundary.
Root Cause: Staging table for partition exchange contains data from multiple partition ranges mixed together; data in the staging table includes values that belong to adjacent partitions; loading process did not filter data to exactly match the target partition's boundary.
Solution: Validate before exchange: SELECT COUNT(*) FROM staging_table WHERE partition_key NOT BETWEEN lower_bound AND upper_bound; pre-filter: DELETE FROM staging_table WHERE partition_key < lower_bound OR partition_key >= upper_bound; use WITHOUT VALIDATION only if you are 100% certain data is valid and want to skip the check for performance: ALTER TABLE pt EXCHANGE PARTITION p1 WITH TABLE staging INCLUDING INDEXES WITHOUT VALIDATION.
Meaning: The LGWR or ARCn process on the primary database failed to archive a redo log to the Data Guard standby destination.
Root Cause: Network interruption between primary and standby; RFS process on standby not running; standby destination disk full; archive destination set as MANDATORY causing primary to stall; standby database not open in MOUNT state to receive logs.
Solution: Check transport status: SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE TARGET='STANDBY'; verify standby availability: ping standby_host; check standby alert log: tail -f $ORACLE_BASE/diag/rdbms/dbname/instance/trace/alert_*.log; if MANDATORY is stalling primary: ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER; resolve standby issue; re-enable: ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE.
Meaning: The Data Guard redo transport service was disabled, causing real-time redo shipping to the standby to stop, increasing the potential data loss window.
Root Cause: LOG_ARCHIVE_DEST_STATE_n parameter changed to DEFER or RESET during maintenance; Data Guard Broker disabled transport due to repeated failures; standby was deliberately disabled; archive destination error exceeded retry threshold causing auto-deferral.
Solution: Re-enable transport: ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; via Data Guard Broker: DGMGRL> EDIT DATABASE 'standby_db' SET STATE='APPLY-ON'; check broker status: DGMGRL> SHOW CONFIGURATION; verify transport: SELECT DEST_ID, ARCHIVER, STATUS, TARGET FROM V$ARCHIVE_DEST_STATUS WHERE STATUS='VALID'.
Meaning: The Data Guard Broker cannot establish a connection to the primary database using the configured connection identifier.
Root Cause: DG_CONFIG or DB_UNIQUE_NAME mismatch; LOG_ARCHIVE_DEST_n USING clause pointing to wrong service name; primary database listener not running; Data Guard Broker configuration file (drc*.dat) corrupted or referencing stale connection strings.
Solution: Verify primary connection string: DGMGRL> SHOW DATABASE VERBOSE 'primary_db'; update connection identifier: DGMGRL> EDIT DATABASE 'primary_db' SET PROPERTY DGConnectIdentifier='primary_service'; test connection: tnsping primary_service from standby host; re-enable Broker: ALTER SYSTEM SET DG_BROKER_START=TRUE; verify: SELECT VALUE FROM V$PARAMETER WHERE NAME='dg_broker_start'.
Meaning: A DGMGRL or Broker API call failed because the Data Guard Monitor process (DMON) has not yet fully initialized or started.
Root Cause: DG_BROKER_START=TRUE but DMON process still initializing after database startup; DMON crashed and is restarting; connect to DGMGRL too quickly after database open; broker configuration file locked or corrupt preventing DMON startup.
Solution: Wait 60-90 seconds after database startup before connecting to DGMGRL; check DMON status: ps -ef | grep dmon; verify in alert log: grep -i dmon $ORACLE_BASE/diag/.../alert_*.log; restart DMON: ALTER SYSTEM SET DG_BROKER_START=FALSE; ALTER SYSTEM SET DG_BROKER_START=TRUE; check broker config file permissions: ls -la $ORACLE_BASE/dbs/dr*.dat.
Meaning: A Data Guard Broker operation (switchover, failover, reinstate) was canceled before completion, leaving the configuration in an intermediate or inconsistent state.
Root Cause: Switchover timeout due to active sessions preventing clean switchover; network interruption during the operation; operator pressed Ctrl+C during DGMGRL command; DMON process timeout during complex multi-step broker operation.
Solution: Check configuration state: DGMGRL> SHOW CONFIGURATION VERBOSE; if switchover is in-progress: DGMGRL> SHOW DATABASE VERBOSE 'db_name' to see current state; resolve TRANSPORT LAG and APPLY LAG; retry the operation: DGMGRL> SWITCHOVER TO 'standby_db'; if state is TRANSIENT ERROR: DGMGRL> EDIT DATABASE 'db_name' SET STATE='APPLY-ON'; always disconnect all application sessions before initiating planned switchover.
Meaning: The Managed Recovery Process (MRP) stopped applying redo because it encountered a missing archived log in the sequence, creating a gap between the last applied log and the next available one.
Root Cause: Missing archivelog on standby due to network interruption during transport; archivelog purged from primary before being shipped to standby; standby's archive destination ran out of space causing logs to be skipped; FETCH ARCHIVE LOG FROM SERVICE configuration issue.
Solution: Identify gap: SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP; fetch missing logs from primary: RMAN> RECOVER DATABASE; (connected to standby) or: ALTER DATABASE REGISTER LOGFILE '/path/to/archived_log.arc'; restart MRP: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; enable automatic fetch: LOG_ARCHIVE_DEST_n='...' VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE) DB_UNIQUE_NAME=...' ASYNC NOAFFIRM.
Meaning: An operation was attempted that is not valid for the current container context — either a PDB-only operation attempted from CDB$ROOT or a CDB-only operation attempted from a PDB.
Root Cause: Attempting CREATE PLUGGABLE DATABASE from within a PDB session; trying ALTER SYSTEM operations that only apply to the CDB root from a PDB; application code designed for non-CDB databases executed in a CDB/PDB environment without modification.
Solution: Verify current container: SHOW CON_NAME; to switch to root: ALTER SESSION SET CONTAINER=CDB$ROOT; for PDB-specific operations: ALTER SESSION SET CONTAINER=my_pdb; document which operations require root vs. PDB context; create wrapper procedures that automatically switch container context for mixed-environment scripts.
Meaning: A CREATE PLUGGABLE DATABASE or related operation failed because the required datafile path specification is missing or contains an invalid path.
Root Cause: FILE_NAME_CONVERT clause missing when PDB_FILE_NAME_CONVERT is not set and DB_CREATE_FILE_DEST is not configured; target directory for PDB files does not exist on the OS; path specified in FILE_NAME_CONVERT not accessible to the oracle user.
Solution: Specify FILE_NAME_CONVERT explicitly: CREATE PLUGGABLE DATABASE new_pdb ADMIN USER admin IDENTIFIED BY pwd FILE_NAME_CONVERT=('/source/path/','/target/path/'); or pre-configure: ALTER SYSTEM SET DB_CREATE_FILE_DEST='/oradata'; verify target directory exists: mkdir -p /oradata/new_pdb && chown oracle:oinstall /oradata/new_pdb.
Meaning: An attempt to add a property (e.g., service name, default tablespace) to a PDB failed because that property is already defined.
Root Cause: Duplicate service registration; PDB already has a service with the same name; idempotent PDB configuration script not checking property existence before adding; re-running a PDB setup script on an already-configured PDB.
Solution: Check existing PDB properties: SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'PDB%'; for services: SELECT NAME FROM DBA_SERVICES WHERE PDB='MY_PDB'; use defensive coding: BEGIN DBMS_SERVICE.CREATE_SERVICE(...); EXCEPTION WHEN OTHERS THEN IF SQLCODE = -65007 THEN NULL; ELSE RAISE; END IF; END.
Meaning: A security operation in a PDB was blocked because the CDB-level mandatory profile has not been configured, and the PDB requires it for compliance.
Root Cause: CDB administrator has not set the mandatory profile: ALTER PROFILE mandatory_profile LIMIT ...; EXEC DBMS_MACADM.CREATE_MANDATORY_SECURITY_POLICY(...); PDB attempting a user creation operation that requires a baseline security profile that the CDB hasn't defined yet.
Solution: Create and set mandatory profile in CDB$ROOT: CREATE PROFILE cdb_mandatory_profile LIMIT PASSWORD_LIFE_TIME 180 PASSWORD_REUSE_TIME 365 FAILED_LOGIN_ATTEMPTS 5; execute from CDB$ROOT as DBA with CDB privileges; verify: SELECT PROFILE FROM DBA_PROFILES WHERE PROFILE='CDB_MANDATORY_PROFILE'; apply: ALTER SYSTEM SET MANDATORY_USER_PROFILE=CDB_MANDATORY_PROFILE SCOPE=BOTH.
Meaning: An ALTER PLUGGABLE DATABASE OPEN command failed because the target PDB is already in an OPEN READ WRITE or OPEN READ ONLY state.
Root Cause: Duplicate OPEN command in a startup script without status check; CRS auto-start policy already opened the PDB before the DBA's manual command; monitoring script issuing OPEN without verifying current state.
Solution: Check PDB state before opening: SELECT CON_NAME, OPEN_MODE FROM V$PDBS; use conditional open: BEGIN FOR p IN (SELECT CON_NAME FROM V$PDBS WHERE OPEN_MODE='MOUNTED') LOOP EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE '||p.CON_NAME||' OPEN'; END LOOP; END; avoid hardcoded unconditional OPEN in startup scripts.
Meaning: A DDL or configuration command was rejected because the Pluggable Database is in an incompatible state (RESTRICTED, READ ONLY, or MOUNTED) for the requested operation.
Root Cause: PDB opened in READ ONLY mode but application attempting DML or DDL; PDB in RESTRICTED mode blocking normal user connections; PDB in MOUNTED state when a command requiring OPEN is issued; standby PDB not eligible for write operations.
Solution: Check PDB state: SELECT CON_NAME, OPEN_MODE, RESTRICTED FROM V$PDBS WHERE CON_NAME='MY_PDB'; to open for read/write: ALTER PLUGGABLE DATABASE my_pdb CLOSE; ALTER PLUGGABLE DATABASE my_pdb OPEN READ WRITE; to remove restricted mode: ALTER PLUGGABLE DATABASE my_pdb OPEN READ WRITE RESETLOGS; verify RESTRICTED status: SELECT RESTRICTED FROM V$PDBS WHERE CON_NAME='MY_PDB'.
Meaning: A user attempted to connect to a PDB that is currently in MOUNTED (not open) state, causing the connection to be rejected.
Root Cause: CDB restarted but the PDB's SAVE STATE configuration not set to auto-open after restart; PDB manually closed for maintenance; PDB failed to auto-open due to a datafile issue; CRS startup dependency not configured to open the PDB.
Solution: Open the PDB: ALTER PLUGGABLE DATABASE my_pdb OPEN READ WRITE; to persist across restarts: ALTER PLUGGABLE DATABASE my_pdb SAVE STATE; verify: SELECT CON_NAME, OPEN_MODE FROM V$PDBS; configure CRS-based auto-open: srvctl modify database -db cdb_name -startoption OPEN; all PDBs with SAVE STATE will auto-open when the CDB starts.
Meaning: ALTER PLUGGABLE DATABASE CLOSE was attempted but active transactions or connected sessions in the PDB are preventing a clean closure.
Root Cause: Active uncommitted transactions in the PDB; connected application sessions holding open cursors or transactions; CLOSE IMMEDIATE mode not specified; monitoring or pooled connections left open in the PDB.
Solution: For clean close: ALTER PLUGGABLE DATABASE my_pdb CLOSE IMMEDIATE; to forcibly kill sessions and close: ALTER PLUGGABLE DATABASE my_pdb CLOSE ABORT; identify active sessions: SELECT SID, SERIAL#, USERNAME FROM V$SESSION WHERE CON_ID=(SELECT CON_ID FROM V$PDBS WHERE NAME='MY_PDB') AND STATUS='ACTIVE'; kill them first: ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE.
Meaning: An attempt to plug in a PDB XML manifest failed because the PDB GUID or DBID matches an existing PDB in the current CDB, indicating it is being plugged into the same CDB it came from.
Root Cause: Unplug/plug workflow used incorrectly — plugging the same PDB back into the same CDB without first dropping it; accidental plug-in of the same PDB to the source CDB instead of the target CDB; test script ran on wrong CDB environment.
Solution: Verify you are on the correct target CDB: SELECT NAME, DB_UNIQUE_NAME FROM V$DATABASE; check existing PDB GUIDs: SELECT CON_UID, NAME FROM V$PDBS; if the PDB needs to be refreshed in the same CDB: ALTER PLUGGABLE DATABASE my_pdb CLOSE; DROP PLUGGABLE DATABASE my_pdb KEEP DATAFILES; then CREATE PLUGGABLE DATABASE my_pdb USING '/path/to/manifest.xml' NOCOPY.
Meaning: An ALTER PLUGGABLE DATABASE ALL OPEN command could not open all PDBs because some PDBs have resource conflicts, are assigned to specific instances, or have errors preventing startup.
Root Cause: In RAC CDB, some PDBs are configured to open only on specific instances; PDB has a corrupt datafile preventing open; insufficient SGA to support all PDBs simultaneously; PDB was flagged for restricted access due to previous error on startup.
Solution: Open PDBs individually to identify which one is failing: ALTER PLUGGABLE DATABASE my_pdb OPEN; check specific PDB errors: SELECT NAME, CAUSE, MESSAGE FROM PDB_PLUG_IN_VIOLATIONS WHERE STATUS='PENDING'; check alert log for each failed PDB open; in RAC: ALTER PLUGGABLE DATABASE pdb_name OPEN INSTANCES=ALL; review per-PDB memory usage: SELECT CON_ID, PHYSICAL_MEMORY_BYTES FROM V$PGASTAT WHERE NAME='aggregate PGA target parameter'.
Meaning: A DML or query operation failed because the tablespace containing the target object has been taken offline and is not accessible.
Root Cause: DBA took the tablespace offline for maintenance (ALTER TABLESPACE t OFFLINE); automatic offline due to datafile I/O errors (in ARCHIVELOG mode); tablespace recovery in progress; partial database restore operation in flight.
Solution: Check tablespace status: SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES; to bring online after maintenance: ALTER TABLESPACE my_ts ONLINE; if datafile recovery needed: RMAN> RESTORE DATAFILE n; RECOVER DATAFILE n; ALTER DATABASE DATAFILE n ONLINE; then: ALTER TABLESPACE my_ts ONLINE; verify: SELECT FILE#, STATUS FROM V$DATAFILE WHERE TS#=(SELECT TS# FROM V$TABLESPACE WHERE NAME='MY_TS').
Meaning: A tablespace was automatically taken offline by Oracle after multiple I/O errors on its datafiles, making all objects in that tablespace inaccessible until recovery is completed.
Root Cause: Storage LUN failure causing repeated I/O errors; Oracle automatically offlines the tablespace in ARCHIVELOG mode to protect data integrity while allowing the rest of the database to continue; FORCE offline occurs when NOARCHIVELOG mode can't offline gracefully.
Solution: Restore the storage LUN or replace the failed disk; restore the datafile from RMAN backup: RMAN> SQL 'ALTER DATABASE DATAFILE n OFFLINE'; RESTORE DATAFILE n; RECOVER DATAFILE n; SQL 'ALTER DATABASE DATAFILE n ONLINE'; ALTER TABLESPACE my_ts ONLINE; validate: SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE STATUS != 'ONLINE'.
Meaning: A CREATE TABLESPACE statement failed because a tablespace with the same name already exists in the database dictionary.
Root Cause: Idempotent DDL script not checking existence before creation; tablespace created in a previous incomplete deployment attempt; different schema design assumes tablespace needs creation but it already exists from a prior migration.
Solution: Check existing tablespaces: SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM DBA_TABLESPACES; use defensive script: DECLARE v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='MY_TS'; IF v_count=0 THEN EXECUTE IMMEDIATE 'CREATE TABLESPACE my_ts DATAFILE ...'; END IF; END; /; or simply verify and skip if exists.
Meaning: The active undo segment could not extend into the undo tablespace because no free space is available, causing the current transaction to fail.
Root Cause: Undo tablespace full; long-running transactions consuming all undo space preventing older undo from expiring; AUTOEXTEND disabled on undo datafile; UNDO_RETENTION set too high, keeping undo data longer than needed, filling the tablespace.
Solution: Immediate: check undo tablespace usage: SELECT TABLESPACE_NAME, SUM(BLOCKS)*8192/1024/1024 MB_USED FROM DBA_UNDO_EXTENTS WHERE STATUS='ACTIVE' GROUP BY TABLESPACE_NAME; add a datafile: ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/oradata/undotbs02.dbf' SIZE 4G AUTOEXTEND ON NEXT 512M MAXSIZE 20G; long-term: tune UNDO_RETENTION and commit large DML in batches.
Meaning: A large batch UPDATE or DELETE operation generated undo data faster than space could be reclaimed from expired undo extents, exhausting the undo tablespace.
Root Cause: Single transaction updating millions of rows without intermediate commits; bulk load deleting entire tables in one shot; undo consumption rate exceeds the rate of undo space reclamation; no AUTOEXTEND configured to handle burst operations.
Solution: Break bulk DML into smaller batches: FOR i IN 1..total_rows/10000 LOOP UPDATE table SET col=val WHERE rownum <= 10000 AND col != val; COMMIT; END LOOP; enable AUTOEXTEND: ALTER DATABASE DATAFILE '/undo/undotbs01.dbf' AUTOEXTEND ON MAXSIZE UNLIMITED; monitor undo during bulk: SELECT USUSED_UBLK, USLESS_UBLK FROM V$ROLLSTAT during the operation.
Meaning: A sort or temporary operation exhausted the maximum number of extents allowed for a temporary segment in a dictionary-managed temporary tablespace.
Root Cause: Legacy DICTIONARY-managed temporary tablespace with MAXEXTENTS set; sort operation requiring more temp space than MAXEXTENTS allows; pre-Oracle9i tablespace configuration that should be upgraded to locally managed.
Solution: Migrate to locally managed temporary tablespace: CREATE TEMPORARY TABLESPACE temp_new TEMPFILE '/oradata/temp02.dbf' SIZE 4G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_new; DROP TABLESPACE temp OLD INCLUDING CONTENTS AND DATAFILES; locally managed tablespaces have no MAXEXTENTS limit.
Meaning: A local partitioned index partition cannot grow because the tablespace designated for that index partition has no free space.
Root Cause: Index tablespace for a specific partition grew full; AUTOEXTEND disabled; each partition of a local partitioned index stored in its own tablespace, and one of them ran out of space; common in range-partitioned tables with per-partition tablespace allocation.
Solution: Identify the full tablespace: SELECT INDEX_NAME, PARTITION_NAME, TABLESPACE_NAME FROM DBA_IND_PARTITIONS WHERE STATUS='UNUSABLE' OR INDEX_NAME='MY_IDX'; add space: ALTER TABLESPACE idx_ts_q1 ADD DATAFILE '/oradata/idx_ts_q1_02.dbf' SIZE 2G AUTOEXTEND ON; then rebuild the partition: ALTER INDEX my_idx REBUILD PARTITION q1_part.
Meaning: A datafile with AUTOEXTEND ON reached its MAXSIZE limit and cannot grow further, or the underlying filesystem has no remaining free space.
Root Cause: MAXSIZE parameter reached (common when MAXSIZE set conservatively); OS filesystem 100% full; ASM diskgroup full; LVM volume group at capacity; tablespace growth rate exceeded capacity planning assumptions.
Solution: Increase MAXSIZE: ALTER DATABASE DATAFILE '/oradata/data01.dbf' AUTOEXTEND ON MAXSIZE 32G; or add a new datafile: ALTER TABLESPACE users ADD DATAFILE '/oradata/data02.dbf' SIZE 4G AUTOEXTEND ON NEXT 512M MAXSIZE 30G; check filesystem: df -h /oradata; check ASM: SELECT GROUP_NUMBER, FREE_MB FROM V$ASM_DISKGROUP; set up monitoring alerts at 80% capacity.
Meaning: ALTER DATABASE DATAFILE RESIZE to a smaller value failed because the new size would be below the high water mark of the datafile — some blocks below the requested size contain data.
Root Cause: Data blocks exist near the end of the datafile preventing shrinkage; deleted rows freed logical space but did not move the high water mark; large objects previously stored at high block numbers; the datafile cannot be shrunk below its current high water mark without data reorganization.
Solution: Find the minimum safe size: SELECT CEIL(((SELECT MAX(BLOCK_ID+BLOCKS-1) FROM DBA_EXTENTS WHERE FILE_ID=file_number)+1)*8192/1048576) MIN_MB FROM DUAL; reorganize to lower high water mark: ALTER TABLE t MOVE TABLESPACE ts; ALTER INDEX i REBUILD; then retry: ALTER DATABASE DATAFILE '/path/file.dbf' RESIZE 2G; use DBMS_SPACE.ISDATAFILERESIZABLE to check feasibility.
Meaning: An ALTER TABLE operation (DROP PARTITION, EXCHANGE PARTITION, TRUNCATE PARTITION) referenced a partition name that does not exist in the table's partition definition.
Root Cause: Partition naming convention mismatch; partition was previously dropped and the script still references it; automated partition maintenance script using a stale partition list; wrong table name in the ALTER TABLE statement.
Solution: List existing partitions: SELECT PARTITION_NAME, HIGH_VALUE, PARTITION_POSITION FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='MY_TABLE' ORDER BY PARTITION_POSITION; use dynamic SQL in partition maintenance scripts: SELECT PARTITION_NAME INTO v_pname FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='MY_TABLE' AND HIGH_VALUE LIKE '%2024-01-01%'; never hardcode partition names in scripts — always derive from DBA_TAB_PARTITIONS dynamically.
Meaning: An ENQUEUE, DEQUEUE, or queue administration operation referenced a queue table that does not exist in the current schema.
Root Cause: Queue table not created before attempting to create the queue on it; wrong schema qualifier; queue table dropped or schema migrated without recreating AQ infrastructure; deployment script executed in wrong order (queue before queue table).
Solution: Create queue table first: DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table => 'MY_QUEUE_TABLE', queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE', multiple_consumers => TRUE); then create queue: DBMS_AQADM.CREATE_QUEUE(queue_name => 'MY_QUEUE', queue_table => 'MY_QUEUE_TABLE'); verify: SELECT QUEUE_TABLE, QUEUE_NAME FROM ALL_QUEUES WHERE OWNER='MY_SCHEMA'.
Meaning: DBMS_AQADM.ADD_SUBSCRIBER failed because the specified subscriber already has an active subscription to the target queue.
Root Cause: Idempotent deployment script calling ADD_SUBSCRIBER without checking if the subscription already exists; application restart triggering duplicate subscription registration; microservice architecture where multiple pods each try to subscribe independently.
Solution: Use defensive subscription: BEGIN DBMS_AQADM.ADD_SUBSCRIBER(queue_name => 'MY_QUEUE', subscriber => SYS.AQ$_AGENT('MY_APP', NULL, NULL)); EXCEPTION WHEN OTHERS THEN IF SQLCODE = -24034 THEN NULL; ELSE RAISE; END IF; END; verify subscriptions: SELECT NAME, ADDRESS FROM DBA_QUEUE_SUBSCRIBERS WHERE QUEUE_NAME='MY_QUEUE'.
Meaning: The DEQUEUE_OPTIONS.deq_condition parameter contains an invalid SQL WHERE clause fragment that cannot be used to select a specific message from the queue.
Root Cause: SQL syntax error in the dequeue condition string; referencing columns that do not exist in the queue table; using Oracle SQL features not supported in queue dequeue conditions; missing proper qualification of user-data payload columns with 'tab.user_data.' prefix.
Solution: Dequeue conditions use specific syntax: deq_options.deq_condition := 'tab.user_data.priority = 1'; for JMS payloads: 'tab.user_data.text_vc LIKE ''%ORDER%'''; verify syntax by testing against the underlying queue table view: SELECT * FROM MY_QUEUE_TABLE WHERE <condition>; consult DBMS_AQ package documentation for condition column reference syntax.
Meaning: A DEQUEUE operation timed out because no message matching the dequeue criteria became available within the specified WAIT time.
Root Cause: Queue is empty and WAIT=DBMS_AQ.NO_WAIT or a short wait time was specified; consumer waiting for messages that are not being produced; dequeue condition too selective finding no messages; consumer and producer out of sync.
Solution: Handle ORA-25228 as a normal empty-queue condition, not an error: EXCEPTION WHEN OTHERS THEN IF SQLCODE = -25228 THEN NULL; -- Queue empty, retry later ELSE RAISE; END IF; use DBMS_AQ.FOREVER for blocking dequeue in dedicated consumer threads; implement exponential backoff in polling consumers; monitor queue depth: SELECT COUNT(*) FROM MY_QUEUE WHERE MSG_STATE='READY'.
Meaning: All available messages in the queue have been successfully dequeued and there are no more messages to process in the current dequeue session.
Root Cause: Consumer has processed all pending messages; end-of-fetch condition for a queue browse operation; batch dequeue script hitting the end of the available message set; expected behavior when queue is fully drained.
Solution: Treat ORA-25235 as a normal termination condition for batch consumers: LOOP DBMS_AQ.DEQUEUE(...); COMMIT; processed_count := processed_count + 1; EXCEPTION WHEN OTHERS THEN IF SQLCODE IN (-25228, -25235) THEN EXIT; ELSE RAISE; END IF; END LOOP; for browse operations: v_browse_options.wait := DBMS_AQ.NO_WAIT; loop until no-more-messages exception is raised.
Meaning: The network connection between the Oracle client and the database server was lost during an active operation, resulting in the current transaction being rolled back.
Root Cause: Network interruption (switch failure, cable issue); server-side TCP keepalive timeout; Oracle server process killed while client was connected; intermediate firewall or load balancer closing idle/slow connections; NIC failover causing brief TCP disruption.
Solution: Configure Oracle Dead Connection Detection: add SQLNET.EXPIRE_TIME=10 to sqlnet.ora on the server (probe every 10 minutes); for firewalls: set TCP_KEEPALIVE in sqlnet.ora: SQLNET.RECV_TIMEOUT=120; implement connection pooling with connection validation (testOnBorrow in JDBC); application must handle connection loss with reconnect logic and transaction retry.
Meaning: The Oracle dedicated server process for the session was killed by the Linux OOM (Out-of-Memory) killer due to system memory exhaustion, causing immediate connection loss.
Root Cause: System RAM exhausted; OOM killer selected oracle server processes as candidates for termination; vm.overcommit_memory not tuned for Oracle; SGA+PGA+OS overhead exceeding physical RAM; large PGA workload (sorts, hash joins) consuming excessive process memory.
Solution: Check OOM kills: dmesg | grep -i 'oom killer'; grep -i 'out of memory' /var/log/messages; configure vm.overcommit_memory=2 and vm.overcommit_ratio=80; use HugePages for SGA to reduce memory fragmentation: configure nr_hugepages; ensure total memory allocation: SGA + (PGA_AGGREGATE_TARGET × 1.2) + OS overhead < Physical RAM × 0.85.
Meaning: Oracle rolled back the current transaction, typically because it was chosen as the deadlock victim, was terminated by Resource Manager, or was rolled back by an explicit internal decision.
Root Cause: Deadlock detection: Oracle chose this transaction as the victim to break the deadlock cycle (ORA-00060 will accompany this); Resource Manager terminating a runaway transaction; ROLLBACK command issued by the application; transaction rolled back due to ORA-01555 or other internal error.
Solution: Application must detect ORA-24761 and retry the transaction if it was a deadlock victim; implement retry logic with exponential backoff (max 3 retries); analyze deadlock trace file: SELECT VALUE FROM V$DIAG_INFO WHERE NAME='Default Trace File'; redesign transaction ordering to prevent deadlocks; use SELECT FOR UPDATE SKIP LOCKED to avoid deadlock-prone lock contention patterns.
Meaning: Oracle's space management layer (ktf*) detected an inconsistency in the freelist or bitmap blocks used to track free space in a locally managed tablespace.
Root Cause: Space management bitmap block corruption; storage-level write error corrupting the extent map; Oracle bug in specific release affecting locally managed tablespace bitmap; first argument [ktfbn] or [ktfbhget] in ORA-600 identifies the space management subsystem.
Solution: Run RMAN VALIDATE: RMAN> VALIDATE TABLESPACE my_ts; check V$DATABASE_BLOCK_CORRUPTION; attempt block recovery: RMAN> RECOVER CORRUPTION LIST; check MOS Note for specific ORA-600 argument combination (search: ORA-600 [ktfbn]); may require segment rebuild or tablespace recreation if corruption is extensive; open SR with full trace and alert log.
Meaning: An INSERT or UPDATE supplied a value that fails the Boolean expression defined in a CHECK constraint on the target column or table.
Root Cause: Application inserting a value outside the allowed range (e.g., STATUS NOT IN ('A','I','P')); business rule encoded in the database that the application layer did not enforce; data migration loading legacy values not valid under current constraints.
Solution: Identify the violated constraint: SELECT CONSTRAINT_NAME, SEARCH_CONDITION FROM DBA_CONSTRAINTS WHERE TABLE_NAME='MY_TABLE' AND CONSTRAINT_TYPE='C'; review the failing row value against the condition; fix the application logic or pre-filter migration data; to temporarily disable during migration: ALTER TABLE t DISABLE CONSTRAINT ck_name; re-enable and validate: ALTER TABLE t ENABLE VALIDATE CONSTRAINT ck_name.
Meaning: A CREATE TABLE or ALTER TABLE ADD CONSTRAINT statement failed because a UNIQUE or PRIMARY KEY constraint with the same key columns already exists on the table.
Root Cause: Idempotent DDL script creating constraints without checking existence; primary key already implicitly created by CREATE TABLE definition; constraint re-added after a failed partial migration script; deployment tool running CREATE CONSTRAINT multiple times.
Solution: Check existing constraints: SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS FROM DBA_CONSTRAINTS WHERE TABLE_NAME='MY_TABLE' AND CONSTRAINT_TYPE IN ('P','U'); use defensive DDL: BEGIN EXECUTE IMMEDIATE 'ALTER TABLE t ADD CONSTRAINT pk_t PRIMARY KEY (id)'; EXCEPTION WHEN OTHERS THEN IF SQLCODE=-2261 THEN NULL; ELSE RAISE; END IF; END; verify the existing constraint is valid before skipping.
Meaning: A FOREIGN KEY constraint cannot be created because the child column's data type does not exactly match the parent column's data type.
Root Cause: Parent table uses NUMBER(10) but child table uses NUMBER(12) or VARCHAR2 for the same logical key; NLS character set differences affecting CHAR semantics between tables; child and parent column lengths or precision differ; foreign key columns must match exactly in type and precision.
Solution: Verify column types: SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION FROM DBA_TAB_COLUMNS WHERE COLUMN_NAME='FK_COL' AND TABLE_NAME IN ('PARENT','CHILD'); modify the child column to exactly match: ALTER TABLE child MODIFY fk_col NUMBER(10); then create the constraint: ALTER TABLE child ADD CONSTRAINT fk_name FOREIGN KEY (fk_col) REFERENCES parent(id).
Meaning: A FOREIGN KEY constraint references a parent table column (or column combination) that does not have a PRIMARY KEY or UNIQUE constraint defined on it.
Root Cause: Parent table column referenced in REFERENCES clause has no PRIMARY KEY or UNIQUE constraint; composite FK references columns not covered by a composite unique/PK; wrong column name specified in REFERENCES clause; parent table's unique constraint was recently dropped.
Solution: Verify parent table's primary/unique keys: SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM DBA_CONSTRAINTS WHERE TABLE_NAME='PARENT_TABLE' AND CONSTRAINT_TYPE IN ('P','U'); verify column membership: SELECT * FROM DBA_CONS_COLUMNS WHERE CONSTRAINT_NAME='PK_PARENT'; create the missing parent constraint first, then add the foreign key.
Meaning: ALTER TABLE ENABLE VALIDATE CONSTRAINT failed because existing data in the table violates the CHECK constraint being enabled.
Root Cause: Check constraint added retroactively but existing data violates the rule; data migrated or bulk-loaded while the constraint was disabled; different application versions used different value ranges for the column.
Solution: Identify violating rows: SELECT * FROM my_table WHERE NOT (check_condition); fix or archive invalid data before enabling: UPDATE my_table SET col=default_val WHERE NOT (col IN ('A','I','P')); to enable without validating existing rows (trusting they are correct): ALTER TABLE my_table ENABLE NOVALIDATE CONSTRAINT ck_name; use ENABLE VALIDATE only after cleaning data.
Meaning: Enabling a NOT NULL constraint or a PRIMARY KEY constraint failed because existing rows contain NULL values in the constrained column.
Root Cause: Column added as nullable, populated partially, and now being converted to NOT NULL; primary key column contains NULLs from a bulk load that bypassed validation; nullable column used as a composite primary key component with NULL entries.
Solution: Find NULL rows: SELECT COUNT(*) FROM my_table WHERE col IS NULL; update them with a default: UPDATE my_table SET col=default_val WHERE col IS NULL; COMMIT; then enable the constraint: ALTER TABLE my_table ENABLE CONSTRAINT nn_col; if a default is appropriate going forward: ALTER TABLE my_table MODIFY col NOT NULL DEFAULT 'UNKNOWN'.
Meaning: ALTER TABLE DISABLE CONSTRAINT on a PRIMARY KEY or UNIQUE constraint failed because other tables have FOREIGN KEY constraints that reference it.
Root Cause: Attempting to disable a parent table's primary key while child tables have active FK references; during data migration, DBAs often need to disable parent PKs but must disable child FKs first in the correct dependency order.
Solution: Identify all child FK constraints: SELECT a.TABLE_NAME, a.CONSTRAINT_NAME FROM DBA_CONSTRAINTS a JOIN DBA_CONSTRAINTS b ON a.R_CONSTRAINT_NAME=b.CONSTRAINT_NAME WHERE b.TABLE_NAME='PARENT_TABLE'; disable FKs first: ALTER TABLE child_table DISABLE CONSTRAINT fk_name; then disable parent PK: ALTER TABLE parent_table DISABLE CONSTRAINT pk_name; re-enable in reverse order after data operations.
Meaning: ALTER TABLE ENABLE VALIDATE CONSTRAINT on a FOREIGN KEY constraint failed because child rows exist whose FK values do not match any parent key.
Root Cause: Data migration inserted child rows while FK was disabled without ensuring parent rows exist; orphaned child records from deleted parents (FK was DISABLED during bulk delete); incorrect reference key values in child rows from legacy data.
Solution: Find orphaned rows: SELECT DISTINCT c.fk_col FROM child c WHERE NOT EXISTS (SELECT 1 FROM parent p WHERE p.id=c.fk_col); delete or fix orphans: DELETE FROM child WHERE fk_col NOT IN (SELECT id FROM parent); then validate: ALTER TABLE child ENABLE VALIDATE CONSTRAINT fk_name; alternatively use ENABLE NOVALIDATE to allow without checking historical data.
Meaning: DROP TABLE or DROP CONSTRAINT failed because other tables have FOREIGN KEY constraints that reference the unique or primary key being removed.
Root Cause: Attempting DROP TABLE parent_table without first dropping child FK constraints; DROP CONSTRAINT on a PK column still referenced by FKs in child tables; schema cleanup script executing DROP TABLE statements in wrong (child-first) order.
Solution: Use CASCADE CONSTRAINTS for table drops: DROP TABLE parent_table CASCADE CONSTRAINTS; for constraint drops: ALTER TABLE child_table DROP CONSTRAINT fk_name; then ALTER TABLE parent_table DROP PRIMARY KEY; generate FK drop script: SELECT 'ALTER TABLE '||TABLE_NAME||' DROP CONSTRAINT '||CONSTRAINT_NAME||';' FROM DBA_CONSTRAINTS WHERE R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='PARENT_TABLE').
Meaning: After re-enabling a CHECK constraint following a bulk data migration, the ENABLE VALIDATE step fails because migrated rows contain values that violate the constraint.
Root Cause: Source system had looser validation rules than the target; ETL pipeline bypassed constraint checks using direct-path inserts; APPEND hint or SQL*Loader DIRECT=TRUE mode bypasses constraint validation during load.
Solution: Pre-validate before enabling: SELECT COUNT(*), constraint_column FROM migration_table WHERE NOT (search_condition) GROUP BY constraint_column; create a exceptions table: CREATE TABLE exceptions (ROW_ID ROWID, OWNER VARCHAR2(30), TABLE_NAME VARCHAR2(30), CONSTRAINT VARCHAR2(30)); use EXCEPTIONS INTO: ALTER TABLE t ENABLE VALIDATE CONSTRAINT ck_name EXCEPTIONS INTO exceptions; query exceptions table to find and fix all violating rows.
Meaning: An operation (FETCH, CLOSE, EXECUTE) was performed on a cursor handle that is not valid — it was never opened, has already been closed, or the handle itself is NULL or uninitialized.
Root Cause: DBMS_SQL cursor closed and then referenced again; OCI application using a stale cursor handle after cursor close; exception handler closing a cursor that was never opened (cursor handle is 0 or negative); PL/SQL explicit cursor FETCH after CLOSE.
Solution: Always validate cursor state: IF DBMS_SQL.IS_OPEN(v_cursor) THEN DBMS_SQL.CLOSE_CURSOR(v_cursor); END IF; for explicit PL/SQL cursors: IF v_cur%ISOPEN THEN FETCH/CLOSE; END IF; in OCI: always call OCIHandleAlloc before OCIStmtPrepare; add null-checks on cursor handles in error paths; never reuse a closed cursor handle — open a new one.
Meaning: A SQL statement with bind variable placeholders (e.g., :1, :name, ?) was executed but fewer bind values were provided than the number of placeholders in the statement.
Root Cause: Dynamic SQL string built with :1, :2, :3 but only two bind values provided; DBMS_SQL.BIND_VARIABLE called for fewer positions than the statement requires; JDBC PreparedStatement with 3 '?' placeholders but only 2 setXxx() calls made; SQL statement edited to add a WHERE clause binding but application code not updated.
Solution: Count placeholders: SELECT REGEXP_COUNT(sql_text, ':\w+') FROM DUAL; ensure bind calls match placeholder count exactly; in DBMS_SQL: DBMS_SQL.BIND_VARIABLE(c, ':p1', val1); DBMS_SQL.BIND_VARIABLE(c, ':p2', val2); in JDBC: pstmt.setString(1,v1); pstmt.setString(2,v2); use named binds instead of positional to make mismatches obvious.
Meaning: An Oracle API call was made without an active database session — the session never logged on, was terminated, or the connection was severed before the call was made.
Root Cause: OCI application calling OCIStmtExecute before OCISessionBegin; Pro*C program losing connection and not reconnecting before issuing SQL; session expired due to IDLE_TIME profile limit between operations; connection pool returning an invalidated connection that was silently dropped.
Solution: In OCI: always verify session handle is valid before use; in JDBC: enable connection validation (testOnBorrow=true) in the pool; in Pro*C: EXEC SQL WHENEVER SQLERROR DO reconnect(); implement connection health-check wrapper that reconnects on ORA-01012; monitor profile IDLE_TIME: SELECT PROFILE, LIMIT FROM DBA_PROFILES WHERE RESOURCE_NAME='IDLE_TIME'.
Meaning: The currently executing SQL or PL/SQL operation was explicitly canceled — either by the user pressing Ctrl+C in SQL*Plus, by an application-level timeout mechanism, or by an OCIBreak() call from OCI.
Root Cause: SQL*Plus user hit Ctrl+C during a long-running query; JDBC statement timeout (setQueryTimeout) expired and the driver issued a cancel; application-level watchdog thread called OCIBreak to interrupt a runaway operation; Resource Manager switching the session's consumer group causing the statement to be aborted.
Solution: Treat ORA-01013 in applications as a recoverable interruption — rollback and retry or report timeout to user; in JDBC: catch SQLTimeoutException which wraps ORA-01013; investigate WHY the query was slow enough to trigger a timeout: check AWR for the SQL's execution plan and tune; increase setQueryTimeout only if the operation is legitimately long-running.
Meaning: A new connection attempt was rejected because the Oracle instance is currently executing a SHUTDOWN sequence and is no longer accepting new user connections.
Root Cause: DBA issued SHUTDOWN IMMEDIATE or SHUTDOWN TRANSACTIONAL; OS-level shutdown script stopping Oracle services; CRS-managed database rolling restart; application connection pool attempting to establish new connections while the database is shutting down.
Solution: Applications must handle ORA-01014 gracefully by implementing connection retry with exponential backoff; connection pool should detect this error and pause reconnection attempts; monitor for planned shutdowns via change management; implement application-level circuit breaker that disables DB operations when ORA-01014 is received; alert operations team immediately when this error appears in application logs unexpectedly.
Meaning: A bind variable name or position specified in an OCI BIND call, EXECUTE IMMEDIATE, or DBMS_SQL.BIND_VARIABLE is not valid or does not match any placeholder in the parsed SQL statement.
Root Cause: Bind variable name in BIND_VARIABLE call (':param1') does not match the placeholder in the SQL text (':p1'); positional bind index out of range; SQL statement rebuilt dynamically but bind calls reference old placeholder names; trailing spaces in bind variable name causing mismatch.
Solution: Verify placeholder names match exactly: v_sql := 'SELECT * FROM t WHERE id=:v_id'; DBMS_SQL.BIND_VARIABLE(c, ':v_id', 100); — the name in BIND_VARIABLE must exactly match the colon-prefixed name in the SQL; use DBMS_SQL.DESCRIBE_COLUMNS after parsing to verify bind positions; avoid mixing named and positional binds in the same statement.
Meaning: A user has SELECT privilege on a view but lacks the required privileges on the base tables or other objects that the view queries, preventing the view from executing.
Root Cause: View owner granted SELECT on the view to user A, but user A does not have direct grants on the base tables, and the view was created without AUTHID CURRENT_USER (invoker's rights); view referencing objects in a schema where direct access was revoked after view creation.
Solution: Grant direct SELECT on base tables: GRANT SELECT ON base_table TO app_user; or use a stored procedure with AUTHID DEFINER to execute queries securely without exposing base tables; alternatively create the view WITH READ ONLY and ensure the view owner has the required table privileges; use SELECT * FROM SESSION_PRIVS to verify current user's privileges.
Meaning: ROLLBACK TO SAVEPOINT savepoint_name failed because the specified savepoint was never created in the current session, or was erased by a prior ROLLBACK or COMMIT.
Root Cause: SAVEPOINT sp_name was never executed before ROLLBACK TO SAVEPOINT sp_name; savepoint created in a different session (savepoints are session-local); previous ROLLBACK without the savepoint name erased all savepoints established after the last commit; COMMIT executed between SAVEPOINT and ROLLBACK TO SAVEPOINT, erasing the savepoint.
Solution: Always establish savepoints before complex multi-step operations: SAVEPOINT before_update; UPDATE ...; IF error THEN ROLLBACK TO SAVEPOINT before_update; END IF; remember that COMMIT erases all savepoints; for nested transactions in PL/SQL, use PRAGMA AUTONOMOUS_TRANSACTION instead of savepoints; use savepoints defensively in long transactions with multiple logical steps.
Meaning: A new user connection was rejected because the database instance is in the process of shutting down; only SYSDBA and SYSOPER connections are allowed during this period.
Root Cause: SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE in progress waiting for existing sessions to disconnect; application connection pool aggressively retrying connections during the shutdown window; automated monitoring tool attempting connections during planned maintenance.
Solution: Implement application-level maintenance mode detection: poll V$INSTANCE.STATUS or the listener's status before connecting; configure connection pool with maxRetries and retryDelay to back off gracefully; use JDBC connection validation to detect ORA-01090 and pause pool acquisition; notify application teams before initiating database shutdowns to activate maintenance pages.
Meaning: The Oracle instance crashed or was aborted while the session was connected, causing all active sessions to be forcibly disconnected with their uncommitted transactions rolled back.
Root Cause: ORA-00600 or ORA-07445 caused instance abort; DBA issued SHUTDOWN ABORT; background process (DBWn, LGWR, CKPT) crashed triggering instance recovery; OS killed Oracle processes due to OOM; storage failure caused critical I/O errors in background processes.
Solution: Immediately check alert log: tail -1000 $ORACLE_BASE/diag/rdbms/.../alert_*.log | grep -A5 'ORA-00600\|ABORT\|terminating'; attempt restart: STARTUP; if recovery needed, Oracle will perform automatic instance recovery; inform application teams to retry transactions; check V$INSTANCE.STATUS after restart; investigate root cause before next production use.
Meaning: Oracle encountered a physical I/O error when attempting to read a specific block from a datafile, indicating a storage-level problem below Oracle.
Root Cause: Disk hardware failure causing read errors; SAN/NAS path failure; corrupted block at the OS filesystem level; HBA timeout causing I/O to fail; multipath configuration failure leaving only a degraded path; RAID array degraded with missing parity.
Solution: Immediately run RMAN block recovery: RMAN> RECOVER DATAFILE n BLOCK m; check OS I/O errors: dmesg | tail -100 | grep -i error; check storage path health: multipath -l; if block is physically unreadable, restore datafile from backup: RMAN> RESTORE DATAFILE n; RECOVER DATAFILE n; validate: RMAN> VALIDATE DATAFILE n; always check storage hardware health immediately after this error.
Meaning: Oracle could not open a datafile at the OS level during database startup or during a file access attempt, preventing the database from opening.
Root Cause: Datafile physically missing from the filesystem; directory containing the datafile was deleted or moved; OS-level file permissions changed removing oracle user access; symbolic link pointing to datafile is broken; filesystem containing the datafile failed to mount after reboot.
Solution: Verify file exists: ls -la /path/to/datafile.dbf; check mount status: df -h | grep /oradata; if file is missing and database is ARCHIVELOG mode: RMAN> RESTORE DATAFILE n; RECOVER DATAFILE n; ALTER DATABASE DATAFILE n ONLINE; if filesystem unmounted: remount it then open the database; check alert log for the specific file number and path: grep 'ORA-01116' alert_*.log.
Meaning: The header of a datafile does not match what Oracle expects based on the controlfile, indicating the file is from a different database, a different time, or is corrupt.
Root Cause: Wrong datafile restored from backup (file from a different database or a clone); datafile header corrupt due to incomplete write during crash; file system block size mismatch; datafile from before RESETLOGS being used after RESETLOGS opened the database with a new incarnation.
Solution: Check file header: RMAN> LIST DATAFILECOPY '/path/file.dbf'; compare DBID with V$DATABASE: SELECT DBID FROM V$DATABASE; use DBVERIFY: dbv file=/path/file.dbf blocksize=8192; restore the correct file from backup: RMAN> RESTORE DATAFILE n; always verify DBID matches before restoring files; if post-RESETLOGS, use the correct post-RESETLOGS backup.
Meaning: ALTER TABLESPACE t OFFLINE IMMEDIATE was attempted but the database is in NOARCHIVELOG mode, which does not support immediate offline without data loss.
Root Cause: NOARCHIVELOG mode databases cannot safely take tablespaces offline immediately because without archivelogs, the tablespace cannot be recovered when brought back online; the operation requires ARCHIVELOG mode for the recovery capability it depends on.
Solution: In NOARCHIVELOG mode, use OFFLINE NORMAL instead: ALTER TABLESPACE t OFFLINE NORMAL; this requires all datafiles to be accessible; alternatively enable ARCHIVELOG mode: SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN; then OFFLINE IMMEDIATE will be permitted; for production databases, ARCHIVELOG mode is mandatory for operational flexibility.
Meaning: Oracle's DBWn background process could not write a checkpoint to a datafile and automatically offlined the file to protect the rest of the database, while logging this warning in the alert log.
Root Cause: I/O error on the specific datafile's storage path preventing DBWn from writing dirty buffers; storage LUN failure mid-operation; filesystem full preventing checkpoint write; disk I/O timeout; the datafile is automatically offlined as a protective measure in ARCHIVELOG mode.
Solution: Immediately check alert log for accompanying ORA-00345 or ORA-01578; fix the storage issue: restore the failed path or LUN; restore and recover the datafile: RMAN> SQL 'ALTER DATABASE DATAFILE n OFFLINE'; RESTORE DATAFILE n; RECOVER DATAFILE n; SQL 'ALTER DATABASE DATAFILE n ONLINE'; monitor with: SELECT FILE#, STATUS, CHECKPOINT_CHANGE# FROM V$DATAFILE WHERE STATUS != 'ONLINE'.
Meaning: Point-in-time or incomplete recovery completed successfully, but the warning indicates that the database has been rolled back to an earlier point and transactions after the recovery target have been lost.
Root Cause: RECOVER DATABASE UNTIL TIME/SCN/CANCEL used for PITR (point-in-time recovery); accidental data loss recovery scenario; flashback database operation; incomplete recovery due to missing archivelogs forcing recovery to stop before current SCN.
Solution: This is expected after PITR — proceed with ALTER DATABASE OPEN RESETLOGS; immediately take a full RMAN backup after RESETLOGS: RMAN> BACKUP DATABASE PLUS ARCHIVELOG; verify data is at the correct recovery point by checking SCN and timestamps: SELECT CURRENT_SCN, SYSDATE FROM V$DATABASE; notify stakeholders of the recovery point and what data was lost; update RMAN catalog to reflect the new incarnation.
Meaning: The CREATE DATABASE command failed, leaving the database in an unusable state that typically requires dropping partially created files and restarting.
Root Cause: Invalid DB_BLOCK_SIZE (must be a power of 2 between 2K and 64K); insufficient disk space for initial system datafiles; SYSTEM or SYSAUX tablespace initial size too small; invalid character in DB_NAME parameter; DB_CREATE_FILE_DEST directory does not exist or lacks write permission.
Solution: Check the alert log for the specific error accompanying ORA-01501; clean up any partially created files: rm /oradata/DBNAME/*; fix init.ora parameters: DB_BLOCK_SIZE=8192, DB_NAME='MYDB' (max 8 chars for some versions); ensure SYSTEM tablespace size is at least 700MB; verify directories exist and oracle user has write access before retrying CREATE DATABASE.
Meaning: A command referencing a rollback segment by name (SET TRANSACTION USE ROLLBACK SEGMENT or ALTER ROLLBACK SEGMENT) failed because the named segment does not exist.
Root Cause: Legacy application using SET TRANSACTION USE ROLLBACK SEGMENT for manual RBS management but the database now uses Automatic Undo Management (AUM); old ROLLBACK_SEGMENTS parameter referencing non-existent segments in init.ora/SPFILE; rollback segment dropped but still referenced in configuration.
Solution: Verify rollback segments: SELECT SEGMENT_NAME, STATUS FROM DBA_ROLLBACK_SEGS; modern databases use AUM — remove ROLLBACK_SEGMENTS from SPFILE: ALTER SYSTEM RESET ROLLBACK_SEGMENTS SCOPE=SPFILE; verify UNDO_MANAGEMENT=AUTO: SELECT VALUE FROM V$PARAMETER WHERE NAME='undo_management'; remove SET TRANSACTION USE ROLLBACK SEGMENT from application code as it is incompatible with AUM.
Meaning: DROP TABLESPACE failed because the tablespace still contains segments (tables, indexes, LOBs, etc.) and the INCLUDING CONTENTS clause was not specified.
Root Cause: Attempting DROP TABLESPACE without INCLUDING CONTENTS on a non-empty tablespace; tablespace considered empty by the DBA but still contains system-generated objects (LOB segments, index-organized table overflow segments, hidden IOT segments).
Solution: List remaining objects: SELECT SEGMENT_TYPE, SEGMENT_NAME, OWNER FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='MY_TS'; to drop all contents and datafiles: DROP TABLESPACE my_ts INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS; to only drop the tablespace definition keeping datafiles: DROP TABLESPACE my_ts INCLUDING CONTENTS; WARNING: this permanently deletes all data — verify objects are backed up or migrated first.
Meaning: An attempt to DROP TABLESPACE SYSTEM or SYSAUX was made, which Oracle prevents because these tablespaces are required for the database to function.
Root Cause: Script accidentally targeting SYSTEM instead of a user tablespace; tablespace name typo; automated space management script with insufficient safety checks allowing system tablespace names; DBAs unfamiliar with Oracle's protected tablespaces attempting cleanup.
Solution: SYSTEM and SYSAUX can never be dropped; if these tablespaces are corrupt or need recovery, restore via RMAN: RMAN> RESTORE TABLESPACE SYSTEM; RECOVER TABLESPACE SYSTEM; verify the tablespace name in the DROP statement; add safety checks in scripts: IF v_ts_name IN ('SYSTEM','SYSAUX','UNDOTBS1','TEMP') THEN RAISE_APPLICATION_ERROR(-20001,'Cannot drop system tablespace'); END IF.
Meaning: Oracle cannot open the SPFILE at startup because the file does not exist at the expected path, cannot be read, or the path in the PFILE points to a non-existent SPFILE.
Root Cause: SPFILE deleted or moved without updating the PFILE pointer; ORACLE_HOME environment variable incorrect causing wrong SPFILE path to be searched; permissions changed on $ORACLE_HOME/dbs/spfile.ora; database cloned but SPFILE path not updated; symlink to SPFILE broken.
Solution: Verify SPFILE location: find $ORACLE_HOME -name 'spfile*.ora' 2>/dev/null; list PFILE: cat $ORACLE_HOME/dbs/init$ORACLE_SID.ora; if SPFILE is missing, restore from backup: RMAN> RESTORE SPFILE FROM AUTOBACKUP; or recreate from a parameter backup: CREATE SPFILE FROM PFILE='/tmp/init_backup.ora'; verify with: SHOW PARAMETER spfile after startup.
Meaning: Fast (incremental) refresh was requested or configured for a materialized view that does not meet the structural requirements for REFRESH FAST.
Root Cause: Materialized view uses subqueries, CONNECT BY, non-mergeable views, or analytic functions which fast refresh does not support; missing materialized view log on the base table(s); MV joins three or more tables without rowid in the SELECT list; GROUP BY with aggregates not supported for fast refresh without ROWID or primary key.
Solution: Check MV capabilities: SELECT OWNER, MVIEW_NAME, FAST_REFRESHABLE, STALE_SINCE FROM DBA_MVIEWS WHERE MVIEW_NAME='MY_MV'; create required MV logs: CREATE MATERIALIZED VIEW LOG ON base_table WITH ROWID, SEQUENCE (col1, col2) INCLUDING NEW VALUES; simplify the MV query to meet fast refresh requirements; or switch to REFRESH COMPLETE: EXEC DBMS_MVIEW.REFRESH('MY_MV', method => 'C').
Meaning: DBMS_MVIEW.REFRESH failed with a generic error indicating a problem in the refresh execution path, often masking a more specific underlying error.
Root Cause: Base table has been dropped or renamed; DB link used for remote MV refresh is broken; materialized view log on base table was purged or dropped; insufficient temp space for complete refresh; base table structure changed invalidating the MV definition.
Solution: Enable detailed refresh errors: EXEC DBMS_MVIEW.REFRESH('MY_MV', atomic_refresh => FALSE); check DBA_MVIEW_REFRESH_TIMES and DBA_MVIEWS for status; for remote MVs: test the DB link: SELECT 1 FROM DUAL@remote_link; check MV log existence: SELECT LOG_TABLE FROM DBA_MVIEW_LOGS WHERE MASTER='BASE_TABLE'; drop and recreate the MV if base table structure changed: DROP MATERIALIZED VIEW mv_name; CREATE MATERIALIZED VIEW mv_name AS SELECT ...
Meaning: An Oracle-internal job (typically an automatic materialized view refresh job managed by DBMS_JOB or the Scheduler) failed during execution.
Root Cause: Automatic refresh job for a materialized view encountered an error; job marked BROKEN after repeated failures; base table locked or unavailable during scheduled refresh window; ORA-12008 from the MV refresh propagated as ORA-12012 from the job framework.
Solution: Check broken jobs: SELECT JOB, WHAT, BROKEN, FAILURES FROM DBA_JOBS WHERE BROKEN='Y'; reset and rerun: EXEC DBMS_JOB.BROKEN(job_id, FALSE); EXEC DBMS_JOB.RUN(job_id); for Scheduler-based MV jobs: SELECT JOB_NAME, STATUS FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE STATUS != 'SUCCEEDED' ORDER BY LOG_DATE DESC; address the underlying MV refresh error first before re-enabling the automatic job.
Meaning: A fast refresh of a materialized view requires a primary key or rowid-based materialized view log on the base table, which is missing or incomplete.
Root Cause: Materialized view log created WITH ROWID but MV definition uses primary key columns; base table has no primary key and the MV log was not created WITH ROWID; primary key was dropped from the base table after the MV was created; MV log created before the primary key was defined.
Solution: For primary-key based fast refresh: ensure base table has a PK: ALTER TABLE base_table ADD CONSTRAINT pk_bt PRIMARY KEY (id); then create or recreate the MV log: DROP MATERIALIZED VIEW LOG ON base_table; CREATE MATERIALIZED VIEW LOG ON base_table WITH PRIMARY KEY (id), SEQUENCE INCLUDING NEW VALUES; for rowid-based: CREATE MATERIALIZED VIEW LOG ON base_table WITH ROWID (col1) INCLUDING NEW VALUES.
Meaning: The materialized view log on the base table is newer than the last refresh of the materialized view, meaning the log was truncated or recreated since the last refresh, making fast refresh impossible.
Root Cause: MV log was accidentally truncated or dropped and recreated after the last MV refresh; someone issued DBMS_MVIEW.PURGE_MVIEW_FROM_LOG to clean up log entries, going beyond what was needed; MV log was dropped as part of a maintenance operation without refreshing dependent MVs first.
Solution: Fast refresh is not possible in this state — must do complete refresh: EXEC DBMS_MVIEW.REFRESH('MY_MV', method => 'C', atomic_refresh => FALSE); after complete refresh, fast refresh will work again: EXEC DBMS_MVIEW.REFRESH('MY_MV', method => 'F'); set up monitoring to alert when MV_LOG is modified without a corresponding MV refresh; never truncate MV logs manually.
Meaning: A materialized view refresh operation (manual or automatic) failed due to an error during the refresh process, leaving the MV in a STALE state.
Root Cause: For ON COMMIT MVs: the base table transaction commit triggered a synchronous refresh that failed (e.g., ORA-01555, ORA-04031); for ON DEMAND MVs: scheduled refresh encountered temp space exhaustion or lock contention; base table query returning an error during refresh (ORA-00942, privileges revoked).
Solution: Check MV status: SELECT MVIEW_NAME, STALENESS, COMPILE_STATE FROM DBA_MVIEWS WHERE MVIEW_NAME='MY_MV'; attempt manual refresh with error details: EXEC DBMS_MVIEW.REFRESH('MY_MV', atomic_refresh => FALSE, out_of_place => TRUE); check errors in DBA_MVIEW_REFRESH_TIMES; address root cause (space, locks, privileges) and then manually refresh to bring MV current.
Meaning: A descending sequence (INCREMENT BY negative value) has reached its MINVALUE and NOCYCLE is set, so NEXTVAL cannot produce further values.
Root Cause: Descending sequence (INCREMENT BY -1) set with MINVALUE and NOCYCLE; sequence used for reverse-ordered primary keys reached its minimum; sequence designed to count down from a large number to 1 hit 1 and cannot continue without cycling.
Solution: Extend the sequence downward: ALTER SEQUENCE desc_seq MINVALUE -9999999; or enable cycling: ALTER SEQUENCE desc_seq CYCLE; to check current value and limits: SELECT LAST_NUMBER, MIN_VALUE, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG FROM DBA_SEQUENCES WHERE SEQUENCE_NAME='DESC_SEQ'; consider whether a descending sequence is the right design — usually ascending sequences with ORDER are preferred.
Meaning: SEQUENCE_NAME.CURRVAL was referenced before SEQUENCE_NAME.NEXTVAL was called in the current session; CURRVAL only holds a value after at least one NEXTVAL call in the same session.
Root Cause: Application calling seq.CURRVAL immediately after a database reconnection (new session) without first calling seq.NEXTVAL; INSERT trigger referencing seq.CURRVAL but no NEXTVAL call made earlier in the session; microservice architecture where different requests hit different connection pool sessions — session A calls NEXTVAL but session B tries CURRVAL.
Solution: Always call NEXTVAL before CURRVAL in any new session; design INSERT triggers to use NEXTVAL directly: :NEW.id := my_seq.NEXTVAL; never rely on CURRVAL across session boundaries; in Oracle 12c+, use IDENTITY columns instead of manual sequences: id NUMBER GENERATED ALWAYS AS IDENTITY; eliminates the CURRVAL session-dependency problem entirely.
Meaning: An operation referencing a sequence (NEXTVAL, CURRVAL, ALTER SEQUENCE, DROP SEQUENCE) failed because the sequence does not exist in the current or specified schema.
Root Cause: Sequence accidentally dropped during schema cleanup; wrong schema qualifier used (schema.sequence_name); synonym pointing to a sequence that was dropped; case-sensitivity issue with quoted sequence names; sequence never created in the deployment script due to a prior error.
Solution: Search for the sequence: SELECT OWNER, SEQUENCE_NAME, LAST_NUMBER FROM DBA_SEQUENCES WHERE SEQUENCE_NAME=UPPER('my_seq'); if dropped: recreate with an appropriate START WITH value to avoid PK conflicts: CREATE SEQUENCE my_seq START WITH (SELECT NVL(MAX(id),0)+1 FROM my_table) INCREMENT BY 1 CACHE 20 NOORDER NOCYCLE; check synonyms: SELECT SYNONYM_NAME, TABLE_OWNER, TABLE_NAME FROM DBA_SYNONYMS WHERE SYNONYM_NAME=UPPER('my_seq_syn').
Meaning: The connect string or service name format passed to Oracle is syntactically malformed and cannot be parsed as a valid TNS descriptor or EZConnect string.
Root Cause: EZConnect string missing the service name component (e.g., host:port without //host:port/service); tnsnames.ora entry with unbalanced parentheses or missing required keywords (HOST, PORT, SERVICE_NAME); JDBC URL with invalid format; ORACLE_SID or TWO_TASK environment variable set to an invalid value.
Solution: For EZConnect format use: //hostname:1521/service_name; for tnsnames format validate parenthesis balance: grep -c '(' tnsnames.ora should equal grep -c ')' tnsnames.ora; validate tnsnames.ora: tnsping my_service; check environment: echo $ORACLE_SID; echo $TWO_TASK; test JDBC URL format: jdbc:oracle:thin:@//host:1521/service.
Meaning: The Oracle client initiated a TCP connection to the listener but the connection was not completed within the configured SQLNET.INBOUND_CONNECT_TIMEOUT or the OS TCP connect timeout period.
Root Cause: Network routing issue causing packets to be dropped or delayed; firewall inspecting Oracle packets and introducing latency; listener overloaded and not accepting connections quickly enough; SQLNET.INBOUND_CONNECT_TIMEOUT set too low for the network characteristics; database server under heavy CPU load delaying listener response.
Solution: Test basic connectivity: telnet dbhost 1521 (should connect immediately); increase connect timeout in sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=120; check listener load: lsnrctl status; verify listener log for errors: tail -100 $ORACLE_BASE/diag/tnslsnr/.../trace/listener.log; investigate firewall deep packet inspection rules for Oracle traffic; check server CPU: top | grep ora.
Meaning: An Oracle network read or write operation exceeded its configured timeout, causing the connection to be declared failed. This can occur during authentication, data transfer, or while waiting for a response.
Root Cause: SQLNET.RECV_TIMEOUT exceeded during a slow query result transfer; SQLNET.SEND_TIMEOUT exceeded when server is under load; network bandwidth saturation causing TCP retransmit delays; very large result sets being transferred over a slow WAN link.
Solution: Tune sqlnet.ora timeouts appropriately: SQLNET.RECV_TIMEOUT=0 (disable, let OS manage) or increase to 300; SQLNET.SEND_TIMEOUT=0; reduce result set sizes: add appropriate WHERE clauses or use pagination; check network throughput: iperf3 between app and DB server; investigate server-side wait events in V$SESSION for the affected session during the timeout.
Meaning: Oracle's TNS network layer failed to write a data packet to the connection, indicating the TCP connection has been broken from the client or an intermediate network device.
Root Cause: Client application crashed or disconnected mid-operation; firewall reset the TCP connection; network interface or switch failure causing TCP RST; client-side TCP buffer exhaustion; NIC bonding/failover event causing a brief TCP disruption.
Solution: Server-side, this error appears in the listener log and indicates a client-side disconnect: tail -f $ORACLE_BASE/diag/tnslsnr/.../trace/listener.log | grep TNS-12571; this is usually not a server problem — investigate the client side; check for patterns (same client IP, same time of day); if widespread, check for network infrastructure issues; configure SQLNET.EXPIRE_TIME for dead connection detection.
Meaning: Oracle could not retrieve authentication credentials (from a wallet, Kerberos ticket, or OS authentication token) needed to complete the connection authentication.
Root Cause: Oracle Wallet not open or not found at the path specified in sqlnet.ora's WALLET_LOCATION; Kerberos ticket expired or not present; SQLNET.AUTHENTICATION_SERVICES set to (BEQ, KERBEROS5) but Kerberos is not configured; wallet password incorrect or wallet corrupted.
Solution: Check wallet status: mkstore -wrl /path/to/wallet -listCredential; open auto-login wallet: orapki wallet create -wallet /wallet_path -auto_login; verify sqlnet.ora WALLET_LOCATION matches actual wallet path; for Kerberos: verify kinit -l -f krbtgt/REALM generates a valid ticket: klist; for SSL: verify certificate chain and server wallet is properly configured with TCPS listener.
Meaning: The authentication mechanism configured in SQLNET.AUTHENTICATION_SERVICES could not be initialized, preventing any connections from being authenticated.
Root Cause: SQLNET.AUTHENTICATION_SERVICES set to NTS or KERBEROS5 but the underlying OS authentication service (Windows security or Kerberos libraries) is unavailable; oracle OS group membership not configured correctly for OS authentication; Oracle network library (ons, nns) not properly installed.
Solution: Comment out or modify SQLNET.AUTHENTICATION_SERVICES in sqlnet.ora: SQLNET.AUTHENTICATION_SERVICES=(NONE); this falls back to password authentication; on Windows: verify OracleService is running and the oracle user is in the ORA_DBA group; on Linux for OS auth: verify /etc/oraInst.loc and oracle group membership; for Kerberos: verify Oracle KRB5 libraries are installed.
Meaning: Oracle's TNS layer could not establish a connection to the target address specified in the connect descriptor — the host, port, or protocol combination is unreachable.
Root Cause: Host in the connect descriptor is unreachable (routing issue, host down, wrong IP); port 1521 not open on the target host (firewall rule); TCP/IP protocol stack issue; tnsnames.ora has a valid service name but wrong host or port for the environment (e.g., using DEV host in PROD tnsnames).
Solution: Test basic TCP connectivity: telnet hostname 1521; verify tnsnames.ora entry: tnsping service_name; check listener: lsnrctl status on target host; verify firewall rules: iptables -L | grep 1521 (Linux) or Windows Firewall; test with a minimal connect descriptor to isolate the issue: sqlplus user/pwd@//specific_ip:1521/service.
Meaning: The Oracle JDBC Thin driver encountered an I/O error at the network layer, typically wrapping a java.net.SocketException or java.io.IOException indicating the connection to the database was lost or could not be established.
Root Cause: Network interruption between the Java application server and the Oracle database; Oracle database instance crashed mid-operation; firewall or load balancer closed the TCP connection; JDBC connection pool returning a stale connection that was closed by the database or network layer.
Solution: In connection pool config: set testOnBorrow=true and validationQuery='SELECT 1 FROM DUAL'; set connectionTestInterval to detect stale connections; implement retry logic: catch SQLException where getErrorCode()==17002 and retry with exponential backoff; configure Oracle JDBC reconnection: oracle.net.CONNECT_TIMEOUT=5000; oracle.jdbc.ReadTimeout=60000; check database alert log for instance events correlating with the JDBC error time.
Meaning: An ALTER SYSTEM SET command was issued for a static parameter that can only be changed in the SPFILE and requires a database restart to take effect.
Root Cause: Static parameters like DB_BLOCK_SIZE, PROCESSES, SGA_MAX_SIZE cannot be changed with SCOPE=MEMORY; attempting ALTER SYSTEM SET processes=500 without SCOPE=SPFILE; these parameters are read only at instance startup and cannot change while the instance is running.
Solution: Use SCOPE=SPFILE for static parameters: ALTER SYSTEM SET PROCESSES=500 SCOPE=SPFILE; then restart: SHUTDOWN IMMEDIATE; STARTUP; check if a parameter is static or dynamic: SELECT NAME, ISSYS_MODIFIABLE FROM V$PARAMETER WHERE NAME='processes'; ISSYS_MODIFIABLE=FALSE means SPFILE-only; plan parameter changes during maintenance windows as they require a restart.
Meaning: The total PGA memory used across all sessions exceeded the hard ceiling defined by PGA_AGGREGATE_LIMIT, causing Oracle to terminate the largest PGA consumers to enforce the limit.
Root Cause: PGA_AGGREGATE_LIMIT set too low for the workload; many concurrent memory-intensive operations (sorts, hash joins, OLAP queries) simultaneously consuming PGA; PGA_AGGREGATE_LIMIT defaults to 2GB or 200% of PGA_AGGREGATE_TARGET — whichever is larger; memory leak in a PL/SQL session accumulating unbounded collection data.
Solution: Increase the limit: ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=8G; monitor PGA usage: SELECT SUM(PGA_ALLOC_MEM)/1024/1024 MB FROM V$PROCESS; identify top PGA consumers: SELECT s.USERNAME, p.PGA_USED_MEM/1024/1024 MB FROM V$SESSION s JOIN V$PROCESS p ON s.PADDR=p.ADDR ORDER BY p.PGA_USED_MEM DESC; tune large-sort queries to use less PGA: increase PGA_AGGREGATE_TARGET to allow better sort area sizing.
Meaning: An Oracle feature was invoked that is not licensed or enabled in the current Oracle edition or configuration.
Root Cause: Using partitioning features on Standard Edition; calling DBMS_RLS (VPD) on non-Enterprise edition; attempting Parallel Query with no license; using In-Memory Column Store option without purchasing the license; running Database Vault commands without the Database Vault option installed.
Solution: Check licensed options: SELECT VALUE FROM V$OPTION WHERE PARAMETER='Partitioning'; SELECT * FROM V$LICENSE; review Oracle License Agreement to understand which options are included in your edition; Standard Edition 2 does not include partitioning, RAC, or In-Memory; upgrade to Enterprise Edition if the feature is required; contact Oracle licensing team to add the option license.
Meaning: ALTER SYSTEM RESET parameter_name SCOPE=SPFILE failed because the parameter does not exist as an explicit entry in the SPFILE — it is already using the default value.
Root Cause: Attempting to reset a parameter that was never explicitly set in the SPFILE; the parameter exists only as an implicit default; scripted parameter cleanup iterating over all parameters including those at default values.
Solution: Check if parameter is explicitly set in SPFILE: SELECT NAME, VALUE, ISDEFAULT FROM V$SPPARAMETER WHERE NAME='parameter_name'; if ISDEFAULT=TRUE, the parameter does not need to be reset — it is already at default; the error is benign in this case; guard scripts: IF v_isdefault = 'FALSE' THEN EXECUTE IMMEDIATE 'ALTER SYSTEM RESET '||v_param||' SCOPE=SPFILE'; END IF.
Meaning: ALTER SYSTEM SET with SCOPE=SPFILE or SCOPE=BOTH failed to write the updated parameter to the SPFILE on disk.
Root Cause: SPFILE is located on read-only filesystem; oracle user does not have write permission to the SPFILE location; SPFILE is in ASM and the ASM diskgroup is full; SPFILE has been manually locked; the path where SPFILE resides is on a full disk.
Solution: Check SPFILE location: SHOW PARAMETER spfile; verify write access: ls -la $ORACLE_HOME/dbs/spfile*.ora; if in ASM: check diskgroup space: SELECT FREE_MB FROM V$ASM_DISKGROUP WHERE NAME='DATA'; if filesystem full: df -h; to work around temporarily: use SCOPE=MEMORY only and create a new SPFILE from current memory: CREATE SPFILE='/new/path/spfile.ora' FROM MEMORY; update the startup pointer.
Meaning: A DDL statement (CREATE, DROP, ALTER, TRUNCATE, GRANT) was attempted directly in a PL/SQL block without using EXECUTE IMMEDIATE or DBMS_SQL, which is required for DDL in PL/SQL.
Root Cause: PL/SQL block containing a bare CREATE TABLE or ALTER TABLE statement without EXECUTE IMMEDIATE wrapping; developer accustomed to SQL*Plus running DDL directly inside BEGIN...END blocks; migration scripts containing DDL mixed with PL/SQL procedural logic without proper wrapping.
Solution: Wrap all DDL in EXECUTE IMMEDIATE: BEGIN EXECUTE IMMEDIATE 'CREATE TABLE my_table (id NUMBER PRIMARY KEY)'; EXCEPTION WHEN OTHERS THEN IF SQLCODE=-955 THEN NULL; ELSE RAISE; END IF; END; DDL in PL/SQL always requires EXECUTE IMMEDIATE or DBMS_SQL.PARSE; DDL implicitly commits — design PL/SQL to account for this transactional behavior.
Meaning: A RETURNING ... INTO clause was used in an EXECUTE IMMEDIATE call but the SQL statement is not an INSERT, UPDATE, or DELETE — RETURNING is only valid with DML statements.
Root Cause: EXECUTE IMMEDIATE 'SELECT col FROM t' INTO v_var — this is incorrect; SELECT with INTO should not use the RETURNING syntax; developer confused RETURNING INTO (DML result capture) with the INTO clause in SELECT INTO; RETURNING INTO only works with DML that modifies rows.
Solution: For DML: EXECUTE IMMEDIATE 'INSERT INTO t VALUES (:1) RETURNING id INTO :2' USING v_val RETURNING INTO v_id; for SELECT: EXECUTE IMMEDIATE 'SELECT col FROM t WHERE id=:1' INTO v_result USING v_id; remember: SELECT uses INTO, DML uses RETURNING INTO — syntactically different clauses used with EXECUTE IMMEDIATE.
Meaning: A function called from within a SQL query (SELECT list, WHERE clause) attempted to perform a DDL statement, COMMIT, or ROLLBACK, which is prohibited in that context.
Root Cause: PL/SQL function called from SQL performing COMMIT inside; user-defined function in SELECT list calling EXECUTE IMMEDIATE 'TRUNCATE TABLE ...' or INSERT/UPDATE; COMMIT in a function breaks read consistency for the calling query; DDL has an implicit commit that conflicts with the calling DML context.
Solution: If COMMIT is truly needed inside a function called from SQL, use PRAGMA AUTONOMOUS_TRANSACTION: CREATE OR REPLACE FUNCTION my_func RETURN NUMBER IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN ... COMMIT; RETURN val; END; however, avoid calling functions with side effects from SQL queries; restructure logic to separate query execution from DML/DDL operations.
Meaning: The archiver process could not archive an online redo log because all archive destinations are either full, unreachable, or in DEFERRED state with no valid destination remaining.
Root Cause: All LOG_ARCHIVE_DEST_n destinations are either DEFERRED, INACTIVE, or in ERROR state; local archive destination full AND remote standby destination unavailable simultaneously; MANDATORY destinations all failed causing archiving to halt; DB_RECOVERY_FILE_DEST full with no secondary destination configured.
Solution: Check all archive destination states: SELECT DEST_ID, STATUS, TARGET, ERROR FROM V$ARCHIVE_DEST WHERE STATUS != 'INACTIVE'; free space in the local archive destination or FRA: RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1'; re-enable a deferred destination: ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; ensure at least one non-MANDATORY destination exists as a failsafe.
Meaning: The standby database's FAL (Fetch Archive Log) process attempted to request missing archived logs from the primary but the request was rejected, leaving an unresolved archive gap on the standby.
Root Cause: FAL_SERVER on the standby is misconfigured or points to a wrong primary service name; primary database's listener not recognizing the FAL service; network connectivity between standby and primary FAL port unavailable; LOG_ARCHIVE_DEST parameter not configured for FAL requests on primary.
Solution: Verify FAL parameters on standby: SELECT VALUE FROM V$PARAMETER WHERE NAME IN ('fal_server','fal_client'); test FAL service connectivity: tnsping fal_server_service; on primary, verify FAL service is registered: lsnrctl status; manually fetch missing logs: ALTER DATABASE REGISTER LOGFILE '/path/archlog.arc'; configure FAL_SERVER=PRIMARY_SERVICE and FAL_CLIENT=STANDBY_SERVICE on the standby.
Meaning: The primary database is configured in MAXIMUM AVAILABILITY or MAXIMUM PROTECTION mode, which requires at least one synchronized standby, but no eligible standby is available.
Root Cause: Data Guard protection mode set to MAXIMUM PROTECTION but the only standby is lagging or disconnected; switchover/failover left the configuration without a functional standby meeting the SYNC requirement; standby network connectivity lost while primary is in a protection mode requiring acknowledgment.
Solution: Immediate relief: downgrade protection mode: ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE; this relaxes the requirement and allows the primary to continue without standby acknowledgment; investigate why the standby is disconnected; restore standby connectivity and catch up the apply gap; then re-upgrade protection mode: ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY.
Meaning: The Remote File Server (RFS) process on the standby rejected the redo transport connection because the primary's shipping credentials are not authenticated.
Root Cause: Oracle password file on the primary and standby are out of sync; SYS password changed on primary but not replicated to standby via password file; password file was recreated on one side but not the other; DB_UNIQUE_NAME mismatch in the Data Guard configuration.
Solution: Copy the primary's password file to the standby: scp $ORACLE_HOME/dbs/orapwPRIMARY standby_host:$ORACLE_HOME/dbs/orapwSTANDBY; verify DB_UNIQUE_NAME on both: SELECT DB_UNIQUE_NAME FROM V$DATABASE; check LOG_ARCHIVE_DEST_n on primary has correct DB_UNIQUE_NAME for the standby; after copying password file, restart MRP on standby: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT.
Meaning: An error occurred during the application of redo on the standby database that the primary's Data Guard framework classified as a recovery error, potentially pausing redo transport.
Root Cause: Block corruption found on the standby during redo apply that does not exist on the primary; redo apply encountered an incompatible SCN; standby redo log (SRL) configuration mismatch; redo apply process encountered an error in the standby's control file record for the change being applied.
Solution: Check standby alert log: grep ORA-16009 $ORACLE_BASE/diag/rdbms/.../alert_*.log -A20; check V$DATAGUARD_STATUS for detailed messages; attempt to recover specific block on standby: RMAN> RECOVER DATAFILE n BLOCK m FROM SERVICE primary; restart MRP if it stopped: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; if corruption persists, restore the affected datafile from primary.
Meaning: An operation (INSERT, UPDATE, ALTER, CREATE INDEX) targeted an object that is currently in the Oracle Recycle Bin (prefixed with BIN$), which is not available for normal DML or DDL operations.
Root Cause: Application or maintenance script referencing an object by its recycle bin name (BIN$xxxxx==0); object was dropped with DROP TABLE (without PURGE) and moved to the recycle bin; trigger or synonym still pointing to the recycle bin version of an object after it was dropped.
Solution: To restore the object: FLASHBACK TABLE table_name TO BEFORE DROP; to permanently remove it: PURGE TABLE table_name; to view recycle bin contents: SELECT OBJECT_NAME, ORIGINAL_NAME, OPERATION FROM DBA_RECYCLEBIN WHERE OWNER='MY_SCHEMA'; to prevent recycle bin usage: DROP TABLE t PURGE; or disable recycle bin globally: ALTER SYSTEM SET RECYCLEBIN=OFF.
Meaning: FLASHBACK TABLE table_name TO BEFORE DROP or PURGE TABLE table_name failed because the specified object is not currently in the Recycle Bin.
Root Cause: Table was dropped with PURGE clause (bypassing recycle bin); recycle bin was purged (PURGE RECYCLEBIN) since the table was dropped; object was dropped while RECYCLEBIN=OFF; wrong table name specified in the FLASHBACK/PURGE command; the object was purged due to recycle bin space pressure (oldest items purged first).
Solution: Check recycle bin: SELECT OBJECT_NAME, ORIGINAL_NAME, DROPTIME FROM USER_RECYCLEBIN ORDER BY DROPTIME DESC; if not found, the object was permanently deleted; restore from RMAN backup: RMAN> RESTORE TABLE schema.table_name UNTIL TIME "TO_DATE('2024-01-15 02:00:00','YYYY-MM-DD HH24:MI:SS')" AUXILIARY DESTINATION '/tmp/aux' REMAP TABLE schema.table_name:table_name_restored; this requires Oracle 12.1+ RMAN granular restore.
Meaning: A FLASHBACK TO BEFORE DROP was attempted on an object type that the Recycle Bin does not support — such as a tablespace, cluster, or database link.
Root Cause: Oracle Recycle Bin only supports regular heap tables and their dependent objects (indexes, constraints, triggers); FLASHBACK TABLE cannot recover a dropped VIEW, SEQUENCE, PROCEDURE, or PACKAGE — these are permanently gone when dropped; attempting FLASHBACK on a non-table object type.
Solution: For views, procedures, packages: retrieve DDL from a backup or from a pre-existing AWR/ASH repository: SELECT SQL_TEXT FROM DBA_HIST_SQLTEXT WHERE SQL_TEXT LIKE '%CREATE VIEW%view_name%'; or use Data Pump backup: impdp ... INCLUDE=VIEW:"LIKE 'MY_VIEW'" SQLFILE=ddl_only.sql; for tables: FLASHBACK TABLE my_table TO BEFORE DROP; always use DDL export (expdp CONTENT=METADATA_ONLY) for regular schema backups.
Meaning: An NLS parameter string passed as the optional third argument to TO_DATE, TO_CHAR, TO_NUMBER, or a similar function contains an invalid NLS parameter name or value.
Root Cause: Typo in NLS parameter name: 'NLS_DATE_LANGUAG=AMERICAN' (missing E); invalid NLS parameter value: 'NLS_DATE_LANGUAGE=ENGLISH' (should be 'AMERICAN' for US English); mixing NLS parameters that conflict; unsupported NLS territory name in the NLS_TERRITORY parameter.
Solution: Verify valid NLS parameter names: SELECT PARAMETER, VALUE FROM NLS_SESSION_PARAMETERS; valid NLS_DATE_LANGUAGE values: SELECT VALUE FROM V$NLS_VALID_VALUES WHERE PARAMETER='LANGUAGE'; correct usage: TO_DATE('01-JAN-2024','DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'); TO_CHAR(SYSDATE,'Day','NLS_DATE_LANGUAGE=FRENCH'); ensure no typos in parameter names.
Meaning: An operation combined a CHAR/VARCHAR2/CLOB value with an NCHAR/NVARCHAR2/NCLOB value in a context that requires the same character set (e.g., concatenation, CASE expression, UNION).
Root Cause: Concatenating VARCHAR2 || NVARCHAR2 without conversion; CASE WHEN clause returning VARCHAR2 in one branch and NVARCHAR2 in another; UNION query mixing CLOB and NCLOB columns in the same position; implicit conversion between national character set (NLS_NCHAR_CHARACTERSET) and database character set failing.
Solution: Use explicit conversion: CAST(nvarchar2_col AS VARCHAR2(100)); or TO_CHAR(nvarchar2_col); or concatenate with conversion: varchar2_col || TO_CHAR(nvarchar2_col); in UNION queries: ensure both SELECT lists use the same character type in corresponding positions; verify database character sets: SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME IN ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET').
Meaning: Oracle cannot find or access the NLS data files needed to initialize the National Language Support environment for the specified NLS_LANG setting.
Root Cause: NLS_LANG environment variable set to an unsupported or misspelled character set: NLS_LANG=AMERICAN_AMERICA.UTF9 (UTF9 doesn't exist); ORACLE_HOME/oracore/zoneinfo or ORACLE_HOME/nls/data directories missing or inaccessible; incomplete Oracle client installation missing NLS data files; NLS_LANG format must be: LANGUAGE_TERRITORY.CHARACTERSET.
Solution: Check NLS_LANG: echo $NLS_LANG; valid format: AMERICAN_AMERICA.AL32UTF8 or ENGLISH_UNITED KINGDOM.WE8MSWIN1252; list valid character sets: ls $ORACLE_HOME/nls/data/; unset NLS_LANG to use database default: unset NLS_LANG; verify ORACLE_HOME NLS data: ls $ORACLE_HOME/nls/data/old/ should list character set files; reinstall Oracle client if NLS data files are missing.
Meaning: DBMS_REPCAT.CREATE_MASTER_REPGROUP or CREATE_SNAPSHOT_REPGROUP failed because a replication group with the same name already exists.
Root Cause: Idempotent replication setup script re-creating a repgroup that already exists from a prior partial setup; replication group name collision; running setup scripts on a database that already has replication partially configured.
Solution: Check existing repgroups: SELECT GNAME, STATUS FROM DBA_REPGROUP; drop existing group if needed: DBMS_REPCAT.DROP_MASTER_REPGROUP(gname => 'MY_GROUP', drop_contents => TRUE, all_sites => FALSE); or use defensive creation: DECLARE v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM DBA_REPGROUP WHERE GNAME='MY_GROUP'; IF v_count=0 THEN DBMS_REPCAT.CREATE_MASTER_REPGROUP('MY_GROUP'); END IF; END; note: Advanced Replication is deprecated in 19c+ — consider migrating to GoldenGate or Streams.
Meaning: A DBMS_AQ.ENQUEUE or DBMS_AQ.DEQUEUE operation failed because the specified queue name does not exist in the database.
Root Cause: Queue not created before messaging operations; wrong schema qualifier on the queue name (schema.queue_name); queue was dropped and not recreated after a schema migration; queue name case mismatch (AQ queue names are stored uppercase).
Solution: Verify queue existence: SELECT OWNER, NAME, QUEUE_TYPE, ENQUEUE_ENABLED, DEQUEUE_ENABLED FROM DBA_QUEUES WHERE OWNER='MY_SCHEMA'; create if missing: DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table=>'MY_QT', queue_payload_type=>'RAW'); DBMS_AQADM.CREATE_QUEUE(queue_name=>'MY_Q', queue_table=>'MY_QT'); DBMS_AQADM.START_QUEUE('MY_Q'); verify queue is started: STATUS should be READY not STOPPED.
Meaning: An attempt was made to cancel or modify a queue propagation job using DBMS_JOB directly, instead of the proper DBMS_AQADM.UNSCHEDULE_PROPAGATION API.
Root Cause: DBA attempting to stop queue propagation by breaking the DBMS_JOB job that drives it; AQ propagation jobs are internally managed by Oracle AQ and must be controlled through the AQ API; DBMS_JOB.REMOVE on an AQ propagation job can corrupt the AQ metadata.
Solution: Use the proper AQ API: DBMS_AQADM.UNSCHEDULE_PROPAGATION(queue_name => 'MY_QUEUE', destination => 'MY_DBLINK'); to pause: DBMS_AQADM.DISABLE_PROPAGATION_SCHEDULE(queue_name => 'MY_QUEUE', destination => 'MY_DBLINK'); to resume: DBMS_AQADM.ENABLE_PROPAGATION_SCHEDULE; view propagation schedules: SELECT * FROM DBA_QUEUE_SCHEDULES; never manipulate AQ propagation jobs directly through DBMS_JOB.
Meaning: DBMS_LOGMNR.START_LOGMNR was called without first adding any log files using DBMS_LOGMNR.ADD_LOGFILE, and CONTINUOUS_MINE option was not specified.
Root Cause: LogMiner session started without adding redo or archived logs first; incorrect LogMiner workflow — ADD_LOGFILE must be called before START_LOGMNR when not using CONTINUOUS_MINE; start time/SCN range specified but no log files cover that range.
Solution: Correct LogMiner workflow: DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/arch/1_100.arc', OPTIONS => DBMS_LOGMNR.NEW); DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/arch/1_101.arc', OPTIONS => DBMS_LOGMNR.ADDFILE); DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); SELECT SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_NAME='MY_TABLE'; DBMS_LOGMNR.END_LOGMNR; or use CONTINUOUS_MINE: DBMS_LOGMNR.START_LOGMNR(STARTTIME => start_time, OPTIONS => DBMS_LOGMNR.CONTINUOUS_MINE + DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG).
Meaning: LogMiner encountered an unrecoverable internal error and the current LogMiner session must be ended and restarted.
Root Cause: Dictionary built from online catalog becomes invalid when DDL changes occur mid-session; redo log file added to the session is corrupt or incompatible; LogMiner running against a redo log from a different database incarnation than the dictionary; supplemental logging not enabled, causing gaps in the LogMiner data stream.
Solution: End the current session: DBMS_LOGMNR.END_LOGMNR; enable supplemental logging first: ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; rebuild from scratch with a fresh dictionary: DBMS_LOGMNR_D.BUILD(options => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS); start a new LogMiner session; ensure all log files in the session are from the same incarnation: SELECT RESETLOGS_ID FROM V$DATABASE.
Meaning: LogMiner cannot process the requested SCN range because an archived log file needed to fill a sequence gap is missing from the set of logs added to the session.
Root Cause: Archived log was purged before LogMiner analysis was completed; log files added to the session have a sequence gap (e.g., logs 1_100 and 1_102 added but 1_101 is missing); using CONTINUOUS_MINE but FRA has purged the required archived log.
Solution: Find missing logs: SELECT NAME, SEQUENCE# FROM V$ARCHIVED_LOG WHERE SEQUENCE# BETWEEN start_seq AND end_seq ORDER BY SEQUENCE#; verify which are present: SELECT * FROM V$LOGMNR_LOGS; if log is in FRA: check V$RECOVERY_FILE_DEST; restore from backup: RMAN> RESTORE ARCHIVELOG SEQUENCE n; add missing log: DBMS_LOGMNR.ADD_LOGFILE('/path/restored_1_101.arc', DBMS_LOGMNR.ADDFILE); configure sufficient archived log retention before starting LogMiner analysis sessions.
Meaning: A partition-specific ALTER TABLE operation (SPLIT PARTITION, MERGE PARTITION, MOVE PARTITION, EXCHANGE PARTITION) was attempted on a table that is not partitioned.
Root Cause: Script designed for partitioned tables executed against a non-partitioned table; wrong table name in ALTER TABLE command; table was meant to be partitioned but CREATE TABLE was run without partition clause; monitoring/maintenance script not filtering for partitioned tables before applying partition operations.
Solution: Verify if a table is partitioned: SELECT PARTITIONED FROM DBA_TABLES WHERE TABLE_NAME='MY_TABLE' AND OWNER='MY_SCHEMA'; to convert a non-partitioned table to partitioned: use online redefinition: DBMS_REDEFINITION.CAN_REDEF_TABLE('SCHEMA','TABLE',DBMS_REDEFINITION.CONS_USE_PK); or recreate with partitioning; in maintenance scripts: add WHERE PARTITIONED='YES' filter before applying partition DDL.
Meaning: A composite-partitioned table (RANGE-HASH, RANGE-LIST, etc.) CREATE TABLE or ALTER TABLE ADD PARTITION specified duplicate subpartition names within a partition definition.
Root Cause: Subpartition names must be unique across the entire table (not just within a partition); reusing the same subpartition name template across multiple partitions without making them unique; autogenerated subpartition names colliding when template-based naming is used with subpartition templates.
Solution: Use globally unique subpartition names: PARTITION p_q1 VALUES LESS THAN (DATE '2024-04-01') (SUBPARTITION sp_q1_r1, SUBPARTITION sp_q1_r2), PARTITION p_q2 VALUES LESS THAN (DATE '2024-07-01') (SUBPARTITION sp_q2_r1, SUBPARTITION sp_q2_r2); use SUBPARTITION TEMPLATE to auto-generate unique names; verify existing names: SELECT SUBPARTITION_NAME FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME='MY_TABLE'.
Meaning: A second operation on an index was attempted while the index is in the middle of an online rebuild, creation, or modification, causing a conflict.
Root Cause: Two concurrent DBA sessions both trying to rebuild the same index online (ALTER INDEX idx REBUILD ONLINE); deployment script running while a DBA is simultaneously performing index maintenance; automated index rebuild job overlapping with a manual rebuild; index in BUILDING/UNUSABLE state that another session is also modifying.
Solution: Check index status: SELECT INDEX_NAME, STATUS FROM DBA_INDEXES WHERE INDEX_NAME='MY_IDX'; wait for the active rebuild to complete: SELECT SID, OPNAME, TARGET, START_TIME, TIME_REMAINING FROM V$SESSION_LONGOPS WHERE OPNAME LIKE '%INDEX%'; ensure only one rebuild process runs per index at a time; implement advisory locks or serialization in maintenance scripts to prevent concurrent index operations.
Meaning: An ALTER TABLE DROP SUBPARTITION command was rejected because it would leave the parent partition with no subpartitions, which violates Oracle's composite partitioning rules.
Root Cause: Partition maintenance script dropping subpartitions without checking if it is the last one; aggressive partition purging that removed all but one subpartition and now tries to remove the last; incorrect partition design with only one subpartition per partition.
Solution: Check subpartition count: SELECT PARTITION_NAME, COUNT(*) FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME='MY_TABLE' AND PARTITION_NAME='MY_PARTITION' GROUP BY PARTITION_NAME; to remove the entire composite partition when it has only one subpartition left: ALTER TABLE my_table DROP PARTITION my_partition; not DROP SUBPARTITION; guard scripts: IF subpartition_count = 1 THEN drop parent partition ELSE drop subpartition END IF.
Meaning: A PL/SQL procedure or trigger explicitly raised a user-defined application error using RAISE_APPLICATION_ERROR with an error number in the reserved range -20000 to -20999.
Root Cause: Business validation logic in PL/SQL raising a meaningful application error; trigger detecting invalid business state; database API enforcing business rules and communicating violations to the calling application; the error message text provides the specific business reason for the failure.
Solution: Read the full error message — it contains the application-defined reason: RAISE_APPLICATION_ERROR(-20001, 'Order amount exceeds credit limit: '||v_amount); establish a centralized error code registry in your organization (e.g., -20001 to -20099 for order errors, -20100 to -20199 for inventory errors); in calling code: EXCEPTION WHEN OTHERS THEN IF SQLCODE BETWEEN -20999 AND -20000 THEN handle_app_error(SQLCODE, SQLERRM); ELSE RAISE; END IF.
Meaning: The error number passed to RAISE_APPLICATION_ERROR must be in the range -20000 to -20999, but a value outside this range was supplied.
Root Cause: Code using RAISE_APPLICATION_ERROR(-1, 'message') or RAISE_APPLICATION_ERROR(-30000, 'message') with an invalid range; developer unfamiliar with the reserved range; copy-paste error using an Oracle system error code instead of an application code.
Solution: Only use error numbers between -20000 and -20999: RAISE_APPLICATION_ERROR(-20001, 'Validation failed: '||SQLERRM); the range -20000 to -20999 is the only user-defined range in Oracle; outside this range, use standard exception raising: RAISE NO_DATA_FOUND; or define custom exceptions with PRAGMA EXCEPTION_INIT: my_error EXCEPTION; PRAGMA EXCEPTION_INIT(my_error, -20001); RAISE my_error.
Meaning: An operation (TABLE() function, COLUMN_VALUE, NESTED_TABLE_GET_REFS) attempted to access elements of a nested table or varray column that contains a NULL value (not an empty collection, but a NULL collection).
Root Cause: Nested table column was never populated — it is NULL, not an empty initialized collection; INSERT without specifying the nested table column leaves it NULL; difference between NULL collection (no collection at all) vs. empty collection (initialized but has no elements); TABLE(NULL_COLUMN) in SELECT raises this error.
Solution: Check for NULL before accessing: SELECT * FROM t, TABLE(t.nested_col) WHERE t.nested_col IS NOT NULL; or use NVL-equivalent: TABLE(NVL(t.nested_col, empty_type_constructor())); when inserting, initialize the collection: INSERT INTO t VALUES (1, my_nested_type()); not INSERT INTO t VALUES (1, NULL); validate with: SELECT COUNT(*) FROM my_table WHERE nested_col IS NULL — these rows will cause ORA-22908.
Meaning: An arithmetic operation produced a result that exceeds Oracle's maximum representable NUMBER value (approximately 10^125), causing an overflow condition.
Root Cause: Factorial calculation or recursive multiplication producing astronomically large intermediate values; POWER(very_large_base, very_large_exponent) exceeding NUMBER precision; financial calculations with incorrect scaling (e.g., interest rate applied as integer instead of fraction); bug in mathematical formula creating exponential growth.
Solution: Add overflow guards: IF ABS(v_number) > 1E125 THEN RAISE_APPLICATION_ERROR(-20001,'Overflow: value too large'); END IF; use LOG() and EXP() for large power calculations to keep values manageable; validate input ranges before mathematical operations; use BINARY_DOUBLE for scientific calculations that need a wider range than Oracle NUMBER; check for divide-by-zero or near-zero divisors that can produce extreme values.
Meaning: A JDBC operation was attempted on a java.sql.Connection object that has already been closed, either explicitly by the application or because the underlying Oracle session was terminated.
Root Cause: Connection returned to pool after conn.close() but still referenced by a thread; connection pool not properly managing connection lifecycle; Oracle session killed by DBA (ALTER SYSTEM KILL SESSION) while JDBC application holds the connection object; connection object shared across threads without proper synchronization.
Solution: Use try-with-resources in Java: try (Connection conn = pool.getConnection()) { ... } — ensures connection is closed on exit; configure pool with testOnBorrow=true and validationQuery='SELECT 1 FROM DUAL'; catch SQLException with ORA-17008 and obtain a new connection from the pool; never store Connection objects in class-level variables — obtain fresh from pool per request; use connection pool health checking with minEvictableIdleTimeMillis.
Meaning: A JDBC PreparedStatement, CallableStatement, or Statement object was used after it had already been closed via stmt.close(), resulting in an invalid state operation.
Root Cause: Statement closed in a finally block and then referenced in the catch block after the finally executes; PreparedStatement cached at class level, closed by one thread and then used by another; ORM framework reusing a closed statement handle; statement closed in a cleanup method while another thread still iterating its ResultSet.
Solution: Use try-with-resources: try (PreparedStatement ps = conn.prepareStatement(sql)) { ... }; never cache PreparedStatement objects in instance variables shared across threads; close statements explicitly in the correct order: ResultSet → PreparedStatement → Connection; use connection pool statement caching: oracle.jdbc.implicitStatementCacheSize=20 in JDBC URL; add statement.isClosed() check before reuse.
Meaning: A JDBC ResultSet operation (getString, getInt, etc.) was called after all rows have been fetched — ResultSet.next() returned false but column access was still attempted.
Root Cause: Application not checking the return value of rs.next() before accessing column values; code assuming at least one row is always returned; nested ResultSet loops where outer cursor's state is confused with inner cursor's; concurrent access to a single ResultSet from multiple threads.
Solution: Always check rs.next() return value: if (rs.next()) { String val = rs.getString(1); } else { /* no rows */ }; for single-row expected results: use rs.next() in an if/else, not assuming rows exist; never access column values after rs.next() returns false; for multiple rows: while (rs.next()) { processRow(rs); }; treat ORA-17011 as a programming error — always guard ResultSet access with next() checks.
Meaning: Column values were accessed from a JDBC ResultSet before ResultSet.next() was called to position the cursor on the first row, which is invalid.
Root Cause: Code accessing rs.getString(1) immediately after rs = ps.executeQuery() without calling rs.next() first; ResultSet cursor starts before the first row — next() must be called to advance to the first row; developer confused about JDBC ResultSet positioning vs Oracle cursor model.
Solution: Correct JDBC pattern: ResultSet rs = ps.executeQuery(); if (rs.next()) { String val = rs.getString("column_name"); } else { /* empty result */ }; a freshly executed ResultSet is positioned before the first row — always call next() before any column access; use rs.getRow() to check current position; for scrollable ResultSets: rs.first() or rs.absolute(1) to position at the first row.
Meaning: The Oracle JDBC Thin driver received an unexpected or malformed packet from the Oracle server, indicating the TNS protocol was violated, typically due to a network corruption or version mismatch.
Root Cause: Network device (firewall, load balancer, SSL terminator) modifying Oracle TNS packets; JDBC Thin driver version significantly different from server version; corrupt network packet from a TCP retransmission; Oracle server crashing mid-response and sending a malformed termination packet.
Solution: Upgrade JDBC driver to match server version: use ojdbc8.jar for Oracle 19c; verify no network appliance is modifying Oracle traffic: test with direct connection bypassing LB/firewall; check server alert log for crashes coinciding with ORA-17401 occurrence; add JDBC connection URL parameter: oracle.net.DISABLE_OOB=true to disable out-of-band breaks that can confuse some network devices.
Meaning: A Flashback Query (SELECT ... AS OF TIMESTAMP) or AS OF SCN query cannot find undo data going back to the specified point in time, because the undo for that timestamp has been overwritten.
Root Cause: Flashback query target time is older than the oldest available undo snapshot; UNDO_RETENTION parameter too low to preserve undo back to the requested point; undo tablespace pressure caused old undo to be overwritten before UNDO_RETENTION expired; requesting a timestamp more than UNDO_RETENTION seconds in the past.
Solution: Check how far back undo extends: SELECT MIN(BEGIN_TIME), MAX(END_TIME), AVG(UNDOBLKS) FROM V$UNDOSTAT; increase UNDO_RETENTION: ALTER SYSTEM SET UNDO_RETENTION=86400 (24 hours); enable GUARANTEE: ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE; for longer flashback windows use Flashback Data Archive: CREATE FLASHBACK ARCHIVE my_archive TABLESPACE users QUOTA 10G RETENTION 1 YEAR; ALTER TABLE my_table FLASHBACK ARCHIVE my_archive.
Meaning: ALTER DATABASE FLASHBACK ON failed because Oracle could not enable Flashback Database logging due to a configuration or resource issue.
Root Cause: DB_RECOVERY_FILE_DEST (FRA) not configured; FRA is too small to hold flashback logs; database not in ARCHIVELOG mode (required for Flashback Database); insufficient disk space in the FRA path; FRA path permissions incorrect for oracle user.
Solution: Ensure ARCHIVELOG mode is enabled; configure FRA: ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/fra' SCOPE=BOTH; ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=100G SCOPE=BOTH; then enable flashback: ALTER DATABASE FLASHBACK ON; verify: SELECT FLASHBACK_ON FROM V$DATABASE; minimum FRA size should be several times DB_FLASHBACK_RETENTION_TARGET × redo generation rate; monitor FRA usage: SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE.
Meaning: A Flashback Database operation cannot be completed because the required flashback logs or redo logs for the requested flashback point are missing or have been purged from the FRA.
Root Cause: Flashback target time is beyond the available flashback log retention; FRA space pressure caused oldest flashback logs to be purged before FLASHBACK_RETENTION_TARGET was met; flashback logs were accidentally deleted from FRA; flashback logging was temporarily disabled (flashback turned off and on), creating a gap.
Solution: Check available flashback window: SELECT OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG; if insufficient, flashback database cannot reach the target — consider RMAN PITR instead: RMAN> RESTORE DATABASE UNTIL TIME '...'; RECOVER DATABASE UNTIL TIME '...'; ALTER DATABASE OPEN RESETLOGS; increase FRA size and DB_FLASHBACK_RETENTION_TARGET before next maintenance window; monitor with: SELECT ESTIMATED_FLASHBACK_SIZE/1024/1024/1024 REQUIRED_GB FROM V$FLASHBACK_DATABASE_STAT.
Meaning: A direct ALTER TABLE operation (adding columns, changing types, moving tablespace) was rejected because the table requires Online Redefinition due to its type or current state.
Root Cause: Attempting to directly ALTER an IOT (Index-Organized Table) with operations it doesn't support; trying to add column to a table with ROW MOVEMENT disabled in a way that requires DBMS_REDEFINITION; attempting incompatible structural changes to a table that Oracle requires to be redefined online.
Solution: Use DBMS_REDEFINITION for online table restructuring: DBMS_REDEFINITION.CAN_REDEF_TABLE('SCHEMA','TABLE'); CREATE TABLE interim_table AS SELECT * FROM original_table WHERE 1=2 (with new structure); DBMS_REDEFINITION.START_REDEF_TABLE('SCHEMA','ORIGINAL','INTERIM'); DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCHEMA','ORIGINAL','INTERIM'); DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCHEMA','ORIGINAL','INTERIM'); allows online structural changes without downtime.
Meaning: ALTER TABLE SHRINK SPACE was attempted on a segment type that does not support the shrink operation — such as a LOB segment stored outside BasicFile, an IOT overflow, or a cluster.
Root Cause: Attempting to shrink a SecureFiles LOB segment (only BasicFile LOBs support shrink); trying to shrink an IOT overflow segment directly; using SHRINK on a table with compressed rows; SHRINK requires ROW MOVEMENT to be enabled but segments in some types don't support it.
Solution: Enable row movement first: ALTER TABLE t ENABLE ROW MOVEMENT; for BasicFile LOBs: ALTER TABLE t MODIFY LOB (col) (SHRINK SPACE); for SecureFiles: no shrink available — use CREATE TABLE AS SELECT to rebuild; check segment type: SELECT SEGMENT_TYPE FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MY_TABLE'; for general space reclamation: ALTER TABLE t MOVE TABLESPACE same_ts; then: ALTER INDEX idx REBUILD.
Meaning: Oracle's query optimizer found an optimizer hint in the SQL comment (/*+ hint */) that it could not apply, either because the hint is syntactically invalid, references a non-existent object, or is inapplicable to the query structure.
Root Cause: Hint references a table alias that doesn't exist in the query; INDEX hint specifying a non-existent index: /*+ INDEX(t idx_nonexistent) */; typo in hint name: /*+ NO_HASHJOIN */ (should be /*+ NO_HASH_JOIN */); hints on views or subqueries that Oracle cannot push down; conflicting hints like /*+ ALL_ROWS FIRST_ROWS(10) */.
Solution: Validate hints are being applied: check execution plan OUTLINE HINTS section or use: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id, null, 'OUTLINE +NOTE')); the NOTE section shows why hints were ignored; use exact table aliases in hints: /*+ INDEX(t1 idx_orders_date) */ where t1 is the alias used in FROM clause; verify index name: SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='ORDERS'.
Meaning: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE or SET TRANSACTION READ ONLY was issued but the session already has an active distributed transaction (involving a DB link) that has incompatible transaction characteristics.
Root Cause: SQL spanning multiple databases via DB links makes serializable isolation difficult to enforce globally; SET TRANSACTION must be the first statement in a transaction — if a DB link query already started the distributed transaction, SET TRANSACTION will fail; application architecture mixing distributed transactions with serializable isolation.
Solution: SET TRANSACTION characteristics must be set before the first DML or query in the transaction: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; (first statement, before any DB link queries); restructure application to not mix distributed DB link operations with serializable transactions; use READ COMMITTED isolation for distributed transactions which is compatible with cross-database queries; or eliminate the need for serializable isolation through application-level conflict detection.
Meaning: A DML operation over a database link failed, and all previous operations in the distributed transaction must now be rolled back to maintain global consistency across all participating databases.
Root Cause: Remote database returned an error during a two-phase commit distributed UPDATE/INSERT/DELETE; network failure mid-transaction leaving the remote database in an uncertain state; remote database ran out of undo space while processing the distributed DML; constraint violation on the remote database during a distributed INSERT.
Solution: Application MUST issue ROLLBACK immediately: ROLLBACK; — no other DML is possible in this transaction after ORA-02055; design distributed transactions to be small and fast to minimize the failure window; implement compensating transactions for business logic; check DBA_2PC_PENDING for any in-doubt transactions: SELECT LOCAL_TRAN_ID, STATE FROM DBA_2PC_PENDING; resolve manually if needed: DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY.
Meaning: ALTER SYSTEM SET RESOURCE_MANAGER_PLAN or a Resource Manager API call referenced a plan that does not exist in DBA_RSRC_PLANS.
Root Cause: Resource plan name typo; plan was dropped between the time of configuration and when it was activated; attempting to activate a plan pending submission (not yet validated); plan exists in a different CDB/PDB context.
Solution: List existing plans: SELECT PLAN, STATUS FROM DBA_RSRC_PLANS; verify plan is active (not pending): STATUS='ACTIVE'; to submit a pending plan: DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA; to activate: ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='MY_PLAN'; recreate if missing: DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA; DBMS_RESOURCE_MANAGER.CREATE_PLAN('MY_PLAN','Description'); DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA; then activate.
Meaning: A user was assigned to, or an attempt was made to switch to, a Resource Manager consumer group that does not exist in DBA_RSRC_CONSUMER_GROUPS.
Root Cause: Consumer group name typo; group was dropped while users were still mapped to it; automated session-switching trigger referencing a deleted consumer group; mapping table (DBA_RSRC_GROUP_MAPPINGS) referencing a group that was dropped without updating the mappings.
Solution: List existing consumer groups: SELECT CONSUMER_GROUP FROM DBA_RSRC_CONSUMER_GROUPS; check user-to-group mappings: SELECT GRANTEE, GRANTED_GROUP FROM DBA_RSRC_CONSUMER_GROUP_PRIVS; create the missing group: DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA; DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP('MY_GROUP', 'description'); DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA; then grant: DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('username','MY_GROUP',FALSE).
Meaning: An operation (certain ALTER TABLE forms, partition DDL) was rejected because the target table has the INMEMORY attribute enabled, and the requested operation is incompatible with In-Memory Column Store management.
Root Cause: Attempting certain partition operations on an INMEMORY table that require the In-Memory population to be flushed first; combining INMEMORY with operations not yet supported by the In-Memory feature in the current Oracle version; ALTER TABLE with conflicting INMEMORY directives.
Solution: Disable INMEMORY on the table before the operation: ALTER TABLE my_table NO INMEMORY; perform the required DDL operation; re-enable after: ALTER TABLE my_table INMEMORY PRIORITY CRITICAL; monitor repopulation: SELECT v.segment_name, v.bytes_not_populated FROM V$IM_SEGMENTS v; check In-Memory compatibility: SELECT * FROM V$OPTION WHERE PARAMETER='In-Memory Column Store'.
Meaning: A Parallel DML operation (INSERT /*+ PARALLEL */, UPDATE with parallel DOP) was attempted on a table with the INMEMORY attribute, which has restrictions on parallel DML.
Root Cause: INMEMORY tables have constraints on parallel DML because maintaining In-Memory column store consistency during parallel writes requires special handling; application or ETL using PDML hints on tables that were later converted to In-Memory without updating the DML approach.
Solution: Disable PARALLEL DML for the session: ALTER SESSION DISABLE PARALLEL DML; or remove PARALLEL hint from DML statements targeting INMEMORY tables; alternatively temporarily disable INMEMORY: ALTER TABLE t NO INMEMORY; run the parallel DML; re-enable: ALTER TABLE t INMEMORY; check if INMEMORY is needed for this write-heavy table — INMEMORY is optimized for read-heavy analytical queries, not high-volume DML targets.
Meaning: An AUDIT POLICY, NOAUDIT POLICY, or DROP AUDIT POLICY statement referenced a unified audit policy that does not exist in the database.
Root Cause: Policy name typo; policy was dropped before being disabled; attempting to reference a traditional audit policy using the new unified audit syntax; policy exists in a different PDB in a CDB environment.
Solution: List existing unified audit policies: SELECT POLICY_NAME, ENABLED_OPT, USER_NAME FROM AUDIT_UNIFIED_ENABLED_POLICIES; check all policies (enabled and disabled): SELECT POLICY_NAME FROM AUDIT_UNIFIED_POLICIES; create missing policy: CREATE AUDIT POLICY my_policy ACTIONS SELECT ON hr.employees, INSERT ON hr.employees; enable it: AUDIT POLICY my_policy; in CDB: unified audit policies are PDB-local — ensure you are connected to the correct PDB.
Meaning: CREATE AUDIT POLICY failed because a unified audit policy with the same name already exists in the current database or PDB.
Root Cause: Idempotent audit policy setup script not checking existence; policy created in a previous deployment attempt that failed partway through; re-running database hardening scripts that create audit policies without first checking if they exist.
Solution: Use defensive policy creation: DECLARE v_cnt NUMBER; BEGIN SELECT COUNT(*) INTO v_cnt FROM AUDIT_UNIFIED_POLICIES WHERE POLICY_NAME='MY_POLICY'; IF v_cnt=0 THEN EXECUTE IMMEDIATE 'CREATE AUDIT POLICY my_policy ACTIONS SELECT ON hr.employees'; END IF; END; or drop and recreate: BEGIN EXECUTE IMMEDIATE 'DROP AUDIT POLICY my_policy'; EXCEPTION WHEN OTHERS THEN NULL; END; EXECUTE IMMEDIATE 'CREATE AUDIT POLICY my_policy ACTIONS ...'.
Meaning: A Data Pump operation command (ADD_FILE, STOP_JOB, CONTINUE_JOB) was issued but is not valid for the current state of the Data Pump job (e.g., trying to STOP a job that is already STOPPED).
Root Cause: Attempting to CONTINUE a job that is not in a SUSPENDED state; trying to ADD_FILE to a job that has already completed; issuing STOP_JOB to a job that was already stopped or completed; job state machine transition violated.
Solution: Check job state: SELECT JOB_NAME, STATE, DEGREE FROM DBA_DATAPUMP_JOBS WHERE STATE != 'NOT RUNNING'; valid state transitions: EXECUTING → STOP → STOPPED → CONTINUE → EXECUTING; to resume a stopped job: expdp ATTACH=job_name → CONTINUE_CLIENT; monitor current state before issuing commands; valid commands per state are documented in Oracle Data Pump documentation.
Meaning: The Data Pump job specification contains an invalid or unsupported combination of parameters that cannot be used together for the requested operation.
Root Cause: Using FULL=Y with SCHEMAS= or TABLES= simultaneously; combining TRANSPORTABLE=ALWAYS with CONTENT=METADATA_ONLY; specifying REMAP_SCHEMA with a FULL export; using NETWORK_LINK without sufficient privileges; mixing incompatible CONTENT and ACCESS_PARAMETERS options.
Solution: Review Data Pump parameter compatibility: FULL=Y and SCHEMAS/TABLES are mutually exclusive; for schema-level export: SCHEMAS=HR without FULL=Y; for table-level: TABLES=HR.EMPLOYEES; check the Data Pump log file for the specific parameter conflict; refer to Oracle documentation for valid parameter combinations by mode (FULL, SCHEMA, TABLE, TABLESPACE, TRANSPORTABLE); use parameter files to organize complex expdp/impdp options.
Meaning: The Data Pump internal master control table (SYS_EXPORT_FULL_01, SYS_IMPORT_FULL_01, etc.) encountered an error, causing the job to fail or become inconsistent.
Root Cause: Tablespace quota exhausted mid-job, preventing master table from being written; user's default tablespace is full; another session dropped or modified the master table while the job was running; DBA accidentally truncated or dropped the SYS_EXPORT* table thinking it was orphaned.
Solution: Check for orphaned master tables: SELECT TABLE_NAME FROM DBA_TABLES WHERE TABLE_NAME LIKE 'SYS_%PORT%' OR TABLE_NAME LIKE '%EXPORT%'; kill the associated Data Pump job if running; clean up orphaned master table: DROP TABLE SYS_EXPORT_FULL_01; ensure the user running expdp has sufficient tablespace quota: ALTER USER expdp_user QUOTA UNLIMITED ON users; restart the Data Pump job after cleaning up.
Meaning: One of the Data Pump worker processes encountered an unrecoverable fatal error and terminated, causing the entire Data Pump job to abort.
Root Cause: ORA-03113 (connection lost) to a remote database during network-linked export/import; out of memory error in a worker process; Oracle internal error (ORA-00600) in a worker; I/O error writing the dump file; insufficient temp space for a worker's sort operation.
Solution: Check the Data Pump log file for the specific error preceding ORA-39126; check the alert log for associated ORA-600 or ORA-07445; if network link failure: verify the DB link is stable and retry: expdp ATTACH=job_name CONTINUE_CLIENT; for memory issues: increase PGA_AGGREGATE_TARGET; for dump file I/O: check filesystem space: df -h /dump_dir; increase PARALLEL count gradually to identify if a specific worker is failing.
Diagnose a live Oracle error. Pick the correct fix — just like a real DBA on call.