{"id":204,"date":"2024-12-15T13:18:34","date_gmt":"2024-12-15T13:18:34","guid":{"rendered":"https:\/\/w3buddy.com\/?p=204"},"modified":"2026-01-15T13:12:21","modified_gmt":"2026-01-15T07:42:21","slug":"how-to-recompile-invalid-schema-objects-in-oracle","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/how-to-recompile-invalid-schema-objects-in-oracle\/","title":{"rendered":"How to Recompile Invalid Schema Objects in Oracle"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Operations such as upgrades, patches, and DDL changes can invalidate schema objects. While Oracle provides automatic recompilation on demand, this process can be time-consuming and may not address complex dependencies efficiently. Proactively recompiling invalid objects can reduce runtime delays and help identify any changes that may have caused issues. This guide outlines various methods to recompile invalid schema objects.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Identifying Invalid Objects<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Before recompiling, you must identify invalid objects in the database. Use the following query on the <code>DBA_OBJECTS<\/code> view to locate invalid objects:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">-- Query 1: Check all invalid objects in the database\nCOLUMN object_name FORMAT A30\nSELECT owner,\n       object_type,\n       object_name,\n       status\nFROM   dba_objects\nWHERE  status = 'INVALID'\nORDER BY owner, object_type, object_name;\n\n-- Enhanced Query to Check Invalid Objects with Additional Details\nSET LINESIZE 150\nSET PAGESIZE 50\nCOLUMN owner FORMAT A20\nCOLUMN object_name FORMAT A30\nCOLUMN object_type FORMAT A20\nCOLUMN status FORMAT A10\nCOLUMN created FORMAT A20\nCOLUMN last_ddl_time FORMAT A20\n\nSELECT owner,\n       object_type,\n       object_name,\n       status,\n       TO_CHAR(created, 'YYYY-MM-DD HH24:MI:SS') AS created,\n       TO_CHAR(last_ddl_time, 'YYYY-MM-DD HH24:MI:SS') AS last_ddl_time\nFROM   dba_objects\nWHERE  status = 'INVALID'\nORDER BY owner, object_type, object_name;\n\n\n-- Query 2: Check invalid objects for a specific schema\nCOLUMN object_name FORMAT A30\nSELECT owner,\n       object_type,\n       object_name,\n       status\nFROM   dba_objects\nWHERE  status = 'INVALID'\nAND    owner = 'SCOTT'\nORDER BY object_type, object_name;\n\n-- Query 3: Check invalid objects with a specific name\nCOLUMN object_name FORMAT A30\nSELECT owner,\n       object_type,\n       object_name,\n       status\nFROM   dba_objects\nWHERE  status = 'INVALID'\nAND    object_name = 'MY_OBJECT'\nORDER BY owner, object_type;\n\n-- Query 4: Check invalid objects of a specific type\nCOLUMN object_name FORMAT A30\nSELECT owner,\n       object_type,\n       object_name,\n       status\nFROM   dba_objects\nWHERE  status = 'INVALID'\nAND    object_type = 'PACKAGE'\nORDER BY owner, object_name;\n\n-- Query 5: Check invalid objects with multiple criteria\nCOLUMN object_name FORMAT A30\nSELECT owner,\n       object_type,\n       object_name,\n       status\nFROM   dba_objects\nWHERE  status = 'INVALID'\nAND    owner = 'SCOTT'\nAND    object_type = 'FUNCTION'\nORDER BY object_name;\n\n-- Query 6: Check invalid objects excluding specific types\nCOLUMN object_name FORMAT A30\nSELECT owner,\n       object_type,\n       object_name,\n       status\nFROM   dba_objects\nWHERE  status = 'INVALID'\nAND    object_type NOT IN ('VIEW', 'SYNONYM')\nORDER BY owner, object_type, object_name;\n\n-- Query 7: Check invalid objects created after a specific date\nCOLUMN object_name FORMAT A30\nSELECT owner,\n       object_type,\n       object_name,\n       status,\n       created\nFROM   dba_objects\nWHERE  status = 'INVALID'\nAND    created > TO_DATE('2024-01-01', 'YYYY-MM-DD')\nORDER BY created DESC;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This query provides a list of invalid objects, helping you decide the most suitable recompilation method.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Methods for Recompiling Invalid Objects<\/strong><\/h2>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>1. Manual Recompilation<\/strong><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">For a small number of invalid objects, manual recompilation might suffice. Use the <code>ALTER<\/code> command for various object types:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">ALTER PACKAGE my_package COMPILE;\nALTER PACKAGE my_package COMPILE BODY;\nALTER PROCEDURE my_procedure COMPILE;\nALTER FUNCTION my_function COMPILE;\nALTER TRIGGER my_trigger COMPILE;\nALTER VIEW my_view COMPILE;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Alternatively, use the <code>DBMS_DDL<\/code> package for PL\/SQL objects:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');\nEXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');\nEXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');\nEXEC DBMS_DDL.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');\nEXEC DBMS_DDL.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>2. Using the UTL_RECOMP Package<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The <code>UTL_RECOMP<\/code> package provides procedures for serial and parallel recompilation of invalid objects:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code><strong>RECOMP_SERIAL<\/strong><\/code>: Recompiles objects serially, one at a time.<\/li>\n\n\n\n<li><code><strong>RECOMP_PARALLEL<\/strong><\/code>: Recompiles objects in parallel using multiple threads.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Procedure Definitions<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">PROCEDURE RECOMP_SERIAL(\n   schema   IN   VARCHAR2    DEFAULT NULL,\n   flags    IN   PLS_INTEGER DEFAULT 0);\n\nPROCEDURE RECOMP_PARALLEL(\n   threads  IN   PLS_INTEGER DEFAULT NULL,\n   schema   IN   VARCHAR2    DEFAULT NULL,\n   flags    IN   PLS_INTEGER DEFAULT 0);<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\"><strong>Parameters<\/strong><\/h5>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code><strong>schema<\/strong><\/code>: The schema whose invalid objects are recompiled. If <code>NULL<\/code>, all invalid objects in the database are recompiled.<\/li>\n\n\n\n<li><code><strong>threads<\/strong><\/code>: Number of threads for parallel execution. Defaults to the <code>job_queue_processes<\/code> parameter value.<\/li>\n\n\n\n<li><code><strong>flags<\/strong><\/code>: Reserved for internal diagnostics.<\/li>\n<\/ul>\n\n\n\n<h5 class=\"wp-block-heading\"><strong>Examples<\/strong><\/h5>\n\n\n\n<p class=\"wp-block-paragraph\">Schema-level recompilation:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">EXEC UTL_RECOMP.recomp_serial('W3BUDDY');\nEXEC UTL_RECOMP.recomp_parallel(4, 'W3BUDDY');<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Database-level recompilation:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">EXEC UTL_RECOMP.recomp_serial();\nEXEC UTL_RECOMP.recomp_parallel(4);<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Restrictions:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Parallel execution uses the job queue. Existing jobs are disabled during recompilation.<\/li>\n\n\n\n<li>Must be run as <code>SYS<\/code> or a user with <code>SYSDBA<\/code> privileges.<\/li>\n\n\n\n<li>Avoid concurrent DDL operations to prevent deadlocks.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>3. Using utlrp.sql and utlprp.sql Scripts<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Oracle provides these scripts for database-wide recompilation of invalid objects. Located in <code>$ORACLE_HOME\/rdbms\/admin<\/code>, they are wrappers around the <code>UTL_RECOMP<\/code> package.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code><strong>utlrp.sql<\/strong><\/code>: Calls <code>utlprp.sql<\/code> with a parameter of <code>0<\/code>.<\/li>\n\n\n\n<li><code><strong>utlprp.sql<\/strong><\/code>: Accepts a single parameter for parallelism:\n<ul class=\"wp-block-list\">\n<li><code>0<\/code>: Parallelism derived from <code>CPU_COUNT<\/code>.<\/li>\n\n\n\n<li><code>1<\/code>: Serial recompilation.<\/li>\n\n\n\n<li><code>N<\/code>: Recompilation using <code>N<\/code> threads.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Example<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SQL> @?\/rdbms\/admin\/utlprp.sql 0<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Run these scripts as <code>SYS<\/code> or a user with <code>SYSDBA<\/code> privileges.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>4. Using DBMS_UTILITY.compile_schema<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The <code>DBMS_UTILITY.compile_schema<\/code> procedure recompiles all invalid objects in a specified schema.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Example<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT', compile_all => false);<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code><strong>compile_all<\/strong><\/code>: If <code>true<\/code>, all objects are compiled, regardless of validity. Default is <code>false<\/code>.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Conclusion<\/strong><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use manual recompilation or custom scripts for smaller tasks.<\/li>\n\n\n\n<li>Leverage <code>UTL_RECOMP<\/code> or Oracle-provided scripts (<code>utlrp.sql<\/code> and <code>utlprp.sql<\/code>) for database-wide recompilation.<\/li>\n\n\n\n<li>Use <code>DBMS_UTILITY.compile_schema<\/code> for schema-specific recompilation.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Proactively recompiling invalid objects minimizes runtime errors, ensures code consistency, and reduces the impact of complex dependencies.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Operations such as upgrades, patches, and DDL changes can invalidate schema objects. While Oracle provides automatic recompilation on demand, this process can be time-consuming and may not address complex dependencies efficiently. Proactively recompiling invalid objects can reduce runtime delays and help identify any changes that may have caused issues. This guide outlines various methods to [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-204","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/204","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=204"}],"version-history":[{"count":2,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/204\/revisions"}],"predecessor-version":[{"id":207,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/204\/revisions\/207"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=204"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=204"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=204"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}