-- 在A库中建 DBLINK -- DROP DATABASE LINK ORC_T; CREATE DATABASE LINK ORC_DBLINK CONNECT TO U_USER -- U_USER 远程连接的用户名 IDENTIFIED BY "U_PWD" USING 'ORC_T' -- U_PWD 远程连接的密码,ORC_T 上面配置的服务名CREATE TABLE T_TMP (TABLE_NAME VARCHAR2(50),COLUMN_NAME VARCHAR2(4000));DECLARE CURSOR C_TAB IS SELECT A.TABLE_NAME,COLUMN_NAME FROM USER_TABLES A JOIN USER_COL_COMMENTS B ON B.TABLE_NAME=A.TABLE_NAME WHERE EXISTS (SELECT 1 FROM USER_TABLES@ORC_DBLINK WHERE TABLE_NAME=A.TABLE_NAME) ORDER BY 1,2; V_TABLE_NAME VARCHAR2(50); V_COLUMN_NAME VARCHAR2(50); V_NUM VARCHAR2(50); BEGIN OPEN C_TAB; LOOP FETCH C_TAB INTO V_TABLE_NAME,V_COLUMN_NAME; EXIT WHEN C_TAB%NOTFOUND; SELECT COUNT(1) INTO V_NUM FROM T_TMP WHERE TABLE_NAME=V_TABLE_NAME; IF V_NUM=0 THEN INSERT INTO T_TMP(TABLE_NAME,COLUMN_NAME) VALUES (V_TABLE_NAME,V_COLUMN_NAME); COMMIT; ELSE UPDATE T_TMP SET COLUMN_NAME=COLUMN_NAME||','||V_COLUMN_NAME WHERE TABLE_NAME=V_TABLE_NAME; COMMIT; END IF; END LOOP; CLOSE C_TAB; END; -- 生成插入脚本 SELECT 'INSERT INTO '||TABLE_NAME||' ('||COLUMN_NAME||') SELECT '||COLUMN_NAME||' FROM '||TABLE_NAME||'@ORC_DBLINK' FROM T_TMP;
也可以用copy命令来跑,效率也不错给你个例子,在cmd窗口中执行就行了sqlplus username/pwd@DB_B set arraysize 100 set copycommit 100 copy from username/pwd@DB_A append tab_a using select * from tab_A; copy from username/pwd@DB_A append tab_B using select * from tab_B; --…………
如果是这样的话,可以通过DBLINK,直接进行数据插入(A -> B)。当然,要先区分出,表结构不同的 LIST
然后把A复制一份到B,再把差异在B中建立起来!
数据库的复制可以请你们的DBA帮忙。差异的部分生成脚本SQL,直接在B里面执行!
1) 查出需要插数的表名(A 与 B = count(字段) group by 表名 对比一下可得)
2) 建立DBLINK(A 或 B 皆可)
以 A 建DBLINK为例
在 B 中执行
insert into 表名(字段名)
select 字段名 from 表名@A.DBLINK;
ORC_T =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.0)(PORT = 1521)) ## 127.0.0.0 远程服务器IP
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL) ## ORCL 远程服务器 SID
)
)
-- 在A库中建 DBLINK
-- DROP DATABASE LINK ORC_T;
CREATE DATABASE
LINK ORC_DBLINK CONNECT TO U_USER -- U_USER 远程连接的用户名
IDENTIFIED BY "U_PWD" USING 'ORC_T' -- U_PWD 远程连接的密码,ORC_T 上面配置的服务名CREATE TABLE T_TMP (TABLE_NAME VARCHAR2(50),COLUMN_NAME VARCHAR2(4000));DECLARE
CURSOR C_TAB IS
SELECT A.TABLE_NAME,COLUMN_NAME
FROM USER_TABLES A
JOIN USER_COL_COMMENTS B ON B.TABLE_NAME=A.TABLE_NAME
WHERE EXISTS (SELECT 1 FROM USER_TABLES@ORC_DBLINK WHERE TABLE_NAME=A.TABLE_NAME)
ORDER BY 1,2;
V_TABLE_NAME VARCHAR2(50);
V_COLUMN_NAME VARCHAR2(50);
V_NUM VARCHAR2(50);
BEGIN
OPEN C_TAB;
LOOP
FETCH C_TAB INTO V_TABLE_NAME,V_COLUMN_NAME;
EXIT WHEN C_TAB%NOTFOUND;
SELECT COUNT(1) INTO V_NUM FROM T_TMP WHERE TABLE_NAME=V_TABLE_NAME;
IF V_NUM=0 THEN
INSERT INTO T_TMP(TABLE_NAME,COLUMN_NAME) VALUES (V_TABLE_NAME,V_COLUMN_NAME);
COMMIT;
ELSE
UPDATE T_TMP SET COLUMN_NAME=COLUMN_NAME||','||V_COLUMN_NAME WHERE TABLE_NAME=V_TABLE_NAME;
COMMIT;
END IF;
END LOOP;
CLOSE C_TAB;
END; -- 生成插入脚本
SELECT 'INSERT INTO '||TABLE_NAME||' ('||COLUMN_NAME||') SELECT '||COLUMN_NAME||' FROM '||TABLE_NAME||'@ORC_DBLINK'
FROM T_TMP;
set arraysize 100
set copycommit 100
copy from username/pwd@DB_A append tab_a using select * from tab_A;
copy from username/pwd@DB_A append tab_B using select * from tab_B;
--…………