sql developer, function and procedures gets out of compiling-CodePudding

I work in a company where there are many processes that are executed on an ORACLE server, with lots of functions and procedures that are constantly running different data (all types of data)

Yesterday, we noticed a slowdown in the system, and even a partial halt in traffic on the DB side. After testing our DBA man using SQL DEVELOPER, he founds about 15 functions and procedures that came out of compilation. That is, no changes were made to them, but the icons of all of those 15 functions/procedures showed that they were not compiled, it was necessary to click on 'Recompile' to use them again, thus restoring the system to normal operation.

Up to this moment, we do not know how to explain what caused 15 functions and a procedure to go out of compilation at the same time, but certainly no employee has touched on these functions / procedures. First time such a thing happens to us, and we have not heard of a similar case.

Does anyone have any idea what could have made this happen? Maybe a problematic type of information got into DB that caused a problematic chain reaction?

CodePudding user response：

Usually this occurs when someone changes a dependency (for example: TYPE, TABLE, FUNCTION, PROCEDURE, PACKAGE, etc.) of the function/procedure.

For example:

CREATE TYPE obj IS OBJECT (a INT, b INT);

CREATE PROCEDURE proc(v_obj IN obj)
IS BEGIN NULL; END;
/

Then both are valid. If you then do:

CREATE OR REPLACE TYPE obj IS OBJECT (a NUMBER(5,0), b NUMBER(5,0));

Then SELECT object_name, object_type, status FROM USER_OBJECTS; outputs:

OBJECT_NAME OBJECT_TYPE STATUS

OBJ TYPE VALID

PROC PROCEDURE INVALID

OBJECT_NAME	OBJECT_TYPE	STATUS
OBJ	TYPE	VALID
PROC	PROCEDURE	INVALID

If you then do:

ALTER PROCEDURE proc COMPILE;

Then the procedure is valid again.

db<>fiddle here

CodePudding user response：

Maybe some other object - that is used by all those functions and procedures - was modified (e.g. package specification), and it "invalidated" all objects that reference it.

Basically, you don't have to do anything - Oracle will automatically recompile them as soon as someone calls them.

Here's an example.

Package with a single function:

SQL> create or replace package pkg_test as
  2    function f_test (par_number in number) return number;
  3  end;
  4  /

Package created.

SQL> create or replace package body pkg_test as
  2    function f_test (par_number in number) return number
  3    is
  4    begin
  5      return par_number;
  6    end;
  7  end;
  8  /

Package body created.

Standalone function that references (calls) function from the package;

SQL> create or replace function f_test_2 (par_number in number)
  2    return number
  3  is
  4  begin
  5    return pkg_test.f_test(par_number);
  6  end;
  7  /

Function created.

What is its status? It is VALID:

SQL> select object_name, object_type, status from user_objects where object_name = 'F_TEST_2';

OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
F_TEST_2             FUNCTION            VALID

Does it work? YES:

SQL> select f_test_2 (100) result from dual;

    RESULT
----------
       100

SQL>

OK, let's now slightly modify the packaged function's spec (I'll add the DEFAULT clause):

SQL> create or replace package pkg_test as
  2    function f_test (par_number in number default 0) return number;
  3  end;
  4  /

Package created.

SQL> create or replace package body pkg_test as
  2    function f_test (par_number in number default 0) return number
  3    is
  4    begin
  5      return par_number;
  6    end;
  7  end;
  8  /

Package body created.

I didn't modify standalone function f_test_2. What is its status? It is now INVALID:

SQL> select object_name, object_type, status from user_objects where object_name = 'F_TEST_2';

OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
F_TEST_2             FUNCTION            INVALID           --> see? INVALID

Can I use it (without recompiling it)? Yes, I can:

SQL> select f_test_2 (500) result from dual;

    RESULT
----------
       500

SQL>