oracle 重新编译用户无效对象

1. oracle sys用户无效对象
4. select owner,object_name
5. , replace(object_type,' ','') object_type
6. ,to_char(created,'yyyy-mm-dd') as created
7. ,to_char(last_ddl_time,'yyyy-mm-dd') as last_ddl_time,
8. status
9. from dba_objects where status='INVALID' and owner='SYS';
11. OWNER OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_TIME STATUS
12. ------ --------------------- ------------- ----------- -------------- ----------
13. SYS ALL_TAB_STATISTICS VIEW 2011-09-17 2012-05-16 INVALID
14. SYS USER_TAB_STATISTICS VIEW 2011-09-17 2012-05-16 INVALID
15. SYS ALL_IND_STATISTICS VIEW 2011-09-17 2012-05-16 INVALID
16. SYS USER_IND_STATISTICS VIEW 2011-09-17 2012-05-16 INVALID
17. SYS VALIDATE_ORDIM PROCEDURE 2011-09-17 2012-05-16 INVALID
18. SYS DBMS_CUBE_ADVISE PACKAGEBODY 2011-09-17 2012-05-16 INVALID
19. SYS DBMS_CUBE PACKAGEBODY 2011-09-17 2012-05-16 INVALID
21. 方法1：手动重新rebuilt
23. SQL>alter view sys.ALL_TAB_STATISTICS compile；
25. SQL>alter view sys.USER_TAB_STATISTICS compile；
27. SQL>alter view ALL_IND_STATISTICS compile；
29. SQL>alter view sys.USER_IND_STATISTICS compile；
31. SQL>alter procedure sys.VALIDATE_ORDIM compile;
33. SQL>alter package DBMS_CUBE_ADVISE compile body;
35. SQL>alter package DBMS_CUBE compile body;
38. 方法2：
39. oracle用户下执行
41. $cd $ORACLE_HOME/rdbms/admin
42. $sqlplus / as sysdba
43. SQL>@utlprp.sql
46. 编译完成后，再次查看
48. SQL> select owner,object_name
49. 2 , replace(object_type,' ','') object_type
50. 3 ,to_char(created,'yyyy-mm-dd') as created
51. 4 ,to_char(last_ddl_time,'yyyy-mm-dd') as last_ddl_time,
52. 5 status
53. 6 from dba_objects where status='INVALID' and owner='SYS';
55. no rows selected
56. 方法3：

oracle sys用户无效对象


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';

OWNER   OBJECT_NAME           OBJECT_TYPE    CREATED     LAST_DDL_TIME  STATUS
------  --------------------- -------------  ----------- -------------- ----------
SYS     ALL_TAB_STATISTICS    VIEW           2011-09-17  2012-05-16     INVALID
SYS     USER_TAB_STATISTICS   VIEW           2011-09-17  2012-05-16     INVALID
SYS     ALL_IND_STATISTICS    VIEW           2011-09-17  2012-05-16     INVALID
SYS     USER_IND_STATISTICS   VIEW           2011-09-17  2012-05-16     INVALID
SYS     VALIDATE_ORDIM        PROCEDURE      2011-09-17  2012-05-16     INVALID
SYS     DBMS_CUBE_ADVISE      PACKAGEBODY    2011-09-17  2012-05-16     INVALID
SYS     DBMS_CUBE             PACKAGEBODY    2011-09-17  2012-05-16     INVALID

方法1：手动重新rebuilt

SQL>alter view sys.ALL_TAB_STATISTICS  compile； 

SQL>alter view sys.USER_TAB_STATISTICS compile；
 
SQL>alter view ALL_IND_STATISTICS      compile；
 
SQL>alter view sys.USER_IND_STATISTICS compile；

SQL>alter procedure sys.VALIDATE_ORDIM   compile;

SQL>alter package DBMS_CUBE_ADVISE compile body;
  
SQL>alter package DBMS_CUBE  compile body;


方法2：
oracle用户下执行

$cd $ORACLE_HOME/rdbms/admin
$sqlplus  /  as sysdba
SQL>@utlprp.sql


编译完成后，再次查看

SQL> select owner,object_name
  2  , replace(object_type,' ','') object_type
  3  ,to_char(created,'yyyy-mm-dd') as created
  4  ,to_char(last_ddl_time,'yyyy-mm-dd') as last_ddl_time,
  5  status
  6  from dba_objects where status='INVALID' and owner='SYS';

no rows selected         
                                                                                                                                                                                                                                                                                                                     方法3：

[javascript] view plain copy print ?

1. 以下是一个转帖的方法

以下是一个转帖的方法

[javascript] view plain copy print ?

