Forum Stats

  • 3,853,938 Users
  • 2,264,295 Discussions
  • 7,905,512 Comments

Discussions

issue while revoking access

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;

Tagged:
This discussion has been closed.