Oracle使用技巧(一):跨库复制数据
1、查看当前dblink
select * from dba_db_links;
1
2、创建dblink
create public database link 【link名称】 connect to 【被连接库的用户名】 identified by 【被连接库的密码】 using
'(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 【ip】)(PORT = 【端口】))
)
(CONNECT_DATA =
(SERVICE_NAME = 【实例名】)
)
)';
3、查看表锁
select object_name,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s
where l.object_id = o.object_id and l.session_id=s.sid;
4、解锁表(插入数据卡主时用)
alter system kill session'SID,SERIAL#';
5、字段准备
select CONCAT(COLUMN_NAME,',') from user_tab_columns where table_name = '表名'
6、dblink使用
insert into TableName@dblink (a,b,c) select a,b,c from TableName
7、查看是否存在
SELECT "serviceName"."ISEQ$$10000".nextval from dual;
8、创建序列
create sequence "serviceName"."ISEQ$$10000"
increment by 1
start with 10000 -- 当前表最大ID+1
maxvalue 99999999999999999;