Handling of invalid objects in Oracle Database

Time:2021-6-8

Recently, we inspected the database and found that there were invalid objects in the database business users (non sys / public). Failure objects are analyzed, including failure view, materialized view, function, package, trigger and so on.

reflection:

Based on the following reasons, it is suggested to deal with the failed object:

1. Through the failed objects, business software problems may be found (too many business system functions, there may be insufficient testing problems);

2. If there are too many invalid objects and the business is called frequently, you are worried that the performance of the database will be affected (not tested, personal opinion, if there are any errors, please correct them);

Treatment method:

1. Search and find invalid objects first (execute under sys user)

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

2. Recompiling statements are generated automatically for invalid objects

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

--Automatically generate view recompile statement
select owner, object_name, object_type, status  ,'alter view ' || t.owner||'.' || object_name || ' compile'||';'
from dba_objects t  
where status='INVALID' and t.object_type='VIEW'  order by t.owner,t.object_type;
--Automatically generate function recompile statement
select owner, object_name, object_type, status  ,'alter FUNCTION ' || t.owner||'.' || object_name || ' compile'||';'
from dba_objects t  
where status='INVALID' and t.object_type='FUNCTION'  order by t.owner,t.object_type;
--Automatically generate materialized view and recompile statement
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.owner,t.object_type;
--Automatically generate package recompile 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.owner,t.object_type;
--Automatically generate trigger recompile statement
select owner, object_name, object_type, status  ,'alter TRIGGER ' || t.owner||'.' || object_name || ' compile'||';'
from dba_objects t  
where status='INVALID' and t.object_type='TRIGGER'  order by t.owner,t.object_type;

After generating statements, copy processing can be executed in batch.

3. Recompilation should solve some invalid objects, but there are still some objects that cannot be solved by recompilation. For this part of the object, we need to carry out manual analysis one by one. If it can be confirmed on site, it can be confirmed (if it is useful, it can be modified, if it is useless, it can be deleted). If it can not be confirmed on site, it can be confirmed with R & D, and finally the purpose of dealing with the failed object can be completed.

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

Recommended Today

What is “hybrid cloud”?

In this paper, we define the concept of “hybrid cloud”, explain four different cloud deployment models of hybrid cloud, and deeply analyze the industrial trend of hybrid cloud through a series of data and charts. 01 introduction Hybrid cloud is a computing environment that integrates multiple platforms and data centers. Generally speaking, hybrid cloud is […]