ALTER SESSION SET CURRENT_SCHEMA=<SCHEMA_NAME>

CREATE OR REPLACE PACKAGE test1
IS
   PROCEDURE run_me;
END;

CREATE OR REPLACE PACKAGE BODY test1
IS
   PROCEDURE run_me
   IS
      val   NUMBER;
   BEGIN
      SELECT COUNT (*)
        INTO val
        FROM nagel.insp_violation;
   END;
END;

CREATE OR REPLACE PROCEDURE test_valid
AUTHID CURRENT_USER
IS
BEGIN
   EXECUTE IMMEDIATE 'drop synonym nagel.insp_violation';

   EXECUTE IMMEDIATE 'create synonym nagel.insp_violation for mcmis080822.insp_violation';

   EXECUTE IMMEDIATE 'alter package nagel.test1 compile body';

   test1.run_me;
END;

BEGIN
   test_valid;
END;

create or replace view ALL_SCHEMAS_TABLE1
select 'SCHEMA_JAN' as schema_name, a.*
from SCHEMA_JAN.TABLE1 a
union all
select 'SCHEMA_FEB' as schema_name, a.*
from SCHEMA_FEB.TABLE1 a;