有些系统前期数据库设计不够规范,后期的时候有需求需要将属于指定用户的公共同义词修改为该用户的私有同义词,使用以下存储过程即可实现,使用时修改TABLE_OWNER条件为要修改的模式名即可
DECLARE
V_OWNER VARCHAR2(100);
V_SYMNAME VARCHAR2(100);
SQL_SELECT VARCHAR2(500);
SQL_PUB_SYM TEXT;
SQL_REPLACE TEXT;
SQL_PRI_SYM TEXT;
BEGIN
FOR REC IN (SELECT SYNONYM_NAME,TABLE_OWNER FROM ALL_SYNONYMS WHERE OWNER='PUBLIC' AND TABLE_OWNER='HZQ' ORDER BY 1, 2)
LOOP
SELECT REC.TABLE_OWNER,REC.SYNONYM_NAME INTO V_OWNER,V_SYMNAME FROM DUAL;
SQL_SELECT := 'SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE => ''SYNONYM'',NAME => ''' || V_SYMNAME || ''',SCHNAME => ''' || V_OWNER || ''');';
EXECUTE IMMEDIATE SQL_SELECT INTO SQL_PUB_SYM;
SQL_REPLACE := 'SELECT REPLACE('''||SQL_PUB_SYM||''',''CREATE OR REPLACE PUBLIC SYNONYM '',''CREATE OR REPLACE SYNONYM "'||V_OWNER||'".'');';
EXECUTE IMMEDIATE SQL_REPLACE INTO SQL_PRI_SYM;
EXECUTE IMMEDIATE SQL_PRI_SYM;
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM IF EXISTS "'||V_SYMNAME||'";';
END LOOP;
END;
欢迎访问个人博客Jackin's Blog