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)先从源库中导出需要创建同义词的表信