April 23, 2026
Ora 29535 Source Requires Recompilation
Source

Ora 29535 Source Requires Recompilation

services

The Oracle error ORA-29535, often accompanied by the message source requires recompilation, is a common issue encountered by database administrators and developers working with Oracle databases. This error typically occurs when a stored procedure, function, package, or other database object becomes invalid due to changes in dependent objects, such as tables, views, or other PL/SQL code. Understanding why ORA-29535 occurs, how to identify affected objects, and the steps required to resolve the issue is essential for maintaining database integrity, ensuring smooth operation, and preventing runtime failures in applications that rely on Oracle PL/SQL.

Understanding ORA-29535

ORA-29535 is a PL/SQL error that signals that a database object is invalid and must be recompiled before it can be executed successfully. This typically happens when the object’s dependencies are altered, such as changes to the structure of a table or modifications to another procedure that it calls. When Oracle detects such inconsistencies, it marks the object as invalid to prevent unexpected behavior during execution. The database will not automatically recompile invalid objects in all cases, which means administrators must intervene to restore functionality.

Common Causes of ORA-29535

Several scenarios can lead to ORA-29535

  • Changes to dependent objectsModifying a table, view, or another procedure that a PL/SQL object relies on can invalidate the object.
  • Database upgradesOracle upgrades may alter internal structures or system packages, causing dependent objects to require recompilation.
  • Manual code changesDirect edits to stored procedures, functions, or packages without recompiling dependent objects can trigger this error.
  • Environment differencesMoving objects between development, test, and production environments without recompiling may result in ORA-29535.

Identifying Invalid Objects

Before resolving ORA-29535, it is important to identify which objects are invalid. Oracle provides system views that allow administrators to quickly locate and assess these objects. Some of the most commonly used views include

  • USER_OBJECTSDisplays all objects owned by the current user, including their status (VALID or INVALID).
  • ALL_OBJECTSShows all objects accessible to the user, along with their validity status.
  • DBA_OBJECTSProvides information about all objects in the database for users with DBA privileges.

Querying these views allows administrators to pinpoint objects that have been invalidated and need recompilation. For example

SELECT object_name, object_type, statusFROM user_objectsWHERE status = 'INVALID';

This query lists all invalid objects owned by the user, providing a starting point for corrective action.

Recompilation Methods

Once invalid objects are identified, they must be recompiled. Oracle offers several methods for recompilation

  • Manual recompilationUsing theALTERcommand to recompile a specific object 
    ALTER PROCEDURE procedure_name COMPILE;
  • Recompile all objects in a schemaFor multiple invalid objects, you can use PL/SQL scripts or tools like DBMS_UTILITY 
    EXEC DBMS_UTILITY.compile_schema(schema =>'SCHEMA_NAME');
  • Automatic recompilationSome Oracle versions support recompilation on demand when an invalid object is executed, but this may not always resolve ORA-29535.

Preventive Measures

Preventing ORA-29535 from occurring frequently requires proactive database management and coding practices. Key preventive measures include

  • Dependency managementTrack dependencies between objects and avoid altering structures without considering dependent code.
  • Regular recompilationSchedule periodic recompilation of PL/SQL objects, especially after database upgrades or significant schema changes.
  • Version controlMaintain versioned scripts of stored procedures and functions to manage changes safely.
  • Testing environmentsUse development and testing environments to verify changes before applying them in production.
  • Monitoring toolsUtilize Oracle monitoring tools or custom scripts to detect invalid objects early and address them proactively.

Best Practices for Resolving ORA-29535

When encountering ORA-29535, follow these best practices to ensure a smooth resolution

  • Identify all invalid objects using system views or scripts.
  • Analyze dependencies to ensure that recompiling one object does not create additional invalidations.
  • UseALTER... COMPILEcommands for individual objects orDBMS_UTILITY.compile_schemafor bulk recompilation.
  • Test the objects after recompilation to verify correct functionality.
  • Document the cause and solution to prevent recurrence in the future.

Impact on Applications

ORA-29535 can have significant impacts on applications if not addressed promptly. Invalid objects prevent PL/SQL code from executing correctly, which can lead to application errors, failed transactions, or unexpected downtime. For mission-critical applications, unresolved ORA-29535 errors can disrupt business operations and affect end users. Therefore, understanding the root cause and applying timely recompilation is crucial for maintaining application stability and performance.

Tools and Automation

Several tools and scripts can assist with managing ORA-29535 and other compilation-related issues. Oracle Enterprise Manager (OEM) provides monitoring capabilities to detect invalid objects. Additionally, custom PL/SQL scripts can automate the detection and recompilation process, ensuring that invalid objects are addressed as soon as they occur. Automation reduces manual intervention, minimizes downtime, and ensures a more consistent approach to database maintenance.

ORA-29535, with the message source requires recompilation, is a common but manageable Oracle error. It highlights the importance of maintaining valid PL/SQL objects and understanding the dependencies within a database. By identifying invalid objects, applying appropriate recompilation methods, and implementing preventive measures, database administrators can ensure application stability and prevent recurring issues. Regular monitoring, careful management of schema changes, and adherence to best practices help minimize the occurrence of ORA-29535, ensuring that Oracle databases run smoothly and efficiently. Proper handling of this error not only improves system reliability but also enhances the overall quality of database management and development.