2. --创建自动编译失效过程事务记录表
3. declare
4. tabcnt integer := 0;
5. begin
6. select count(*) into tabcnt from dba_tables where table_name='RECOMPILE_LOG';
7. if tabcnt = 0 then
8. execute immediate 'create table recompile_log(rdate date,errmsg varchar2(200))';
9. end if;
10. end;
11. /
13. --创建编译失效对象的存储过程
14. create or replace procedure recompile_invalid_objects
15. as
16. str_sql varchar2(200); --中间用到的sql语句
17. p_owner varchar2(20); --所有者名称，即SCHEMA
18. errm varchar2(200); --中间错误信息
19. begin
20. /*****************************************************/
21. p_owner := 'owner';/***用户名*************************/
22. /*****************************************************/
23. insert into recompile_log(rdate, errmsg) values(sysdate,'time to recompile invalid objects');
25. --编译失效存储过程
26. for invalid_procedures in (select object_name from all_objects
27. where status = 'INVALID' and object_type = 'PROCEDURE' and owner=upper(p_owner))
28. loop
29. str_sql := 'alter procedure ' ||invalid_procedures.object_name || ' compile';
30. begin
31. execute immediate str_sql;
32. exception
33. When Others Then
34. begin
35. errm := 'error by obj:'||invalid_procedures.object_name||' '||sqlerrm;
36. insert into recompile_log(rdate, errmsg) values(sysdate,errm);
37. end;
38. end;
39. end loop;
41. --编译失效函数
42. for invalid_functions in (select object_name from all_objects
43. where status = 'INVALID' and object_type = 'FUNCTION' and owner=upper(p_owner))
44. loop
45. str_sql := 'alter function ' ||invalid_functions.object_name || ' compile';
46. begin
47. execute immediate str_sql;
48. exception
49. When Others Then
50. begin
51. errm := 'error by obj:'||invalid_functions.object_name||' '||sqlerrm;
52. insert into recompile_log(rdate, errmsg) values(sysdate,errm);
53. end;
54. end;
55. end loop;
57. --编译失效包
58. for invalid_packages in (select object_name from all_objects
59. where status = 'INVALID' and object_type = 'PACKAGE' and owner=upper(p_owner))
60. loop
61. str_sql := 'alter package ' ||invalid_packages.object_name || ' compile';
62. begin
63. execute immediate str_sql;
64. exception
65. When Others Then
66. begin
67. errm := 'error by obj:'||invalid_packages.object_name||' '||sqlerrm;
68. insert into recompile_log(rdate, errmsg) values(sysdate,errm);
69. end;
70. end;
71. end loop;
73. --编译失效类型
74. for invalid_types in (select object_name from all_objects
75. where status = 'INVALID' and object_type = 'TYPE' and owner=upper(p_owner))
76. loop
77. str_sql := 'alter type ' ||invalid_types.object_name || ' compile';
78. begin
79. execute immediate str_sql;
80. exception
81. When Others Then
82. begin
83. errm := 'error by obj:'||invalid_types.object_name||' '||sqlerrm;
84. insert into recompile_log(rdate, errmsg) values(sysdate,errm);
85. end;
86. end;
87. end loop;
89. --编译失效索引
90. for invalid_indexs in (select object_name from all_objects
91. where status = 'INVALID' and object_type = 'INDEX' and owner=upper(p_owner))
92. loop
93. str_sql := 'alter index ' ||invalid_indexs.object_name || ' rebuild';
94. begin
95. execute immediate str_sql;
96. exception
97. When Others Then
98. begin
99. errm := 'error by obj:'||invalid_indexs.object_name||' '||sqlerrm;
100. insert into recompile_log(rdate, errmsg) values(sysdate,errm);
101. end;
102. end;
103. end loop;
105. --编译失效触发器
106. for invalid_triggers in (select object_name from all_objects
107. where status = 'INVALID' and object_type = 'TRIGGER' and owner=upper(p_owner))
108. loop
109. str_sql := 'alter trigger ' ||invalid_triggers.object_name || ' compile';
110. begin
111. execute immediate str_sql;
112. exception
113. When Others Then
114. begin
115. errm := 'error by obj:'||invalid_triggers.object_name||' '||sqlerrm;
116. insert into recompile_log(rdate, errmsg) values(sysdate,errm);
117. end;
118. end;
119. end loop;
121. end;
122. /
124. --创建任务计划，每天早上8点整执行该任务，且保证此任务有且只有一个
125. declare
126. jobcnt integer :=0;
127. job_recompile number := 0;
128. str_sql varchar2(200);
129. begin
130. select count(*) into jobcnt from all_jobs where what = 'recompile_invalid_objects;' and broken = 'N';
131. if jobcnt > 0 then
132. for jobs in (select job from all_jobs where what = 'recompile_invalid_objects;' and broken = 'N')
133. loop
134. str_sql := 'begin dbms_job.remove('||jobs.job||'); end;';
135. begin
136. execute immediate str_sql;
137. exception
138. When Others Then null;
139. end;
140. end loop;
141. end if;
142. --创建任务计划
143. dbms_job.submit(job_recompile,'recompile_invalid_objects;',sysdate,'TRUNC(SYSDATE + 1) + 8/24');
144. --启动任务计划
145. dbms_job.run(job_recompile);
146. end;
147. /