Tag : sql

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_ ..

Read more

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 ..

Read more