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,
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 0
select
'alter '
|| decode(object_type,
'PACKAGE BODY','PACKAGE',
'TYPE BODY','TYPE',
object_type
) || ' '
|| owner || '.' || '"' || object_name || '"' || ' compile '
|| decode(object_type,
'PACKAGE BODY','body;',
'TYPE BODY','body;',
';'
)
,'show error '
|| object_type || ' '
|| owner || '.'
|| object_name
from dba_objects
where object_type in ('FUNCTION','PACKAGE','PROCEDURE','VIEW','TRIGGER','PACKAGE BODY','TYPE','TYPE BODY','INDEXTYPE')
and status = 'INVALID'
--and owner not in ('SYS','SYSTEM')
order by owner,
decode(object_type,
'VIEW', 1,
'PACKAGE', 2,
'PACKAGE BODY', 3,
'PROCEDURE', 4,
'FUNCTION', 5,
'TRIGGER', 6,
9
)
/
===
| 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;
====
spool more_info.txt
set echo on;
connect / as sysdba
@?/rdbms/admin/utlrp.sql
set pagesize 1000 linesize 200
col host_name format a30
col comp_name format a36
col version format a14
col status format a10
col action_time format a30
col action format a20
col comments format a45
col owner format a12
col object_name format a35
col object_type format a20
col name format a35
col table_name format a35
col column_name format a25
col index_name format a25
col grantee format a12
col grantor format a12
col privilege format a20
col schema_url format a60
col user format a12
set echo on;
connect / as sysdba
@?/rdbms/admin/utlrp.sql
set pagesize 1000 linesize 200
col host_name format a30
col comp_name format a36
col version format a14
col status format a10
col action_time format a30
col action format a20
col comments format a45
col owner format a12
col object_name format a35
col object_type format a20
col name format a35
col table_name format a35
col column_name format a25
col index_name format a25
col grantee format a12
col grantor format a12
col privilege format a20
col schema_url format a60
col user format a12
col TEXT for a75
-- Instance and platform
select sysdate from dual;
select instance_name, host_name, version from v$instance;
select platform_name from v$database;
show con_name
-- Status of database components
select comp_name, version, status
from dba_registry
order by status, comp_name;
-- Registry history
select action_time, action, version, comments
from registry$history
order by 1 desc;
-- Invalid objects
select owner, object_name, object_type, status
from dba_objects
where status = 'INVALID'
and owner in ('SYS', 'XDB','MDSYS','ORDSYS','CTXSYS','PUBLIC','CTXSYS')
order by owner, object_name;
-- Object errors
select owner, name, type, text
from dba_errors
where owner in ('SYS', 'XDB','MDSYS','ORDSYS','PUBLIC','CTXSYS')
order by owner, name;
-- Duplicate objects
select owner, object_name, object_type, status
from dba_objects
where object_name in
( select object_name
from dba_objects
where owner = 'XDB' )
and owner not in ('SYS', 'XDB','MDSYS','ORDSYS','PUBLIC','CTXSYS');
-- Privileges
select owner, table_name, grantee, privilege
from dba_tab_privs
where table_name in
( 'DBMS_JOB', 'DBMS_LOB', 'DBMS_SQL',
' DBMS_STATS', 'DBMS_RANDOM', 'UTL_FILE',
'UTL_HTTP', 'UTL_RAW', 'UTL_TCP' )
and grantee = 'PUBLIC'
order by table_name;
select grantee, privilege, table_name, grantor
from dba_tab_privs
where grantor = 'SYS' and grantee = 'XDB'
order by table_name;
-- XMLType tables
select owner, storage_type, count(*) "TOTAL"
from dba_xml_tables
group by owner, storage_type
order by owner, storage_type;
select owner, table_name, storage_type
from dba_xml_tables
where storage_type in ('OBJECT-RELATIONAL', 'BINARY')
and owner != 'XDB'
order by storage_type, table_name;
-- XMLType columns
select owner, storage_type, count(*) "TOTAL"
from dba_xml_tab_cols
group by owner, storage_type
order by owner, storage_type;
select owner, table_name, column_name
from dba_xml_tab_cols
where storage_type = 'BINARY'
and owner not in ('MDSYS', 'ORDDATA', 'SYS', 'XDB')
order by owner, table_name;
-- XDB Indexes
select index_name, status, domidx_status, domidx_opstatus
from dba_indexes
where index_name in ('XDBHI_IDX', 'XDB$RESOURCE_OID_INDEX');
-- XML indexes
select index_owner, index_name, parameters, table_owner, table_name, type, index_type
from dba_xml_indexes;
-- Registered schemas
select owner, count(*) "TOTAL"
from dba_xml_schemas
group by owner;
select owner, schema_url
from dba_xml_schemas
order by 1,2;
-- Resources in the XML repository
select distinct (a.username) "USER", count (r.xmldata) "TOTAL"
from dba_users a, xdb.xdb$resource r
where sys_op_rawtonum (extractvalue (value(r),'/Resource/OwnerID/text()')) = a.USER_ID
group by a.username;
select any_path from resource_view;
-- Any XDB object outside XDB?
SELECT OBJECT_NAME,OBJECT_TYPE,OWNER FROM DBA_OBJECTS WHERE OBJECT_NAME in
(Select OBJECT_NAME from DBA_OBJECTS where owner='XDB') and owner not in ('XDB','PUBLIC');
-- Is Spatial being used?
select owner, index_name
from dba_indexes
where ityp_name = 'SPATIAL_INDEX';
select owner, table_name, column_name
from dba_tab_columns
where data_type= 'SDO_GEOMETRY'
and owner != 'MDSYS';
-- Is Multimedia being used?
set serveroutput on;
@?/ord/im/admin/imremchk.sql
spool off
-- Instance and platform
select sysdate from dual;
select instance_name, host_name, version from v$instance;
select platform_name from v$database;
show con_name
-- Status of database components
select comp_name, version, status
from dba_registry
order by status, comp_name;
-- Registry history
select action_time, action, version, comments
from registry$history
order by 1 desc;
-- Invalid objects
select owner, object_name, object_type, status
from dba_objects
where status = 'INVALID'
and owner in ('SYS', 'XDB','MDSYS','ORDSYS','CTXSYS','PUBLIC','CTXSYS')
order by owner, object_name;
-- Object errors
select owner, name, type, text
from dba_errors
where owner in ('SYS', 'XDB','MDSYS','ORDSYS','PUBLIC','CTXSYS')
order by owner, name;
-- Duplicate objects
select owner, object_name, object_type, status
from dba_objects
where object_name in
( select object_name
from dba_objects
where owner = 'XDB' )
and owner not in ('SYS', 'XDB','MDSYS','ORDSYS','PUBLIC','CTXSYS');
-- Privileges
select owner, table_name, grantee, privilege
from dba_tab_privs
where table_name in
( 'DBMS_JOB', 'DBMS_LOB', 'DBMS_SQL',
' DBMS_STATS', 'DBMS_RANDOM', 'UTL_FILE',
'UTL_HTTP', 'UTL_RAW', 'UTL_TCP' )
and grantee = 'PUBLIC'
order by table_name;
select grantee, privilege, table_name, grantor
from dba_tab_privs
where grantor = 'SYS' and grantee = 'XDB'
order by table_name;
-- XMLType tables
select owner, storage_type, count(*) "TOTAL"
from dba_xml_tables
group by owner, storage_type
order by owner, storage_type;
select owner, table_name, storage_type
from dba_xml_tables
where storage_type in ('OBJECT-RELATIONAL', 'BINARY')
and owner != 'XDB'
order by storage_type, table_name;
-- XMLType columns
select owner, storage_type, count(*) "TOTAL"
from dba_xml_tab_cols
group by owner, storage_type
order by owner, storage_type;
select owner, table_name, column_name
from dba_xml_tab_cols
where storage_type = 'BINARY'
and owner not in ('MDSYS', 'ORDDATA', 'SYS', 'XDB')
order by owner, table_name;
-- XDB Indexes
select index_name, status, domidx_status, domidx_opstatus
from dba_indexes
where index_name in ('XDBHI_IDX', 'XDB$RESOURCE_OID_INDEX');
-- XML indexes
select index_owner, index_name, parameters, table_owner, table_name, type, index_type
from dba_xml_indexes;
-- Registered schemas
select owner, count(*) "TOTAL"
from dba_xml_schemas
group by owner;
select owner, schema_url
from dba_xml_schemas
order by 1,2;
-- Resources in the XML repository
select distinct (a.username) "USER", count (r.xmldata) "TOTAL"
from dba_users a, xdb.xdb$resource r
where sys_op_rawtonum (extractvalue (value(r),'/Resource/OwnerID/text()')) = a.USER_ID
group by a.username;
select any_path from resource_view;
-- Any XDB object outside XDB?
SELECT OBJECT_NAME,OBJECT_TYPE,OWNER FROM DBA_OBJECTS WHERE OBJECT_NAME in
(Select OBJECT_NAME from DBA_OBJECTS where owner='XDB') and owner not in ('XDB','PUBLIC');
-- Is Spatial being used?
select owner, index_name
from dba_indexes
where ityp_name = 'SPATIAL_INDEX';
select owner, table_name, column_name
from dba_tab_columns
where data_type= 'SDO_GEOMETRY'
and owner != 'MDSYS';
-- Is Multimedia being used?
set serveroutput on;
@?/ord/im/admin/imremchk.sql
spool off
==============================
export PATH=$PATH:$ORACLE_HOME/bin cd $ORACLE_HOME/rdbms/admin $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -b utlrp -d $ORACLE_HOME/rdbms/admin utlrp.sql
