oracle失效对象是什么意思,Oracle 处理无效对象数

Oracle 处理无效对象数

大量无效对象数，用户基本都是sys

1. 查看系统中无效对象数个数

SQL>  SELECT COUNT(*) FROM dba_objects WHERE status = 'INVALID' AND object_name NOT LIKE 'BIN$%' AND object_type NOT IN ('MATERIALIZED VIEW');

COUNT(*)

----------

846

SQL>

查看具体信息：

SQL>   SELECT OBJECT_NAME, OBJECT_TYPE, OWNER, CREATED, LAST_DDL_TIME, TIMESTAMP FROM dba_objects WHERE status = 'INVALID' AND object_name NOT LIKE 'BIN$%' AND object_type NOT IN ('MATERIALIZED VIEW');

OBJECT_NAME                                                                                                                      OBJECT_TYPE         OWNER                           CREATED             LAST_DDL_TIME       TIMESTAMP

-------------------------------------------------------------------------------------------------------------------------------- ------------------- ------------------------------ ------------------- ------------------- -------------------

oracle/xquery/SQLDataSource                                                                                                      JAVA CLASS          SYS                     2014-10-22 14:08:50 2014-10-22 14:08:50 2014-10-22:14:08:50

oracle/xquery/SQLIterator                                                                                                        JAVA CLASS          SYS                     2014-10-22 14:08:50 2014-10-22 14:08:50 2014-10-22:14:08:50

/5ec24a53_XDBPreparedXQuery                                                                                                      JAVA CLASS          SYS                     2014-10-22 14:08:50 2014-10-22 14:08:50 2014-10-22:14:08:50

oracle/xquery/XMLDataSource                                                                                                      JAVA CLASS          SYS                     2014-10-22 14:08:50 2014-10-22 14:08:50 2014-10-22:14:08:50

/c4e8112a_XMLDataSourceIterato                                                                                                   JAVA CLASS          SYS                     2014-10-22 14:08:50 2014-10-22 14:08:50 2014-10-22:14:08:50

oracle/xquery/XQLPlus                                                                                                            JAVA CLASS          SYS                     2014-10-22 14:08:50 2014-10-22 14:08:50 2014-10-22:14:08:50

oracle/xquery/XQueryContext                                                                                                      JAVA CLASS          SYS                     2014-10-22 14:08:50 2014-10-22 14:08:50 2014-10-22:14:08:50

oracle/xquery/XQueryDataSource                                                                                                   JAVA CLASS          SYS                     2014-10-22 14:08:50 2014-10-22 14:08:50 2014-10-22:14:08:50

/53ad31cb_TagsLocator                                                                                                            JAVA CLASS          EXFSYS                          2014-10-22 14:10:56 2014-10-22 14:21:50 2014-10-22:14:10:56

/357abd97_XPathFilter                                                                                                            JAVA CLASS          EXFSYS                          2014-10-22 14:10:56 2014-10-22 14:21:48 2014-10-22:14:10:56

846 rows selected.

产生无效对象数原因：导数据或者编译程序

处理办法：

办法1：逐个恢复

查看view对象

SQL>

select owner,object_name

, replace(object_type,' ','') object_type

,to_char(created,'yyyy-mm-dd') as created

,to_char(last_ddl_time,'yyyy-mm-dd') as last_ddl_time,

status

from dba_objects where status='INVALID' and owner='SYS' and object_type='VIEW';

OWNER

------------------------------

OBJECT_NAME

--------------------------------------------------------------------------------

OBJECT_TYPE         CREATED    LAST_DDL_T STATUS

------------------- ---------- ---------- -------

SYS

KU$_ZM_VIEW_FH_VIEW

VIEW                2014-10-22 2014-10-22 INVALID

SQL> ALTER VIEW SYS.KU$_ZM_VIEW_FH_VIEW COMPILE;

SQL> ALTER PACHAGE DBMS_CUBE  compile body;

办法2：  全部恢复

在Oracle目录下找到utlrp.sql文件。

$cd $ORACLE_HOME/rdbms/admin

$sqlplus  /  as sysdba

SQL>@utlprp.sql

重新编译

[oracle@testdb1 ~]:testdb1> sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 12 14:37:48 2014

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @/app/oracle/ora11g/rdbms/admin/utlrp.sql

TIMESTAMP

--------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_BGN  2014-11-12 14:40:20

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid

DOC>   objects in the database. Recompilation time is proportional to the

DOC>   number of invalid objects in the database, so this command may take

DOC>   a long time to execute on a database with a large number of invalid

DOC>   objects.

DOC>

DOC>   Use the following queries to track recompilation progress:

DOC>

DOC>   1. Query returning the number of invalid objects remaining. This

DOC>      number should decrease with time.

DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

DOC>

DOC>   2. Query returning the number of objects compiled so far. This number

DOC>      should increase with time.

DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

DOC>

DOC>   This script automatically chooses serial or parallel recompilation

DOC>   based on the number of CPUs available (parameter cpu_count) multiplied

DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).

DOC>   On RAC, this number is added across all RAC nodes.

DOC>

DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel

DOC>   recompilation. Jobs are created without instance affinity so that they

DOC>   can migrate across RAC nodes. Use the following queries to verify

DOC>   whether UTL_RECOMP jobs are being created and run correctly:

DOC>

DOC>   1. Query showing jobs created by UTL_RECOMP

DOC>         SELECT job_name FROM dba_scheduler_jobs

DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>

DOC>   2. Query showing UTL_RECOMP jobs that are running

DOC>         SELECT job_name FROM dba_scheduler_running_jobs

DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>#

PL/SQL procedure successfully completed.

TIMESTAMP

--------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_END  2014-11-12 14:41:12

