create or replace PROCEDURE Synonym_Switch_DM
AUTHID CURRENT_USER
AS

SYNONYM_ORG VARCHAR2(30);
OWNER_ORG VARCHAR2(30);
NEW_SYNONYM VARCHAR2(30);
TABLE_NAME VARCHAR2 (30);
OWNER_NEW VARCHAR2 (30);

CURSOR C1 IS SELECT SYNONYM_NAME,TABLE_NAME,TABLE_OWNER FROM USER_SYNONYMS WHERE SYNONYM_NAME NOT LIKE '%_REP'AND SYNONYM_NAME LIKE 'DM_%';

BEGIN
OPEN C1;
LOOP
FETCH C1 INTO SYNONYM_ORG,TABLE_NAME,OWNER_ORG;
EXIT WHEN C1%NOTFOUND;
IF OWNER_ORG='SRS_DM_1'
THEN
  OWNER_NEW:='SRS_DM_2';
    EXECUTE IMMEDIATE 'DROP SYNONYM '|| SYNONYM_ORG;
 EXECUTE IMMEDIATE 'DROP SYNONYM '|| SYNONYM_ORG|| '_REP';
 
    EXECUTE IMMEDIATE 'CREATE SYNONYM ' || SYNONYM_ORG || ' FOR ' || OWNER_NEW || '.' || TABLE_NAME; 
    EXECUTE IMMEDIATE 'CREATE SYNONYM ' || SYNONYM_ORG || '_REP FOR ' || OWNER_ORG ||'.'|| TABLE_NAME;
 
ELSE IF
OWNER_ORG='SRS_DM_2'
THEN
  OWNER_NEW:='SRS_DM_1';
    EXECUTE IMMEDIATE 'DROP SYNONYM '|| SYNONYM_ORG;
 EXECUTE IMMEDIATE 'DROP SYNONYM '|| SYNONYM_ORG|| '_REP';
   
 EXECUTE IMMEDIATE 'CREATE SYNONYM ' || SYNONYM_ORG || ' FOR ' || OWNER_NEW ||'.'|| TABLE_NAME;
 EXECUTE IMMEDIATE 'CREATE SYNONYM ' || SYNONYM_ORG || '_REP FOR ' || OWNER_ORG ||'.'|| TABLE_NAME;
   
  END IF;
  END IF;
  END LOOP;
  CLOSE C1;

  COMMIT;
  END;