CODEWITHSUNDEEP
linuxoracle-databaseplsql
tale852150
tale852150

Reputation: 1628

Does anyone have Oracle PL/SQL code to rename all synonyms to another schema?

Oracle 11gR2 RHEL 6.4

Just exported a Oracle database user (schema) to another user using Data Pump. But all the synonyms still point to old (original) user in the new schema.

Does anyone have some code (e.g. PL/SQL) that can be run to rename all the synonyms in the new schema so that they are owned (pointing to) new schema?

Upvotes: 0

Views: 340

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50027

The following will probably need some tweaking, but you can start with

DECLARE
  strSynonyms_owner       VARCHAR2(4000) := 'SOME_USER';
  strSynonyms_new_owner   VARCHAR2(4000) := 'NEW_USER';
  strCommand              VARCHAR2(32767);
BEGIN
  FOR aSynonym IN (SELECT *
                     FROM ALL_SYNONYMS
                     WHERE OWNER = strSynonyms_owner)
  LOOP
    strCommand := 'CREATE OR REPLACE SYNONYM ' ||
                  aSynonym.OWNER || '.' || aSynonym.SYNONYM_NAME ||
                  ' FOR ' || strSynonyms_new_owner || '.' ||
                  aSynonym.TABLE_NAME;
    EXECUTE IMMEDIATE strCommand;
  END LOOP;
END;

Not tested on animals - you'll be first!

Share and enjoy.

Upvotes: 1

Related Questions