Oracle数据库无效对象问题处理

   在新上线的网管系统中，发现维护的数据库存在大量的无效对象，其中大量的无效对象（800多个）所有者为系统用户，虽然对应用暂时没有影响，但为了避免隐患，对无效对象进行了处理。

$select object_name,object_type,owner,created,last_ddl_time,timestamp from dba_objects where status='INVALID' order by 1,2;

 

OBJECT_NAME	OBJECT_TYPE	OWNER
/c0c184e9_QsmaSqlValidationTar	JAVA CLASS	SYS
/da5b87e_QsmaSqlTestCallback	JAVA CLASS	SYS
/ec947e38_QsmaSqlParserJJCalls	JAVA CLASS	SYS
_ALL_REPCOLUMN_GROUP	VIEW	SYS
_ALL_REPCONFLICT	VIEW	SYS
_ALL_REPGROUPED_COLUMN	VIEW	SYS
_ALL_REPPARAMETER_COLUMN	VIEW	SYS
_ALL_REPRESOLUTION	VIEW	SYS
DBA_LOCK_INTERNAL	VIEW	SYS
DBMS_REPCAT_AUTH	PACKAGE BODY	SYS
DBMS_REPCAT_RGT_CUST	PACKAGE BODY	SYS
DBMS_REPCAT_UTL4	PACKAGE BODY	SYS
DBMSOBJG_DP	PACKAGE BODY	SYS
DBURITYPE	TYPE	SYS
DBURITYPE	TYPE BODY	SYS
FTPURITYPE	TYPE	SYS
FTPURITYPE	TYPE BODY	SYS
HTTPURITYPE	TYPE	SYS
HTTPURITYPE	TYPE BODY	SYS
LTRIC	PACKAGE BODY	SYS
LTUTIL	PACKAGE BODY	SYS
oracle/qsma/QsmaDataManager	JAVA CLASS	SYS
oracle/qsma/QsmaDataReports	JAVA CLASS	SYS
oracle/qsma/QsmaSql	JAVA CLASS	SYS
oracle/qsma/QsmaSqlParser	JAVA CLASS	SYS
oracle/qsma/QsmaSqlTest	JAVA CLASS	SYS
oracle/qsma/QsmaWorkload	JAVA CLASS	SYS
oracle/qsma/QsmaWorkloadFilter	JAVA CLASS	SYS
OWM_DDL_PKG	PACKAGE BODY	SYS
OWM_REPUTIL	PACKAGE BODY	SYS
URIFACTORY	PACKAGE BODY	SYS
URITYPE	TYPE BODY	SYS
USER_REPGROUPED_COLUMN	VIEW	SYS
USER_REPPARAMETER_COLUMN	VIEW	SYS
V1	VIEW	SYS
WM_DDL_UTIL	PACKAGE BODY	SYS
XDBURITYPE	TYPE	SYS
XDBURITYPE	TYPE BODY	SYS
XMLCONCAT	FUNCTION	SYS

   无效对象产生可能有多种原因：

   产生SQL对象名无效的问题大多原因是由于数据迁移导致的，另外应用程序的变化等也会造成数据库产生无效对象。

解决办法：     

$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 23 08:03:59 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn /as sysdba
Connected.
SQL> @/oracle/10g/rdbms/admin/utlrp.sql

 

重新编译后，无效对象只有10多个，系统用户没有无效对象。