![]() ![]() Is executed in Paralell(you set the number of threads to be used)ĮXEC UTL_RECOMP.The UTL_RECOMP package contains two procedures used to recompile invalid objects.ĮXEC UTL_RECOMP. Last method i use is using the UTL_RECOMP package. The same as with the previous script you will need to copy and paste the output in order to recomplie the invalid objects. SELECT 'EXEC DBMS_DDL.alter_compile( '' ' || object_type || ' '', '' ' || owner || ' '', '' ' || object_name || ' '' ) ' FROM dba_objects WHERE status = 'INVALID' and object_type not in ( 'VIEW', 'SYNONYM' ) ORDER BY owner, object_type, object_name views and synonyms will not be recomplied using this method.Just copy and paste the output to recomplie.Īlso another method is using the EXEC DBMS_DDL package to perform the recompilations. The output of this script will be some thing like this: object_name || ' ' || ' compile ' FROM dba_objects d2 WHERE d2. owner != 'PUBLIC' union select 'alter ' || d2. object_name || ' ' || ' compile ' FROM dba_objects d1 WHERE d1. Otherwise compile the invalid objects manually: SQL> alter package compile.this method is good when you have a small number of invalid objects, maybe after a DDL change. Recompile the invalid objects of Grid Repository. We will start by using the dba_objects table to identify our invalid objects and we will build a query on top of those invalid objects so the output will generate the sql syntax to recompile invalid objects. So in this article we will go thru some methods i normally use to recompile invalid objects. Get Invalid Objects of Current User in Oracle To get the list of invalid objects for the current logged in user, run the following SQL query: Select objectname, objecttype, created, lastddltime From userobjects Where status 'VALID' The output would the database objects which are invalid. Among the many resons why objects become invalid are : upgrades, DDL changes, patches.Īlthough invalid objects are recompiled automatically on use, it is useful to run this script prior to operation because this will either eliminate or minimize subsequent latencies due to on-demand automatic recompilation at runtime. How to find and Recompile Invalid Objects in Oracleįrom time to time we will need to recomplie database objects.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |