Compile INVALID objects

There are few different ways to compile invalid objects in a database, different people use different ways to compile (like utlrp.sql, alter command,etc)

This is the method i used recently in my environment.

 SQL> select banner from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE    11.2.0.2.0      Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production

Check current invalid object counts:

SQL> select object_type,count(*) from dba_objects where status='INVALID' group by object_type;

SQL >  Spool run_invalid.sql

SQL> select 'ALTER ' || OBJECT_TYPE ||' '||owner||'.'||OBJECT_NAME || ' COMPILE;' from dba_objects where owner='SYSFM' and status = 'INVALID' ;

SQL>  Spool off;

SQL>  @run_invalid

Check again counts:

SQL> select object_type,count(*) from dba_objects where owner='MYUSER' and status='INVALID' group by object_type;

Script to move objects MAXEXTENTS size to UNLIMITED

 sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 24 18:50:58 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning and Automatic Storage Management options

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> spool runme.sql

select  'alter '||   object_type||' '||   object_name||   ' storage (maxextents unlimited);' from  dba_objects  where  object_type in ('TABLE','INDEX') and owner = 'OWNER_NAME';

SQL> spool off

SQL> @runme

Verify:

SQL> select max_extents,count(*) from dba_indexes where owner='SYSFM' group by max_extents;
SQL> select max_extents,count(*) from dba_tables where owner='SYSFM' group by max_extents;