Check function/procedure status : user_objects « System Tables Data Dictionary « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. System Tables Data Dictionary
3. user_objects

SQL>
SQL> create or replace function getArea (i_rad NUMBER)
  2  return NUMBER
  3  is
  4  begin
  5    return 3.14*(i_rad**2);
  6  end;
  7  /

Function created.

SQL>
SQL>
SQL>
SQL> alter function getArea compile;

Function altered.

SQL>
SQL> select status
  2  from user_objects
  3  where object_name = 'GETAREA';
 /

STATUS
----------
VALID