{"id":4186,"date":"2025-06-11T09:43:13","date_gmt":"2025-06-11T09:43:13","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&p=4186"},"modified":"2025-06-11T09:45:40","modified_gmt":"2025-06-11T09:45:40","slug":"recompile-invalid-schema-objects-in-oracle","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/oracle-dba-d2d-tasks\/recompile-invalid-schema-objects-in-oracle\/","title":{"rendered":"Recompile Invalid Schema Objects in Oracle"},"content":{"rendered":"\n
\n

Invalid schema objects can cause runtime errors and unexpected behavior\u2014especially after upgrades, patching, or DDL changes. Oracle can recompile them automatically, but it\u2019s often better to handle them proactively. This guide walks you through identifying and recompiling invalid objects using manual methods, Oracle packages, and built-in scripts.<\/p>\n<\/blockquote>\n\n\n\n

Identify Invalid Objects<\/h2>\n\n\n\n

Before recompilation, identify invalid objects using these diagnostic queries.<\/p>\n\n\n\n

All Invalid Objects in the Database<\/h3>\n\n\n\n
-- Simple View\nCOLUMN object_name FORMAT A30\nSELECT owner, object_type, object_name, status\nFROM   dba_objects\nWHERE  status = 'INVALID'\nORDER BY owner, object_type, object_name;\n\n-- Enhanced View with Timestamps\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;<\/code><\/code><\/pre>\n\n\n\n
Filtered Queries<\/h3>\n\n\n\n
-- For a specific schema\nSELECT owner, object_type, object_name, status\nFROM   dba_objects\nWHERE  status = 'INVALID' AND owner = 'SCOTT'\nORDER BY object_type, object_name;\n\n-- For a specific object name\nSELECT owner, object_type, object_name, status\nFROM   dba_objects\nWHERE  status = 'INVALID' AND object_name = 'MY_OBJECT'\nORDER BY owner, object_type;\n\n-- For a specific object type\nSELECT owner, object_type, object_name, status\nFROM   dba_objects\nWHERE  status = 'INVALID' AND object_type = 'PACKAGE'\nORDER BY owner, object_name;\n\n-- With multiple filters (e.g. schema + type)\nSELECT owner, object_type, object_name, status\nFROM   dba_objects\nWHERE  status = 'INVALID'\nAND    owner = 'SCOTT'\nAND    object_type = 'FUNCTION'\nORDER BY object_name;\n\n-- Exclude object types (e.g. VIEW, SYNONYM)\nSELECT owner, object_type, object_name, status\nFROM   dba_objects\nWHERE  status = 'INVALID'\nAND    object_type NOT IN ('VIEW', 'SYNONYM')\nORDER BY owner, object_type, object_name;\n\n-- Created after a certain date\nSELECT owner, object_type, object_name, status, created\nFROM   dba_objects\nWHERE  status = 'INVALID'\nAND    created > TO_DATE('2024-01-01', 'YYYY-MM-DD')\nORDER BY created DESC;<\/code><\/code><\/pre>\n\n\n\n
Method 1: Manual Recompilation<\/h2>\n\n\n\n
Best for small numbers of invalid objects.<\/p>\n\n\n\n
-- Basic ALTER commands\nALTER 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;\n\n-- Using DBMS_DDL\nEXEC 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><\/code><\/pre>\n\n\n\n
Method 2: Using UTL_RECOMP<\/code> Package<\/h2>\n\n\n\n
Oracle\u2019s built-in package for serial and parallel recompilation of invalid objects.<\/p>\n\n\n\n
Examples<\/h3>\n\n\n\n
-- Recompile One Schema (Serial)\nEXEC UTL_RECOMP.recomp_serial('HR');\n\n-- Recompile One Schema (Parallel)\nEXEC UTL_RECOMP.recomp_parallel(4, 'HR');\n\n-- Recompile All Schemas (Serial)\nEXEC UTL_RECOMP.recomp_serial();\n\n-- Recompile All Schemas (Parallel)\nEXEC UTL_RECOMP.recomp_parallel(4);<\/code><\/code><\/pre>\n\n\n\n
Reference<\/h3>\n\n\n\n
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><\/code><\/pre>\n\n\n\n
\ud83d\udccc Notes<\/strong>:<\/p>\n\n\n\n
    \n
  • Requires SYS<\/strong> or SYSDBA<\/strong> privileges.<\/li>\n\n\n\n
  • Avoid concurrent DDL during recompilation.<\/li>\n\n\n\n
  • Uses job queue<\/strong> (disables jobs temporarily).<\/li>\n<\/ul>\n\n\n\n
    \ud83d\udcdc Method 3: Using utlrp.sql<\/code> or utlprp.sql<\/code> Scripts<\/h2>\n\n\n\n
    Oracle-provided SQL scripts in $ORACLE_HOME\/rdbms\/admin<\/code> directory.<\/p>\n\n\n\n
    -- Recompile all invalid objects (parallelism = auto)\n@?\/rdbms\/admin\/utlprp.sql 0<\/code><\/code><\/pre>\n\n\n\n
    \ud83d\uddd2\ufe0f utlrp.sql<\/strong> simply wraps utlprp.sql<\/strong> with a default parameter of 0.<\/p>\n\n\n\n
    Parallelism Options:<\/h3>\n\n\n\n
      \n
    • 0<\/code>: Based on CPU_COUNT<\/code><\/li>\n\n\n\n
    • 1<\/code>: Serial<\/li>\n\n\n\n
    • N<\/code>: N threads (e.g., 4)<\/li>\n<\/ul>\n\n\n\n
      \ud83d\udccc Must be run as SYS<\/strong> or a SYSDBA<\/strong> user.<\/p>\n\n\n\n
      Method 4: DBMS_UTILITY.compile_schema<\/code><\/h2>\n\n\n\n
      Recompile all invalid objects within a single schema.<\/p>\n\n\n\n
      -- Only invalid objects\nEXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT', compile_all => false);\n\n-- Recompile everything (valid and invalid)\nEXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT', compile_all => true);<\/code><\/code><\/pre>\n\n\n\n
      Conclusion<\/h2>\n\n\n\n
      Task Type<\/th>Recommended Method<\/th><\/tr><\/thead>
      Few objects<\/td>Manual ALTER<\/code> or DBMS_DDL<\/code><\/td><\/tr>
      One schema<\/td>DBMS_UTILITY<\/code> or UTL_RECOMP<\/code><\/td><\/tr>
      Entire DB<\/td>UTL_RECOMP<\/code> or utlrp.sql<\/code>\/utlprp.sql<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
      Proactively recompiling<\/strong> invalid objects ensures performance, reduces runtime errors, and helps pinpoint issues after structural changes.<\/p>\n","protected":false},"excerpt":{"rendered":"
      Invalid schema objects can cause runtime errors and unexpected behavior\u2014especially after upgrades, patching, or DDL changes. Oracle can recompile them automatically, but it\u2019s often better to handle them proactively. This guide walks you through identifying and recompiling invalid objects using manual methods, Oracle packages, and built-in scripts. Identify Invalid Objects Before recompilation, identify invalid objects […]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,984],"class_list":["post-4186","cposts","type-cposts","status-publish","hentry","category-notes","category-oracle-dba-d2d-tasks"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/4186","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4186"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4186"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}