oracle批量修改同义词,ORACLE数据库 批量创建同义词

1.同库不同用户之间建立同义词.使用同义词创建函数实现.

create or replace function CreateSynonymForUser

(SourceUser in varchar2,TarUser in varchar2) return integer is

Result integer;

iCount integer(8);

sTableName varchar2(100);

sSql varchar2(300);

cursor c1(p1 varchar2)

is select TABLE_NAME from dba_tables where OWNER=p1 and TABLESPACE_NAME='USERS';

begin

open c1(SourceUser);

loop

sSql:= 'create or replace synonym ';

fetch c1 into sTableName;

exit when c1%notfound;

sSql:= sSql || TarUser || '.' || sTableName || ' for ' || SourceUser || '.' || sTableName;

EXECUTE IMMEDIATE sSql;

iCount:=iCount + 1;

Result:=iCount;

end loop;

return(Result);

end CreateSynonymForUser;

2.不同库用户间创建同义词.

(1)先从源库中导出需要创建同义词的表信息.使用SQL语句