查看dblink
select owner,object_name from dba_objects where object_type='DATABASE LINK';
or
select * from dba_db_links;
创建dblink
前提:
创建dblink的用户有对应的数据库权限
create public database link 或者create database link ;
grant create public database link,create database link to myAccount;
create public database link dblinkname connect to username identified by password
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = database_ip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =servicename)
)
)';
如果在create之后不加public,则创建的dblink就不是公共的,就只有创建者可以使用了.
- 如何确定数据库的servicename:
- sqlplus中使用
show parameter[s] service_names;
注意parameter和parameters都可以
或者
select name,value from v$parameter where name='service_names';
使用db link
例如,在本机数据库上创建了一个scott_rmthost的public dblink(使用远程主机的scott用户连接),则用sqlplus连接到本机数据库,执行select * from scott.emp@scott_rmthot即可以将远程数据库上的scott用户下的emp表中的数据获取到.
也可以在本地建一个同义词来指向scott.emp@scott_rmthost,这样取值就方便多了.
删除dblink
drop public database link dblinkname;
例:
create public database link chengwenit connect to chengweniterp
identified by "123456" using '(DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.102)(PORT = 1521)) )
(CONNECT_DATA = (SERVICE_NAME =CWIT) ) )';
Oracle循环语句参考示例
--备份错误数据
create table bms_batch_def_noprice0714 as(
select *
from bms_batch_def t
where nvl(t.unitprice, 0) = 0
and t.createfrom = 7);
--修改错误数据
begin
for c1 in (select *
from bms_batch_def t
where nvl(t.unitprice, 0) = 0
and t.createfrom = 7) loop
update bms_batch_def a
set (a.notaxsuprice, a.unitprice) =
(select b.notaxsuprice, b.unitprice
from bms_batch_def b
where b.batchid = c1.oldbatchid)
where a.batchid = c1.batchid;
end loop;
end;