OracleObjects & schema
Recompile invalid objects
Recompile one schema, or everything in the database with Oracle's utlrp script. Run the invalid objects check again afterwards.
Not yet verified. How scripts are tested
1-- One schema2EXEC UTL_RECOMP.RECOMP_SERIAL('APP');3 4-- Or the whole database (as SYSDBA)5-- @?/rdbms/admin/utlrp.sqlSave it as ora-recompile.sql and run it with SQL> @ora-recompile.
More Oracle scripts: Objects & schema
- Generate DDL for a table and an indexUses DBMS_METADATA to write the CREATE statements for a table and one of its indexes into ddl_list.sql in your current directory. Prompts for the…
- Invalid objectsEvery invalid object by owner and type, typically left behind by a deployment or patch. The recompile script fixes most of them.
- Unusable indexes and index partitionsIndexes marked UNUSABLE, typically after a partition operation or a direct-path load. Queries that need them will either fail or fall back to full…
- Foreign keys that are probably not indexedForeign keys whose columns aren't the leading columns of an index. Unindexed foreign keys cause table-level locks when the parent row is updated or…
- Disabled constraints and triggersConstraints and triggers someone switched off, often for a data load, and never switched back on.
- Objects changed in the last 24 hoursApplication objects with DDL in the last day. Grants also update LAST_DDL_TIME, so not every row is a code change.