Re: How Compile all the objects

> I would like to compile all the objects of the database at
> the same time. I made a procedure doing this and using the
> procedure "dbms_ddl.alter_compile" with the parameters
> type_object,owner and name_object I took in the all_objects
> tablespace. I created it under the user sys and when i
> execute it with exec procedure_name it said it has succeded
> without doing nothing. I would like to know why it doesn't
> work or if there is another way to do it with the schema
> manager for example .

Save this as script and run from SYS or SYSTEM account in for ex. SQL*Plus. And, please, ignore polish messages:

SET ECHO OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SET PAGESIZE 0 SPOOL compobj.sql

SELECT 'SET FEEDBACK ON' FROM DUAL; SELECT 'PROMPT Kompiluje VIEW ' || OBJECT_NAME || ' ...' || CHR(10) ||

       'ALTER VIEW ' ||OWNER||'.'|| OBJECT_NAME || ' COMPILE;' || CHR(10) || 'SHOW ERRORS;' || CHR(10) || 'PROMPT;'   FROM DBA_OBJECTS
 WHERE STATUS <> 'VALID'
   AND OBJECT_TYPE = 'VIEW'
 ORDER BY OBJECT_NAME; SELECT 'PROMPT Kompiluje TRIGGER ' || OBJECT_NAME || ' ...' || CHR(10) ||

       'ALTER TRIGGER ' ||OWNER||'.'|| OBJECT_NAME || ' COMPILE;' || CHR(10) || 'SHOW ERRORS;' || CHR(10) || 'PROMPT;'   FROM DBA_OBJECTS
 WHERE STATUS <> 'VALID'
   AND OBJECT_TYPE = 'TRIGGER'
 ORDER BY OBJECT_NAME; SELECT 'PROMPT Kompiluje PROCEDURE ' || OBJECT_NAME || ' ...' || CHR(10) ||

       'ALTER PROCEDURE ' ||OWNER||'.'|| OBJECT_NAME || ' COMPILE;' || CHR(10) || 'SHOW ERRORS;' || CHR(10) || 'PROMPT;'   FROM DBA_OBJECTS
 WHERE STATUS <> 'VALID'
   AND OBJECT_TYPE = 'PROCEDURE'
 ORDER BY OBJECT_NAME; SELECT 'PROMPT Kompiluje FUNKCJE ' || OBJECT_NAME || ' ...' || CHR(10) ||

       'ALTER FUNCTION ' ||OWNER||'.'|| OBJECT_NAME || ' COMPILE;' || CHR(10) || 'SHOW ERRORS;' || CHR(10) || 'PROMPT;'   FROM DBA_OBJECTS
 WHERE STATUS <> 'VALID'
   AND OBJECT_TYPE = 'FUNCTION'
 ORDER BY OBJECT_NAME; SELECT 'PROMPT Kompiluje PAKIET ' || OBJECT_NAME || ' ...' || CHR(10) ||

       'ALTER PACKAGE ' ||OWNER||'.'|| OBJECT_NAME || ' COMPILE;' || CHR(10) || 'SHOW ERRORS;' || CHR(10) || 'PROMPT;'   FROM USER_OBJECTS
 WHERE STATUS <> 'VALID'
   AND OBJECT_TYPE = 'PACKAGE'
 ORDER BY OBJECT_NAME; SELECT 'PROMPT Kompiluje CIALO PAKIETU ' || OBJECT_NAME || ' ...' || CHR(10) ||
       'ALTER PACKAGE ' ||OWNER||'.'|| OBJECT_NAME || ' COMPILE BODY;' || CHR(10) || 'SHOW ERRORS;' || CHR(10) || 'PROMPT;'   FROM DBA_OBJECTS
 WHERE STATUS <> 'VALID'
   AND OBJECT_TYPE = 'PACKAGE BODY'
 ORDER BY OBJECT_NAME; SPOOL OFF SET TERMOUT ON
SET ECHO OFF
SET FEEDBACK OFF SELECT '>>> Uszkodzone obiekty w bazie '   FROM DUAL; SELECT OBJECT_TYPE || ': ' ||OWNER||'.'|| OBJECT_NAME   FROM DBA_OBJECTS
 WHERE STATUS <> 'VALID'
 ORDER BY OBJECT_TYPE, OBJECT_NAME; SELECT '>>> Suma: ' || COUNT(*)
  FROM DBA_OBJECTS
 WHERE STATUS <> 'VALID';

START compobj.sql Received on Tue Mar 28 2000 - 10:40:37 CEST