To check invalid objects in a schema run the following query on SQL*PLUS:
1 | SQL > SELECT * FROM USER_OBJECTS WHERE STATUS = ‘INVALID’; |
And to recompile invalid objects in a schema use DBMS_UTILITY.compile_schema utility:
1 | SQL > EXEC DBMS_UTILITY.compile_schema(schema => ‘SCHEMA_NAME’, compile_all => false); |
where schema_name is the name of the schema where the database objects exist.
We can also use the UTL_RECOMP package to recompile invalid objects at schema and database level.
1 2 3 4 5 6 | ----At schema levelSQL > EXEC UTL_RECOMP.recomp_serial('Mereba');SQL > EXEC UTL_RECOMP.recomp_parallel(4, 'Mereba'); ----Database level.SQL> EXEC UTL_RECOMP.recomp_serial();SQL> EXEC UTL_RECOMP.recomp_parallel(4); |
Amen Answered question 11/08/2022