DBAPack/compila.sql at master · flvani/DBAPack · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
SET VERIFY OFF FEED OFF
DEFINE USU = "&1."

PROMPT
PROMPT EXECUTANDO DBMS_UTILITY.COMPILE_SCHEMA( UPPER( '&USU.' ), compile_all=> false )
EXEC DBMS_UTILITY.COMPILE_SCHEMA( UPPER( '&USU.' ), compile_all=> false )

COL STMT FORMAT A80
SET SERVEROUT ON
DECLARE
  CURSOR C IS
    SELECT 'ALTER ' ||
      DECODE( OWNER, 'PUBLIC', 'PUBLIC ', '' ) ||
      DECODE( OBJECT_TYPE, 'PACKAGE BODY', 'PACKAGE', OBJECT_TYPE )  || ' ' ||
      DECODE( OWNER, 'PUBLIC', '',  OWNER||'.' )  || OBJECT_NAME || ' COMPILE' ||
      DECODE( OBJECT_TYPE, 'PACKAGE BODY', ' BODY', '' )  STMT
    FROM DBA_OBJECTS
    WHERE STATUS <> 'VALID'
    AND OWNER = UPPER( '&USU.' )
    ORDER BY OBJECT_TYPE;
BEGIN
  DBMS_OUTPUT.ENABLE( 100000 );
  FOR R IN C LOOP
    BEGIN
      EXECUTE IMMEDIATE R.STMT;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE( R.STMT );
        DBMS_OUTPUT.PUT_LINE( SUBSTR(SQLERRM, 1, 254) || CHR(10) );
    END;
  END LOOP;
END;
/

SELECT OWNER, OBJECT_TYPE, OBJECT_NAME, STATUS
FROM DBA_OBJECTS
WHERE STATUS <> 'VALID'
AND OWNER = UPPER( '&USU.' );
/

SELECT COUNT(*) INVALIDOS
FROM DBA_OBJECTS
WHERE STATUS <> 'VALID'
AND OWNER = UPPER( '&USU.' );

SELECT OWNER, OBJECT_TYPE, COUNT(*)
FROM DBA_OBJECTS
WHERE STATUS<>'VALID'
AND OWNER = UPPER( '&USU.' )
GROUP BY OWNER, OBJECT_TYPE
HAVING COUNT(*) >= 3
ORDER BY OWNER
/


PROMPT
COL STMT CLEAR
SET VERIFY ON FEED 6
UNDEFINE 1 USU