Oracle中编译无效的对象
查询无效对象SQL: SELECT COUNT (*) FROM user_objects WHERE object_type IN (’PROCEDURE’,’FUNCTION’,’TRIGGER’,’VIEW’,’PACKAGE’) AND status = ’INVALID’;
以下三种方法:
①以SYSDBA用户,执行$ORACLE_HOME/rdbms/admin/utlrp.sql 脚本
②重新编译 FUNCTION、PACKAGE、TYPE、TRIGGER 等,和重建 INDEX 等。所不同的就是查询 all_objects 时的 object_type 不一样,还有要执行的 alter 语句不一样。
object_type 有哪些可用 select distinct object_type from all_objects 获取到;alter 的语句写法参考下面:
alter function function_name compile;
alter package package_name compile;
alter type type_name compile;
alter index index_name rebuild;
--等等
③在SQL*plus中利用中间脚本编译
编写SQL*Plus脚本,它可以帮组你扫描非法的脚本并尝试重新编译它们:
建立脚本reCompile.sql
编写SQL*Plus脚本,它可以帮组你扫描非法的脚本并尝试重新编译它们:
建立脚本reCompile.sql
SET feedback OFF
SET heading OFF
SET linesize 1000
SET pagesize 0
SET pause OFF
SET trimspool ON
SET verify OFF
spool tmp.sql;
SELECT ’alter ’||object_type||’ ’||owner||’.’||object_name||’ compile;’
FROM all_objects
WHERE status = ’INVALID’
AND object_type in
(’FUNCTION’,’JAVA SOURCE’,’JAVA CLASS’,’PROCEDURE’,’PACKAGE’,’TRIGGER’);
SELECT ’alter package ’||owner||’.’||object_name||’ compile body;’
FROM all_objects
WHERE status = ’INVALID’
AND object_type = ’PACKAGE BODY’;
spool OFF;
@tmp.sql
在SQL*Plus中
@reCompile.sql
SET heading OFF
SET linesize 1000
SET pagesize 0
SET pause OFF
SET trimspool ON
SET verify OFF
spool tmp.sql;
SELECT ’alter ’||object_type||’ ’||owner||’.’||object_name||’ compile;’
FROM all_objects
WHERE status = ’INVALID’
AND object_type in
(’FUNCTION’,’JAVA SOURCE’,’JAVA CLASS’,’PROCEDURE’,’PACKAGE’,’TRIGGER’);
SELECT ’alter package ’||owner||’.’||object_name||’ compile body;’
FROM all_objects
WHERE status = ’INVALID’
AND object_type = ’PACKAGE BODY’;
spool OFF;
@tmp.sql
在SQL*Plus中
@reCompile.sql