-- 1.查询当前用户是否具有创建DATABASE LINK的权限(有返回数据则说明具有权限)
select * from user_sys_privs where privilege like upper('%DATABASE LINK%') AND USERNAME='POC_OPERATION';
-- 2.若没有权限,请使用具有dba权限的用户或系统用户sys赋予权限
grant create public database link to POC_OPERATION;
-- 3.获取已存在的dblink信息
select * from user_db_links;
select * from dba_db_links;
select owner,object_name from dba_objects where object_type='DATABASE LINK';
select * from ALL_DB_LINKS;
-- 4.创建dblink
-- Drop existing database link
drop database link WWlink;
-- Create database link
create database link WWlink--{dblink的名称}
connect to YSSAMS--{目标端数据库用户名}
identified by YSSAMS --{目标端数据库用户名对应密码}
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 10.10.x.x)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = ORCL)))';
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = 10.10.x.x)
(PORT = 1521)
)
)
(CONNECT_DATA =(SERVICE_NAME = ORCL))
)
-- 5.dblink使用方法(表名@dblink名) DML语句
SELECT id FROM TEST@WWlink;
insert into TEST@WWlink values('10900');
update TEST@WWlink set id='aaa' where id='10900';
delete from test@WWlink where id='aaa';
-- 6.dblink使用方法(表名@dblink名) DDL语句
declare
begin
dbms_utility.EXEC_DDL_STATEMENT@WWlink('
create table cs_thz_lv(
id number(10),
color varchar2(100),
is_can_water number(1)
)
');
commit;
end;
insert into cs_thz_lv@WWlink values('100','#FFFFFF','0');
declare
begin
dbms_utility.EXEC_DDL_STATEMENT@WWlink('alter table cs_thz_lv modify(color varchar2(300))');
commit;
end;
declare
begin
dbms_utility.EXEC_DDL_STATEMENT@WWlink('truncate table cs_thz_lv');
commit;
end;
declare
begin
dbms_utility.EXEC_DDL_STATEMENT@WWlink('drop table cs_thz_lv');
commit;
end;