oracle dblink

oracle dblink

《PS：留下小赞赞，比心比心》

--查询已有的dblink
select * from dba_objects where  object_type like '%LINK%'
select * from dba_db_links;

--删除dblink
drop public database link dblinkname;

-- 查看wangyong用户是否具备创建database link 权限
select * from user_sys_privs where privilege like upper('%DATABASE LINK%') AND USERNAME='WANGYONG';

-- 给wangyong用户授予创建dblink的权限
grant create public database link to wangyong;

-- 注意一点，如果密码是数字开头，用“”括起来
create public database link TESTLINK2 connect to WANGYONG identified by "123456" USING 'ORCL21'

-- 创建同义词
create synonym TESTSYNONYM FOR company@TESTLINK1;

-- 查询ORCL2中WANGYONG用户的表COMPANY

SELECT * FROM TESTSYNONYM order by id


create database link dblink1 connect to xzzc identified by xzzc using '192.168.10.0:1521/shitan';


create table gzbb2018_type as select * from gzbb2018_type@link1；

grant select on userA(真实数据实例).BA_USER_INFO  to userB(中间库);
create user B identified by B;
grant dba to B;
grant connect to B;

--select * from global_name;

create synonym TESTSYNONYM FOR company@TESTLINK1;  --同义词