oracle怎么一键编译,一次性编译ORACLE中所有对象 | 学步园

这个存储过程不是我写的，但是很好使，特别适合刚导入对象看到满屏幕的叉。

CREATE OR REPLACE  PROCEDURE "USER"."RECOMPILE_SCHEMA"  IS

v_type user_objects.object_type%TYPE;

v_name user_objects.object_name%TYPE;

v_stat user_objects.status%TYPE;

CURSOR c_obj IS

SELECT base

FROM (SELECT a.object_id base,

b.object_id rel

FROM user_objects    a,

user_objects    b,

sys.dependency$ c

WHERE a.object_id = c.d_obj# AND

b.object_id = c.p_obj# AND

a.object_type IN

('PACKAGE', 'PROCEDURE', 'FUNCTION', 'PACKAGE BODY',

--  'VIEW',

'TRIGGER') AND

b.object_type IN

('PACKAGE', 'PROCEDURE', 'FUNCTION', 'PACKAGE BODY',

--  'VIEW',

'TRIGGER') AND

NOT a.object_name = b.object_name) objects

CONNECT BY base = PRIOR rel

GROUP BY base

ORDER BY MAX(LEVEL) DESC;

BEGIN

-- loop through all objects in order of dependancy.

FOR c_row IN c_obj LOOP

-- select the objects attributes (type, name & status).

SELECT object_type,

object_name,

status

INTO v_type,

v_name,

v_stat

FROM user_objects

WHERE object_id = c_row.base;

-- if the OBJECT is INVALID, recompile it.

IF v_stat = 'INVALID' THEN

dbms_ddl.alter_compile(v_type, USER, v_name);

END IF;

END LOOP;

-- Recompile all remaining INVALID OBJECTS (all those without dependencies).

FOR c_row IN (SELECT object_type,

object_name

FROM user_objects

WHERE status = 'INVALID' AND

object_type IN ('PACKAGE', 'PROCEDURE', 'FUNCTION',

'TRIGGER', 'PACKAGE BODY',

--   'VIEW',

'TRIGGER')) LOOP

dbms_ddl.alter_compile(c_row.object_type, USER, c_row.object_name);

END LOOP;

END recompile_schema;

要编译一个用户的对象，只要用该用户的口令登陆，而后执行存储过程即可。

执行前还需把SYS用户的dependency$ 给授权。