Full compile invalid obje by PL/SQL code


grant execute on SYS.FULL_COMPILE to ANAR;
CREATE OR REPLACE PROCEDURE SYS.FULL_COMPILE(xfull IN BOOLEAN,xowner IN VARCHAR2,xoutput IN BOOLEAN)
AS
 CURSOR c1
 IS
 SELECT 'ALTER ' || object_type || ' ' || owner || '.' || object_name || ' COMPILE' strsql
 FROM dba_objects
 WHERE status = 'INVALID'
 AND object_type IN ('PROCEDURE','FUNCTION','VIEW','PACKAGE','TRIGGER')
 AND owner like xowner
 UNION ALL
 SELECT 'ALTER PACKAGE' || ' ' || owner || '.' || object_name || ' COMPILE BODY' strsql
 FROM dba_objects
 WHERE status = 'INVALID' AND object_type = 'PACKAGE BODY'
 AND owner like xowner;
 CURSOR c2
 IS
 SELECT 'ALTER ' || object_type || ' ' || owner || '.' || object_name || ' COMPILE' strsql
 FROM dba_objects
 WHERE object_type IN ('PROCEDURE','FUNCTION','VIEW','PACKAGE','TRIGGER')
 AND owner like xowner
 UNION ALL
 SELECT 'ALTER PACKAGE' || ' ' || owner || '.' || object_name || ' COMPILE BODY' strsql
 FROM dba_objects
 WHERE object_type = 'PACKAGE BODY'
 AND owner like xowner;
 CURSOR c3
 IS
 SELECT *
 FROM dba_objects
 WHERE status = 'INVALID'
 AND owner like xowner
 AND object_type IN ('PROCEDURE','FUNCTION','VIEW','PACKAGE','TRIGGER','PACKAGE BODY')
 ORDER BY owner,object_name,object_type;
 alc1 c1%ROWTYPE;
 alc2 c2%ROWTYPE;
 alc3 c3%ROWTYPE;
 cur INTEGER;
 ret INTEGER;
 db_name VARCHAR2(50);
BEGIN
IF xfull THEN
 OPEN c2;
 LOOP
 FETCH c2 INTO alc2;
 EXIT WHEN c2%NOTFOUND;
 BEGIN
 cur := DBMS_SQL.open_cursor;
 DBMS_SQL.parse (cur,alc2.strsql , DBMS_SQL.NATIVE);
 ret := DBMS_SQL.EXECUTE (cur);
 DBMS_SQL.close_cursor (cur);
 EXCEPTION
 WHEN OTHERS THEN
 NULL;
 IF DBMS_SQL.is_open (cur) THEN
 DBMS_SQL.close_cursor (cur);
 END IF;
 END;
 END LOOP;
 CLOSE c2;
ELSE
 OPEN c1;
 LOOP
 FETCH c1 INTO alc1;
 EXIT WHEN c1%NOTFOUND;
 BEGIN
 cur := DBMS_SQL.open_cursor;
 DBMS_SQL.parse (cur,alc1.strsql , DBMS_SQL.NATIVE);
 ret := DBMS_SQL.EXECUTE (cur);
 DBMS_SQL.close_cursor (cur);
 EXCEPTION
 WHEN OTHERS THEN
 NULL;
 IF DBMS_SQL.is_open (cur) THEN
 DBMS_SQL.close_cursor (cur);
 END IF;
 END;
 END LOOP;
 CLOSE c1;
END IF;
 
IF xoutput THEN
 DBMS_OUTPUT.ENABLE(1000000);
 SELECT name INTO db_name FROM v$database;
 DBMS_OUTPUT.PUT_LINE('DATABASE NAME = '||db_name);
 DBMS_OUTPUT.PUT_LINE('.');
 DBMS_OUTPUT.PUT_LINE('. INVALID OBJECTS LIST ');
 DBMS_OUTPUT.PUT_LINE('.');
 DBMS_OUTPUT.PUT_LINE(RPAD('OWNER',20,' ')||' '||RPAD('OBJECT NAME',40,' ')||' '||RPAD('OBJECT TYPE',15,' '));
 DBMS_OUTPUT.PUT_LINE(RPAD('-',20,'-')||' '||RPAD('-',40,'-')||' '||RPAD('-',15,'-'));
 
 OPEN c3;
 
 LOOP
 FETCH c3 INTO alc3;
 EXIT WHEN c3%NOTFOUND;
 DBMS_OUTPUT.PUT_LINE(RPAD(alc3.owner,20,' ')||' '||RPAD(alc3.object_name,40,' ')||' '||RPAD(alc3.object_type,15,' '));
 END LOOP;
 
 CLOSE c3;
 DBMS_OUTPUT.PUT_LINE('.');
 DBMS_OUTPUT.PUT_LINE('.');
END IF;
END;
/
select'exec sys.full_compile(FALSE,'''||owner||''',TRUE);'from dba_objects where status ='INVALID' and owner not in ('SYS','SYSTEM','MASTER','MASTER','SYSMAN','OLAPSYS') and object_type <> 'SYNONYM'

group by owner;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: