8 Mart 2012 Perşembe

[EN] COMPILE ALL SPs

---compile all invalid
$>cd $ORACLE_HOME/rdbms/admin
$> sqlplus / as sysdba
SQL> @utlrp.sql


---compile all bodies

SELECT 'ALTER PACKAGE ' || a.owner || '.' || a.object_name || ' COMPILE BODY;'
FROM    dba_objects a
WHERE   a.object_type = 'PACKAGE BODY'
AND     a.status      = 'INVALID'
AND     a.owner       = Decode(Upper('BUNYAMIN'), 'ALL',a.owner, Upper('BUNYAMIN'));


---compile all specs

SELECT 'ALTER PACKAGE ' || a.owner || '.' || a.object_name || ' COMPILE;'
FROM    dba_objects a
WHERE   a.object_type = 'PACKAGE'
AND     a.status      = 'INVALID'
AND     a.owner       = Decode(Upper('BUNYAMIN'), 'ALL',a.owner, Upper('BUNYAMIN'));


---compile all procs

SELECT 'ALTER PROCEDURE ' || a.owner || '.' || a.object_name || ' COMPILE;'
FROM    dba_objects a
WHERE   a.object_type = 'PROCEDURE'
AND     a.status      = 'INVALID'
AND     a.owner       = Decode(Upper('BUNYAMIN'), 'ALL',a.owner, Upper('BUNYAMIN'));

---compile all funcs

SELECT 'ALTER FUNCTION ' || a.owner || '.' || a.object_name || ' COMPILE;'
FROM    dba_objects a
WHERE   a.object_type = 'FUNCTION'
AND     a.status      = 'INVALID'
AND     a.owner       = Decode(Upper('BUNYAMIN'), 'ALL',a.owner, Upper('BUNYAMIN'));


---compile all trigs

SELECT 'ALTER TRIGGER ' || a.owner || '.' || a.object_name || ' COMPILE;'
FROM    dba_objects a
WHERE   a.object_type = 'TRIGGER'
AND     a.status      = 'INVALID'
AND     a.owner       = Decode(Upper('BUNYAMIN'), 'ALL',a.owner, Upper('BUNYAMIN'));

---compile all views

SELECT 'ALTER VIEW ' || a.owner || '.' || a.object_name || ' COMPILE;'
FROM    dba_objects a
WHERE   a.object_type = 'VIEW'
AND     a.status      = 'INVALID'
AND     a.owner       = Decode(Upper('BUNYAMIN'), 'ALL',a.owner, Upper('BUNYAMIN'));

Hiç yorum yok:

Yorum Gönder