SET PAGESIZE 1000 SET LINESIZE 100 SET HEADING OFF SPOOL ALT_ALLINVALID.SQL SELECT 'SPOOL ALT_ALLINVALID_RESULT.LST' FROM DUAL; SELECT 'ALTER VIEW '||OBJECT_NAME||' COMPILE;' FROM USER_OBJECTS WHERE OBJECT_TYPE='VIEW' AND STATUS='INVALID'; SELECT 'ALTER TRIGGER '||OBJECT_NAME||' COMPILE;' FROM USER_OBJECTS WHERE OBJECT_TYPE='TRIGGER' AND STATUS='INVALID'; SELECT 'ALTER PROCEDURE '||OBJECT_NAME||' COMPILE;' FROM USER_OBJECTS WHERE OBJECT_TYPE='PROCEDURE' AND STATUS='INVALID'; SELECT 'SPOOL OFF' FROM DUAL; SPOOL OFF @ALT_ALLINVALID.SQL EXIT;
recompile.SQL 文件如下: SET PAGESIZE 1000 SET LINESIZE 100 SET HEADING OFF SPOOL ALT_ALLINVALID.SQL SELECT 'SPOOL ALT_ALLINVALID_RESULT.LST' FROM DUAL; SELECT 'ALTER VIEW '||OBJECT_NAME||' COMPILE;' FROM USER_OBJECTS WHERE OBJECT_TYPE='VIEW' AND STATUS='INVALID'; SELECT 'ALTER TRIGGER '||OBJECT_NAME||' COMPILE;' FROM USER_OBJECTS WHERE OBJECT_TYPE='TRIGGER' AND STATUS='INVALID'; SELECT 'ALTER PROCEDURE '||OBJECT_NAME||' COMPILE;' FROM USER_OBJECTS WHERE OBJECT_TYPE='PROCEDURE' AND STATUS='INVALID'; SELECT 'SPOOL OFF' FROM DUAL; SPOOL OFF @ALT_ALLINVALID.SQL EXIT; scall.bat SQLPLUS scott/tigger@DB_SUN @recompile.SQL
SET LINESIZE 100
SET HEADING OFF
SPOOL ALT_ALLINVALID.SQL
SELECT 'SPOOL ALT_ALLINVALID_RESULT.LST' FROM DUAL;
SELECT 'ALTER VIEW '||OBJECT_NAME||' COMPILE;' FROM USER_OBJECTS WHERE OBJECT_TYPE='VIEW' AND STATUS='INVALID';
SELECT 'ALTER TRIGGER '||OBJECT_NAME||' COMPILE;' FROM USER_OBJECTS WHERE OBJECT_TYPE='TRIGGER' AND STATUS='INVALID';
SELECT 'ALTER PROCEDURE '||OBJECT_NAME||' COMPILE;' FROM USER_OBJECTS WHERE OBJECT_TYPE='PROCEDURE' AND STATUS='INVALID';
SELECT 'SPOOL OFF' FROM DUAL;
SPOOL OFF
@ALT_ALLINVALID.SQL
EXIT;
文件如下:
SET PAGESIZE 1000
SET LINESIZE 100
SET HEADING OFF
SPOOL ALT_ALLINVALID.SQL
SELECT 'SPOOL ALT_ALLINVALID_RESULT.LST' FROM DUAL;
SELECT 'ALTER VIEW '||OBJECT_NAME||' COMPILE;' FROM USER_OBJECTS WHERE OBJECT_TYPE='VIEW' AND STATUS='INVALID';
SELECT 'ALTER TRIGGER '||OBJECT_NAME||' COMPILE;' FROM USER_OBJECTS WHERE OBJECT_TYPE='TRIGGER' AND STATUS='INVALID';
SELECT 'ALTER PROCEDURE '||OBJECT_NAME||' COMPILE;' FROM USER_OBJECTS WHERE OBJECT_TYPE='PROCEDURE' AND STATUS='INVALID';
SELECT 'SPOOL OFF' FROM DUAL;
SPOOL OFF
@ALT_ALLINVALID.SQL
EXIT;
scall.bat
SQLPLUS scott/tigger@DB_SUN @recompile.SQL
等下一次再被调用时, Oracle会自动重新编译那些对象.
但是,如果表结构变化很大的话,那么必须重新修改依附在上面上的对象,
然后在编译.
因为字段名都没有了,不管怎么都会报错的.