Analysis and solution of failure causes of stored procedures or functions in Oracle production

The cause analysis and solution of stored procedure or function failure in Oracle production:

Error message:

Cause Analysis:

1. When we compile a stored procedure or function, all Oracle objects referenced by the procedure or function are recorded in the data dictionary.

The process relies on these stored objects. We can see that a compiled error subroutine is displayed in the data dictionary that is marked as illegal.

Similarly, if a DDL operation is running on the object on which it is related, the stored subroutine will also be illegal. When an object changes, its associated object becomes an illegal object.

If all of the objects are in the same database, then the related objects will go into an illegal state while the underlying object changes. Because data dictionaries are constantly tracking the correlation between objects, this change can be quickly reflected.

2. Why does the process under remote invocation look different?

The answer lies in the fact that the data dictionary does not track remote related objects. In fact, because the remote object may be in a different database, it is virtually impossible to invalidate all the related remote objects (the data dictionary may not be accessible if the remote object is in an invalid period). On the other, the legitimacy of the remote object is checked at run time.

Error scenario

1. The object referenced by the procedure fails, for example: Table structure change

2. Dblink problems during Operation

To view the status of a stored procedure:

# Select T1.owner,t1.object_name,t1.object_type,t1.status,t1.created,t1.last_ddl_time from all_objects t1 where T1.owner = ' Xiaogaokui ' and t1.object_type = ' PROCEDURE ' and t1.status = ' INVALID '

Viewing the objects referenced by a procedure

# Select T2.owner,t2.name,t2.type,t2.referenced_owner,t2.referenced_name from all_dependencies t2 where T2.owner = ' Xiaogaokui ' ORDER by 2;

To view error messages during compilation

#select * from All_errors;

Compiling the invalid process

Way One:

In Oracle Sqlplus

# Spool Compile_invalid_porc.sql--record RECOMPILE statement

# select ' ALTER PROCEDURE ' | | T1.object_name | | ' COMPILE; ' from all_objects t1 where t1.status = ' INVALID ' and t1.object_type = ' PROCEDURE ' and T1.owner = ' Xiaogaokui '

# Spool Off

# @compile_invalid_porc. sql

Way two:

Create or replace procedure Compite_invalid_procedures (

P_owner VARCHAR2--owner name, schema

)

As

--Compile user under invalid process

V_sql_statement VARCHAR2 (2000);

Begin

For Invalid_proc in (select T1.object_name as object_name from all_objects t1 where T1.owner = Upper (P_owner) and T1.OBJEC T_type = ' PROCEDURE ' and t1.status = ' invalied ')

Loop

V_sql_statement: = ' ALTER PROCEDURE ' | | Invalid_proc.object_name | | ' COMPILE ';

Begin

Execute Immediate v_sql_statement

exception

When others then

Dbms_output.put_line (Sqlcode | | sqlerrm);

End

End Loop;

End

/

Solutions in production:

Before calling a stored procedure, increase the compilation statement for the procedure:

Method One:

# EXECUTE IMMEDIATE ' ALTER PROCEDURE compile_invalid_procedures COMPILE ';

Method Two:

Create a stored procedure, execute it when needed, or create a timed task exec Dbms_job.submit (: job_id, ' timer_auto_recompile_objs; ', sysdate, ' sysdate+1/24 '); Timed execution.

Create or replace procedure Timer_auto_recompile_objs

As cursor objects_list is select Object_name,object_type from user_objects where status= ' INVALID ';

Begin for V_object in Objects_list loop

If v_object.object_type= ' PROCEDURE '

Then execute immediate ' alter PROCEDURE ' | | v_object.object_name| | ' Compile ';

ElseIf v_object.object_type= ' FUNCTION '

Then execute immediate ' alter function ' | | v_object.object_name| | ' Compile ';

elsif v_object.object_type= ' VIEW '

Then execute immediate ' alter View ' | | v_object.object_name| | ' Compile ';

elsif v_object.object_type= ' materialized VIEW '

Then execute immediate ' alter materialized view ' | | v_object.object_name| | ' Compile ';

End If;

End Loop;

End

Appendix:

Compile process:

Alter procedure New_procedure compile;

To be able to execute this command, you need to have this procedure, or have ALTER any procedure system permissions.

compiler function:

alter function new_function compile;

To be able to execute, you need to have this function, or have ALTER any procedure system permissions.

To compile the package:

Alter package [user.] Package_name compile [package|body];

In order to be able to execute, you need to own this package, or have ALTER any procedure system permissions.

Replace:

You can use the respective create or replace commands to replace procedures, functions, and packages.

Use the OR Replace clause to preserve the permissions that these objects have been given.

Delete:

Delete process: drop procedure new_procedure;

Delete functions: Drop function New_functioin;

Delete Packages: Drop package new_package;

Delete Package Body: drop packages body new_package;

Analysis and solution of failure causes of stored procedures or functions in Oracle production