Shulou(Shulou.com)06/03 Report--

This article mainly explains "how to deal with Oracle database failure objects". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn how to deal with Oracle database failure objects.

Recently, the database was inspected and it was found that there were failure objects under the database business users (non-SYS/Public). Analyze the failure object, including failure view, materialized view, function, package, trigger and so on.

Think about:

It is recommended to deal with the failure object for the following reasons:

1. Through the failed objects, you may be able to deduce and find the business software problems (the business system has too many functions, and there may be insufficient testing).

2. If there are too many invalid objects and the business calls frequently, worry about affecting the performance of the database (without testing, personal thoughts, please correct if there are any mistakes)

Handling method:

1. Search and find failed objects first (executed under sys users)

Select owner, object_name, object_type, status from dba_objects t where status='INVALID' order by t.owner,t.object_type

2. Automatically generate recompile statements for failed objects and recompile them

The following are generated statements for views, functions, materialized views, packages, and triggers.

-- automatically generate view recompilation statements select owner, object_name, object_type, status, 'alter view' | | t.owner | |'. | | object_name | | 'compile' | |;'; 'from dba_objects t where status='INVALID' and t.objectview typecompilation statements VIEW' order by t.ownerMagt. -- automatically generate function recompilation statements select owner, object_name, object_type, status, 'alter FUNCTION' | | t.owner | |'. | | object_name | | 'compile' | |;'; 'from dba_objects t where status='INVALID' and t.objectroomtypecompilation function' order by t.ownerjudt.objectclassitytype.' -- automatically generate visual materialization recompilation statements select owner, object_name, object_type, status, 'alter MATERIALIZED VIEW' | | t.owner | |'. | object_name | | 'compile' | |'; 'from dba_objects t where status='INVALID' and t.object_type='MATERIALIZED VIEW' order by t.ownerjudt.objectclassity.' -- automatically generate package recompilation statements select owner, object_name, object_type, status, 'alter PACKAGE' | | t.owner | |'. | | object_name | | 'compile' | |'; 'from dba_objects t where status='INVALID' and t.object_type='PACKAGE BODY' order by t.ownerpoint t.objectretype | -- automatically generate trigger recompilation statements select owner, object_name, object_type, status, 'alter TRIGGER' | | t.owner | |'. | object_name | | 'compile' | |;'; 'from dba_objects t where status='INVALID' and t.objecttriggtypecompilation statements TRIGGER' order by t.ownerjudt.objecttypetype

After the statement is generated, the copy processing can be executed in batch.

3. Recompiling should solve some of the invalid objects, but there will still be some objects that cannot be solved by recompiling. For this part of the object, the need for manual analysis one by one, the site can be confirmed for confirmation processing (useful to modify, useless to delete), the site can not be confirmed and R & D to confirm, and finally complete the purpose of dealing with the failure object.

If there are still some parts that no one can confirm in the end, it is recommended to keep them for the time being.

Thank you for your reading, the above is the content of "how to deal with Oracle database failure objects". After the study of this article, I believe you have a deeper understanding of how to deal with Oracle database failure objects, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.