Oracle使用技巧（一）：跨库复制数据

Oracle使用技巧（一）：跨库复制数据

1、查看当前dblink

select * from dba_db_links;

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;