Oracle中DBLINK的使用

-- 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;