重新编译数据库中的失效对象

来源：互联网发布：淘宝部分商品退款编辑：程序博客网时间：2024/04/28 00:59

DECLARE

obj_number number := 0;

cursor C1 is select o.obj#,

'ALTER ' || decode (o.type#,

4, 'VIEW ',

7, 'PROCEDURE ',

8, 'FUNCTION ',

9, 'PACKAGE ',

11, 'PACKAGE ',

12, 'TRIGGER ',

13, 'TYPE ',

14, 'TYPE ',

' ') ||

'"' || u.name || '"."' || o.name || '" COMPILE ' ||

decode (o.type#,

9, 'SPECIFICATION',

11, 'BODY',

13, 'SPECIFICATION',

14, 'BODY',

' ')

from SYS.obj$ o, SYS.user$ u

where o.obj# > obj_number and

u.user# = o.owner# and o.remoteowner is NULL and

o.status in (4,5,6) and o.type# in (4, 7, 8, 9, 11, 12, 13, 14)

order by o.obj#;

DDL_CURSOR integer;

ddl_statement varchar2(200);

iterations number;

loop_count number;

my_err number;

validate number;

BEGIN

loop_count := 0;

select count(*) into iterations from SYS.obj$ where remoteowner is NULL and

status in (4,5,6) and type# in (4, 7, 8, 9, 11, 12, 13, 14);

DDL_CURSOR := dbms_sql.open_cursor;

OPEN C1;

LOOP

BEGIN

FETCH C1 INTO obj_number, ddl_statement;

EXIT WHEN C1%NOTFOUND OR loop_count > iterations;

EXCEPTION

WHEN OTHERS THEN

my_err := SQLCODE;

IF my_err = -1555 THEN

CLOSE C1;

OPEN C1;

GOTO continue;

ELSE

RAISE;

END IF;

END;

select count(*) into validate from SYS.obj$ where obj# = obj_number and

status in (4,5,6);

IF validate = 1 THEN

BEGIN

dbms_sql.parse(DDL_CURSOR, ddl_statement, dbms_sql.native);

EXCEPTION

WHEN OTHERS THEN

null;

END;

END IF;

<<continue>>

loop_count := loop_count + 1;

END LOOP;

dbms_sql.close_cursor(DDL_CURSOR);

CLOSE C1;

END;