Recompile Invalid database objects

When we run DDLs against Oracle tables, views and other database objects, some of the dependent objects become invalid.

Here is a script to recompile invalid objects.

The command to recompile is

alter object_type object_name compile;

The only exception is for the package body. The command to recompile the package body is
alter package package_name compile body;

To take that exception into account, here is the script that will general the "alter" for each invalid database object in the current schema.


 select 'alter ' || case when object_type = 'PACKAGE BODY'
                         then 'PACKAGE'
                         else object_type
                    end
 || ' ' || object_name || ' compile ' ||
                  case when object_type = 'PACKAGE BODY'
                        then 'body'
                        else null
                  end || ';' as mysql
 from USER_OBJECTS
 where status = 'INVALID';

Hope, it will save you time.
Thanks
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
If you are interested in the book "Oracle SQL Scipting". here is the link.

 

http://www.amazon.com/dp/B00E2T1DVA

dbms_metadata

Oracle scripting can be used to extract DDL for database objects. The most common use is for getting the DDL for tables.
Here is a simple demo.

SQL> col my_ddl format a100 word_wrap
SQL> set long 50000
SQL> set pagesize 0
SQL>
SQL> select dbms_metadata.get_ddl('TABLE',table_name,user) AS my_ddl
  2  from all_tables where owner = 'SCOTT'
  3  and table_name = 'DEPT';
CREATE TABLE "SCOTT"."DEPT"
(       "DEPTNO" NUMBER(2,0),
"DNAME" CHAR(14),
"LOC" CHAR(13),
CONSTRAINT "DEPT_DEPTNO_PK" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"  ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
The above output needs some revision.
Let's get rid of the storage clause.

SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

PL/SQL procedure successfully completed.

SQL> /
CREATE TABLE "SCOTT"."DEPT"
(       "DEPTNO" NUMBER(2,0),
"DNAME" CHAR(14),
"LOC" CHAR(13),
CONSTRAINT "DEPT_DEPTNO_PK" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS"  ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS"

We also want to get rid of segment attributes, put a semicolon at the end as sql terminator and make the output pretty!
SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);

PL/SQL procedure successfully completed.

SQL> /
CREATE TABLE "SCOTT"."DEPT"
(       "DEPTNO" NUMBER(2,0),
"DNAME" CHAR(14),
"LOC" CHAR(13),
CONSTRAINT "DEPT_DEPTNO_PK" PRIMARY KEY ("DEPTNO") ENABLE
 ) ;
 
Now, the output looks little better!

If we want to get the DDL for all the tables in the schema, let's remove the table_name filter.
 
SQL> select dbms_metadata.get_ddl('TABLE',table_name,user) AS my_ddl
  2  from all_tables where owner = 'SCOTT';
CREATE TABLE "SCOTT"."EMP"
(       "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
"ENAME" CHAR(10),
"JOB" CHAR(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
"PROJNO" NUMBER,
"LOADSEQ" NUMBER,
CONSTRAINT "EMP_DEPTNO_FK" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) ;

CREATE TABLE "SCOTT"."SALGRADE"
(       "GRADE" NUMBER,
"LOSAL" NUMBER,
"HISAL" NUMBER
   ) ;

CREATE TABLE "SCOTT"."DEPT"
(       "DEPTNO" NUMBER(2,0),
"DNAME" CHAR(14),
"LOC" CHAR(13),
CONSTRAINT "DEPT_DEPTNO_PK" PRIMARY KEY ("DEPTNO") ENABLE
 ) ;

Looks good? Well, the only catch is, the table creation script may not be in the right order. In the above case, we want the dept table to be created first and then emp table. Otherwise, we are going to get error.
We will try to figure out how to achieve that in the next post!
Meanwhile, if you have a better idea, please weigh in!

==========================================================
If you are interested in the book "Oracle SQL Scipting". here is the link.
http://www.amazon.com/Oracle-SQL-Scripting-ebook/dp/B00E2T1DVA/ref=sr_1_1?ie=UTF8&qid=1374436123&sr=8-1&keywords=oracle+sql+scripting