Recompiling objects in oracle

A pl/sql script to recompile all invalid objects in oracle. Simple but powerfull.

DECLARE

   obj_name_    User_Objects.object_name%TYPE;
   obj_type_    User_Objects.object_type%TYPE;
   str_run_     VARCHAR2(200);
   cid_         INTEGER;
   ret_         INTEGER;
   CURSOR Invalid_Objects_ IS
      SELECT object_name, object_type
      FROM user_objects
      WHERE status = 'INVALID'
      ORDER BY object_type ASC;

BEGIN 
   FOR Get_Rec_ IN Invalid_Objects_ LOOP
      BEGIN
         obj_name_ := Get_Rec_.object_name;
         obj_type_ := Get_Rec_.object_type;
         IF (obj_type_ = 'FUNCTION') THEN
            str_run_ := 'ALTER FUNCTION ' || obj_name_ || ' COMPILE';
         ELSIF (obj_type_ = 'JAVA SOURCE') THEN
            str_run_ := 'ALTER JAVA SOURCE "' || obj_name_ || '"
COMPILE';
         ELSIF (obj_type_ = 'JAVA CLASS') THEN
            str_run_ := 'ALTER JAVA CLASS "' || obj_name_ || '"
RESOLVE';
         ELSIF (obj_type_ = 'PACKAGE') THEN
            str_run_ := 'ALTER PACKAGE ' || obj_name_ || ' COMPILE';
         ELSIF (obj_type_ = 'PACKAGE BODY') THEN
            str_run_ := 'ALTER PACKAGE ' || obj_name_ || ' COMPILE
BODY';
         ELSIF (obj_type_ = 'PROCEDURE') THEN
            str_run_ := 'ALTER PROCEDURE ' || obj_name_ || ' COMPILE';
         ELSIF (obj_type_ = 'TRIGGER') THEN
            str_run_ := 'ALTER TRIGGER ' || obj_name_ || ' COMPILE';

         ELSIF (obj_type_ = 'VIEW') THEN
            str_run_ := 'ALTER VIEW ' || obj_name_ || ' COMPILE';
         ELSIF (obj_type_ = 'MATERIALIZED VIEW') THEN
            str_run_ := 'ALTER MATERIALIZED VIEW ' || obj_name_ || '
COMPILE';
         ELSIF (obj_type_ = 'DIMENSION') THEN
            str_run_ := 'ALTER DIMENSION ' || obj_name_ || ' COMPILE';
         ELSIF (obj_type_ = 'TYPE') THEN
            str_run_ := 'ALTER TYPE ' || obj_name_ || ' COMPILE';
         END IF;
         --
         cid_ := DBMS_SQL.OPEN_CURSOR;
         DBMS_SQL.PARSE(cid_, str_run_, DBMS_SQL.NATIVE);
         ret_ := DBMS_SQL.EXECUTE(cid_);
         DBMS_SQL.CLOSE_CURSOR(cid_);
      EXCEPTION
         WHEN OTHERS THEN
            BEGIN
               IF (DBMS_SQL.IS_OPEN(cid_)) THEN
                  DBMS_SQL.CLOSE_CURSOR(cid_);
               END IF;
            END;
      END;

   END LOOP; 
END; 

Source:
http://www.orafaq.com/usenet/comp.databases.oracle.misc/2005/02/15/0382.htmy

Oracle reverse engineering

To reverse engineere the oracle database, you can use the Oracle data dictionary like follows.

-- Get table information from oracle DB
select T.* from USER_TABLES T;

-- Get column information from oracle DB
select C.* from USER_TAB_COLUMNS C;

-- Get constraint information from oracle DB
SELECT cons.constraint_type, cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cons.constraint_name = cols.constraint_name
ORDER BY cols.table_name, cols.position;

-- oracle constraint types
--C (check constraint on a table)
--P (primary key)
--U (unique key)
--R (referential integrity)
--V (with check option, on a view)
--O (with read only, on a view)

-- Get all referential constraints from oracle DB including referencing table
select UCC.TABLE_NAME, UCC.CONSTRAINT_NAME, UCC.COLUMN_NAME, UC.CONSTRAINT_TYPE, UC.SEARCH_CONDITION, UC2.TABLE_NAME as REFERENCES_TABLE
from USER_CONS_COLUMNS UCC, USER_CONSTRAINTS UC, USER_CONSTRAINTS UC2
where UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
and UC.R_CONSTRAINT_NAME = UC2.CONSTRAINT_NAME(+) and UC.CONSTRAINT_TYPE = 'R'
order by UCC.CONSTRAINT_NAME;

-- Get ddl of a table from oracle by name
SELECT DBMS_METADATA.GET_DDL('TABLE','MY_TABLE_NAME') FROM DUAL;

-- Get all referential constraints from oracle DB including referencing table and column     
SELECT CONS.CONSTRAINT_NAME, CONS.TABLE_NAME, COLS.COLUMN_NAME,
    CONS.R_CONSTRAINT_NAME, CONS_R.TABLE_NAME R_TABLE_NAME, COLS_R.COLUMN_NAME R_COLUMN_NAME
FROM USER_CONSTRAINTS CONS
    LEFT JOIN USER_CONS_COLUMNS COLS ON COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
    LEFT JOIN USER_CONSTRAINTS CONS_R ON CONS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME
    LEFT JOIN USER_CONS_COLUMNS COLS_R ON COLS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME
-- returns only foreign key constraints
WHERE CONS.CONSTRAINT_TYPE = 'R' -- AND CONS.TABLE_NAME = 'MY_TABLE_NAME'
ORDER BY CONS.TABLE_NAME, COLS.COLUMN_NAME;