DOC> The following query reports the number of objects that have compiled

DOC> with errors.

DOC>

DOC> If the number is higher than expected, please examine the error

DOC> messages reported with each object (using SHOW ERRORS) to see if they

DOC> point to system misconfiguration or resource constraints that must be

DOC> fixed before attempting to recompile these objects.

DOC>#

OBJECTS WITH ERRORS

-------------------

0

DOC> The following query reports the number of errors caught during

DOC> recompilation. If this number is non-zero, please query the error

DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors

DOC> are due to misconfiguration or resource constraints that must be

DOC> fixed before objects can compile successfully.

DOC>#

ERRORS DURING RECOMPILATION

---------------------------

0

Function created.

PL/SQL procedure successfully completed.

Function dropped.

PL/SQL procedure successfully completed.

SQL>

检查结果：

SQL> select count(*) from dba_objects where status = 'INVALID';

COUNT(*)

----------

0

SQL>

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

自动处理无效对象

转载：

===============================================================================

创建自动编译失效过程事务记录表

declare

tabcnt integer := 0;

begin

select count(*) into tabcnt from dba_tables where table_name='RECOMPILE_LOG';

if tabcnt = 0 then

execute immediate 'create table recompile_log(rdate date,errmsg varchar2(200))';

end if;

end;

/

===============================================================================

创建编译失效对象的存储过程

create or replace procedure recompile_invalid_objects

as

str_sql varchar2(200);  --中间用到的sql语句

p_owner varchar2(20);   --所有者名称，即SCHEMA

errm varchar2(200);     --中间错误信息

begin

/*****************************************************/

p_owner := 'owner';/***用户名*************************/

/*****************************************************/

insert into recompile_log(rdate, errmsg) values(sysdate,'time to recompile invalid objects');

--编译失效存储过程

for invalid_procedures in (select object_name from all_objects

where status = 'INVALID' and object_type = 'PROCEDURE' and owner=upper(p_owner))

loop

str_sql := 'alter procedure ' ||invalid_procedures.object_name || ' compile';

begin

execute immediate str_sql;

exception

When Others Then

begin

errm := 'error by obj:'||invalid_procedures.object_name||' '||sqlerrm;

insert into recompile_log(rdate, errmsg) values(sysdate,errm);

end;

end;

end loop;

--编译失效函数

for invalid_functions in (select object_name from all_objects

where status = 'INVALID' and object_type = 'FUNCTION' and owner=upper(p_owner))

loop

str_sql := 'alter function ' ||invalid_functions.object_name || ' compile';

begin

execute immediate str_sql;

exception

When Others Then

begin

errm := 'error by obj:'||invalid_functions.object_name||' '||sqlerrm;

insert into recompile_log(rdate, errmsg) values(sysdate,errm);

end;

end;

end loop;

--编译失效包

for invalid_packages in (select object_name from all_objects

where status = 'INVALID' and object_type = 'PACKAGE' and owner=upper(p_owner))

loop

str_sql := 'alter package ' ||invalid_packages.object_name || ' compile';

begin

execute immediate str_sql;

exception

When Others Then

begin

errm := 'error by obj:'||invalid_packages.object_name||' '||sqlerrm;

insert into recompile_log(rdate, errmsg) values(sysdate,errm);

end;

end;

end loop;

--编译失效类型

for invalid_types in (select object_name from all_objects

where status = 'INVALID' and object_type = 'TYPE' and owner=upper(p_owner))

loop

str_sql := 'alter type ' ||invalid_types.object_name || ' compile';

begin

execute immediate str_sql;

exception

When Others Then

begin

errm := 'error by obj:'||invalid_types.object_name||' '||sqlerrm;

insert into recompile_log(rdate, errmsg) values(sysdate,errm);

end;

end;

end loop;

--编译失效索引

for invalid_indexs in (select object_name from all_objects

where status = 'INVALID' and object_type = 'INDEX' and owner=upper(p_owner))

loop

str_sql := 'alter index ' ||invalid_indexs.object_name || ' rebuild';

begin

execute immediate str_sql;

exception

When Others Then

begin

errm := 'error by obj:'||invalid_indexs.object_name||' '||sqlerrm;

insert into recompile_log(rdate, errmsg) values(sysdate,errm);

end;

end;

end loop;

--编译失效触发器

for invalid_triggers in (select object_name from all_objects

where status = 'INVALID' and object_type = 'TRIGGER' and owner=upper(p_owner))

loop

str_sql := 'alter trigger ' ||invalid_triggers.object_name || ' compile';

begin

execute immediate str_sql;

exception

When Others Then

begin

errm := 'error by obj:'||invalid_triggers.object_name||' '||sqlerrm;

insert into recompile_log(rdate, errmsg) values(sysdate,errm);

end;

end;

end loop;

end;

/

===============================================================================

创建任务计划，每天早上8点整执行该任务，且保证此任务有且只有一个

declare

jobcnt integer :=0;

job_recompile number := 0;

str_sql varchar2(200);

begin

select count(*) into jobcnt from all_jobs where what = 'recompile_invalid_objects;' and broken = 'N';

if jobcnt > 0 then

for jobs in (select job from all_jobs where what = 'recompile_invalid_objects;' and broken = 'N')

loop

str_sql := 'begin dbms_job.remove('||jobs.job||'); end;';

begin

execute immediate str_sql;

exception

When Others Then null;

end;

end loop;

end if;

--创建任务计划

dbms_job.submit(job_recompile,'recompile_invalid_objects;',sysdate,'TRUNC(SYSDATE + 1) + 8/24');

--启动任务计划

dbms_job.run(job_recompile);

end;

/

===============================================================================

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29500582/viewspace-1329832/，如需转载，请注明出处，否则将追究法律责任。