|
Tip of the Week
Tip for Week of October 30, 2006
Revalidating Invalidated Views
This tip comes from
Bruno Vroman, a DBA at Capgemini in Brussels, Belgium.
Usually, when a view becomes invalid due to DDL work on the underlying table, its first usage (SELECT ... FROM myview ...) revalidates it, or it can be revalidated via "ALTER VIEW myview COMPILE;". But sometimes a view remains invalid, such as when there is a bug. In a case like this, the view has to be recreated.
This script checks all the views of the user running it; it recompiles the invalid views and rebuilds the views that cannot be recompiled successfully.
SET SERVEROUT ON FOR WRA SIZE 50000
DECLARE
i1 PLS_INTEGER := 0;
i2 PLS_INTEGER := 0;
CURSOR c IS
SELECT view_name, text, text_length
FROM user_views, user_objects
WHERE object_type = 'VIEW'
AND object_name = view_name
AND status != 'VALID';
BEGIN
/* First loop: just try to recompile */
FOR r IN c
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER VIEW ' || r.view_name || ' COMPILE';
i1 := i1 + 1;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( sqlerrm );
DBMS_OUTPUT.PUT_LINE( r.view_name );
i2 := i2 + 1;
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE( 'Compile: ' || TO_CHAR( i1 ) || ' OK; ' ||
TO_CHAR( i2 ) || ' not OK.' );
i1 := 0;
i2 := 0;
/* Second loop: recreate */
FOR r IN c
LOOP
BEGIN
EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ' || r.view_name || ' AS ' || r.text;
i1 := i1 + 1;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( sqlerrm );
DBMS_OUTPUT.PUT_LINE( r.view_name );
i2 := i2 + 1;
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE( 'Recreate: ' || TO_CHAR( i1 ) || ' OK; ' ||
TO_CHAR( i2 ) || ' not OK.' );
END;
/
|
Bookmark
