Hi All,
i am executing a script (given below):
this i snot getting completed but hangs when it starts revoking access from packages. If i check the session browser in TOAD. it says "library cache pin' and waiting.
after log time it come out with exception "ORA-04021: timeout occurred while waiting to lock object ".
This issue is when i run this via dynamic sql.
if i revoke access of each object saparatly, it goes fine.
Please suggest what is wrong :
begin
FOR x IN (SELECT * FROM user_tables)
LOOP
EXECUTE IMMEDIATE 'REVOKE SELECT ON ' || x.table_name || ' FROM oasis_vac_access';
END LOOP;
FOR x IN (SELECT * FROM user_views where view_name)
LOOP
EXECUTE IMMEDIATE 'REVOKE SELECT ON ' || x.view_name || ' FROM oasis_vac_access';
END LOOP;
EXECUTE IMMEDIATE 'REVOKE EXECUTE ON pkg_common FROM oasis_vac_access';
EXECUTE IMMEDIATE 'REVOKE EXECUTE ON pkg_derived_function FROM oasis_vac_access';
EXECUTE IMMEDIATE 'REVOKE EXECUTE ON pkg_query_wizard FROM oasis_vac_access';
EXECUTE IMMEDIATE 'REVOKE EXECUTE ON pkg_custom_reports FROM oasis_vac_access';
EXECUTE IMMEDIATE 'REVOKE EXECUTE ON pkg_hitlist_wizard FROM oasis_vac_access';
end;