Oracle Invalid objects ....
object info
define OBJECT_NAME='xxxxxxx'
set linesize 300
col OWNER for a15
col OBJECT_NAME for a25
select * from (select 1 con_id,OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where OBJECT_NAME='&OBJECT_NAME'
union
select con_id, OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from cdb_objects where OBJECT_NAME='&&OBJECT_NAME'
)
;
======
@$ORACLE_HOME/rdbms/admin/utlrp.sql
utlprp.sql =
DECLARE
threads pls_integer := &&1;
BEGIN
utl_recomp.recomp_parallel(threads);
END;
/
declare
threads pls_iteger :=&&1;
begin
utl_recomp.recomp_parallel(threds, flags =>utl_recomp.oracle_maintained);
end;
or
define threds=4
exec utl_recomp.recomp_parallel(&threds, flags =>utl_recomp.oracle_maintained); --- <<<<< Only for Oracle manage objects
======================
set recsep off
column invalid_object format A30
column likely_reason format A35 word_wrapped
break on type on invalid_object
select owner || '.' || object_name invalid_object,'--- ' || object_type || ' ---' likely_reason from dba_objects
where status = 'INVALID'
union
select d.owner || '.' || d.name,'Non-existent referenced db link ' || d.referenced_link_name from dba_dependencies d
where not exists (select 'x'from dba_db_links
where owner in ('PUBLIC', d.owner)
and db_link = d.referenced_link_name
)
and d.referenced_link_name is not null
and (d.owner, d.name, d.type) in (select owner, object_name, object_type from dba_objects where status = 'INVALID')
union
select d.owner || '.' || d.name,'Depends on invalid ' || d.referenced_type || ' '|| d.referenced_owner || '.' || d.referenced_name from dba_objects ro,dba_dependencies d
where ro.status = 'INVALID'
and ro.owner = d.referenced_owner
and ro.object_name = d.referenced_name
and ro.object_type = d.referenced_type
and d.referenced_link_name is null
and (d.owner, d.name, d.type) in (select owner, object_name, object_type from dba_objects where status = 'INVALID')
union
select d.owner || '.' || d.name,'Depends on newer ' || d.referenced_type || ' '|| d.referenced_owner || '.' || d.referenced_name from dba_objects ro,dba_dependencies d,dba_objects o
where nvl(ro.last_ddl_time, ro.created) > nvl(o.last_ddl_time, o.created)
and ro.owner = d.referenced_owner
and ro.object_name = d.referenced_name
and ro.object_type = d.referenced_type
and d.referenced_link_name is null
and d.owner = o.owner
and d.name = o.object_name
and d.type = o.object_type
and o.status = 'INVALID'
union
select d.owner || '.' || d.name,'Depends on ' || d.referenced_type || ' '|| d.referenced_owner || '.' || d.referenced_name|| decode(d.referenced_link_name, NULL, '','@' || d.referenced_link_name) from dba_dependencies d
where d.referenced_owner != 'PUBLIC' -- Public synonyms generate noise
and d.referenced_type = 'NON-EXISTENT'
and (d.owner, d.name, d.type) in (select owner, object_name, object_type from dba_objects where status = 'INVALID')
union
select d.owner || '.' || d.name invalid_object,'No privilege on referenced ' || d.referenced_type || ' '|| d.referenced_owner || '.' || d.referenced_name from dba_objects ro,dba_dependencies d
where not exists (select 'x' from dba_tab_privs p
where p.owner = d.referenced_owner
and p.table_name = d.referenced_name
and p.grantee in ('PUBLIC', d.owner)
)
and ro.status = 'VALID'
and ro.owner = d.referenced_owner
and ro.object_name = d.referenced_name
and d.referenced_link_name is not null
and (d.owner, d.name, d.type) in (select owner, object_name, object_type from dba_objects where status = 'INVALID')
union
select o.owner || '.' || o.object_name,e.text from dba_errors e,dba_objects o
where e.text like 'PLS-%'
and e.owner = o.owner
and e.name = o.object_name
and e.type = o.object_type
and o.status = 'INVALID';
===============
Components valid / invalid
set lines 90
col version for a12
col comp_id for a8
col comp_name for a35
col status for a12
col schema like version
select comp_id,schema,status,version,comp_name from dba_registry
order by 1;
============
set lines 120
col status for a9
col object_type for a20;
col owner.object for a50
select status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
from dba_objects
where 0=0
and status != 'VALID'
and object_name not like 'BIN$%'
order by 4,2;
=====
set serveroutput on
BEGIN
FOR r
IN ( SELECT 'alter '
|| REPLACE (object_type, 'BODY', '')
|| ' '
|| owner
|| '.'
|| object_name
|| ' compile '
|| DECODE (object_type, 'PACKAGE BODY', 'BODY')
stmt
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner,
DECODE (object_type,
'SYNONYM', 1,
'VIEW', 2,
'FUNCTION', 3,
'PROCEDURE', 4,
'PACKAGE', 7,
'PACKAGE BODY', 8,
9))
LOOP
BEGIN
-- EXECUTE IMMEDIATE (r.stmt);
dbms_output.put_line(r.stmt||' ;');
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END LOOP;
END;
/
===
SET PAGESIZE 9999 | |
clear columns | |
clear breaks | |
clear computes | |
column owner format a25 heading 'Owner' | |
column object_name format a30 heading 'Object Name' | |
column object_type format a20 heading 'Object Type' | |
column count format 999,999 heading 'Count' | |
break on owner skip 2 on report | |
compute sum label "Count: " of count on owner | |
compute sum label "Grand Total: " of count on report | |
SELECT | |
owner | |
, object_type | |
, count(*) Count | |
FROM dba_objects | |
WHERE status <> 'VALID' | |
GROUP BY owner, object_type | |
/ |
=====
set pagesize 1000 LINESIZE 300
col COMP_ID format A9
col COMP_NAME format A40
col STATUS format A15
col VERSION format A12
col owner format a30
col object_name format a30
col object_type format a30
col COMMENTS for a60
col ACTION_TIME for a30
col VERSION for a25
--SPOOL DB_DD_CHECK.TXT
prompt /* Database identification */
select NAME, PLATFORM_ID, DATABASE_ROLE from v$database;
select * from V$version where banner like 'Oracle Database%';
select INSTANCE_NAME, to_char(STARTUP_TIME,'DD/MM/YYYY HH24:MI:SS') "STARTUP_TIME" from v$instance;
prompt /* Database Component Registry status */
select comp_id, comp_name, status, version from dba_registry;
prompt /* Level of patch in the database */
select * from dba_registry_history;
prompt /* INVALID objects count, by type -in detail */
select count(*) "INVALID Objects Count" from dba_objects where status !='VALID';
select owner, object_type, count(*) from dba_objects where status !='VALID' group by owner, object_type order by owner, object_type;
select owner, object_type, object_name, status from dba_objects where status !='VALID' order by owner, object_type, object_name;