Oracle把指定数据库中拇指定表的数据导入到当前数据库
1、创建数据库连接，假设数据库连接名为uat，此示例用于从uat数据库中把指定表的数据
导入
到当前数据库中。
2、假设需要到入数据的表为 tnt_report_theme，执行以下脚本：
DECLARE
v_sql VARCHAR2(2000) := NULL;
BEGIN
------------------------------------------
-- 禁用约束
------------------------------------------
FOR tc IN
(SELECT table_name,
constraint_name
FROM user_constraints
WHERE lower(table_name) IN ('tnt_report_theme')
AND constraint_type = 'R'
)
LOOP
v_sql := 'alter table ' || tc.table_name || ' disable constraints ' || tc.constraint_Name;
EXECUTE immediate v_sql ;
END LOOP;
------------------------------------------
-- 导入数据
------------------------------------------
FOR tbl IN
(SELECT table_name
FROM user_tables@uat
WHERE lower(table_name) IN ('tnt_report_theme')
)
LOOP
----------------------------------------
-- 删除数据
----------------------------------------
v_sql := 'delete from ' || tbl.table_name;
EXECUTE immediate v_sql;
COMMIT;
----------------------------------------
-- 导入数据
----------------------------------------
v_sql := 'insert into ' || tbl.table_name || ' select * from ' || tbl.table_name || '@uat';
EXECUTE immediate v_sql ;
COMMIT;
END LOOP;
------------------------------------------
-- 重启约束
------------------------------------------
FOR rtc IN
(SELECT table_name,
constraint_name
FROM user_constraints
WHERE lower(table_name) IN ('tnt_report_theme')
AND constraint_type = 'R'
)
LOOP
v_sql := 'alter table ' || rtc.table_name || ' enable constraints ' || rtc.constraint_name;
EXECUTE immediate v_sql;
END LOOP;
END;