有没有人有示例数据迁移脚本（Oracle 10g 到 Oracle 10g，但架构不同）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1868129/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Does anyone have a sample data migration script (Oracle 10g to Oracle 10g, but different schemas)?
提问by Theresa
I am trying to write a data migration pl/sql script to transfer some of the data in one schema to a different schema on another server. The second database started as a subset of the original database, but we have modified the schema. So I can't just use the following for each table:
我正在尝试编写数据迁移 pl/sql 脚本，以将一个架构中的某些数据传输到另一台服务器上的不同架构。第二个数据库作为原始数据库的一个子集开始，但我们已经修改了架构。所以我不能只对每个表使用以下内容：
Insert into DB2.table_name select * from DB1.table_name2;
I have tried doing a search for sample scripts that show how to do this, but couldn't find anything.
我尝试搜索显示如何执行此操作的示例脚本，但找不到任何内容。
采纳答案by chris
You can create a database link.
您可以创建一个数据库链接。
Then, if you're trying to migrate from db1 to db2:
然后，如果您尝试从 db1 迁移到 db2：
Insert into table_name (f1, f2, etc) select (f1, f2, etc) from table_name2@DB2;
The select can be as complex or simple as needed.
选择可以根据需要复杂或简单。
回答by dcp
If the columns are different between DB1.table_name and DB2.table_name then you're going to have to specify a column list in the insert statement. Unfortunately, there's not really a "magic bullet" here.
如果 DB1.table_name 和 DB2.table_name 之间的列不同，那么您将不得不在插入语句中指定一个列列表。不幸的是，这里并没有真正的“灵丹妙药”。
With that said, to speed up the process you could write some PL/SQL to generate the insert statements, and then you could fix those by hand. Here's a sample PL/SQL code to do this. In this example, l_src_table would be your source table and l_target_table would be your target table. Obviously, you'll still have to manually fix the SQL statement this code generates, but this will at least generate a template SQL which should save you a lot of time.
话虽如此，为了加快进程，您可以编写一些 PL/SQL 来生成插入语句，然后您可以手动修复这些语句。这是执行此操作的示例 PL/SQL 代码。在此示例中， l_src_table 将是您的源表， l_target_table 将是您的目标表。显然，您仍然需要手动修复此代码生成的 SQL 语句，但这至少会生成一个模板 SQL，这将为您节省大量时间。
DECLARE
l_insert_stmt VARCHAR2(4000);
l_comma VARCHAR2(1) DEFAULT ' ';
l_src_table VARCHAR2(500) := 'TABLE1';
l_src_table_owner VARCHAR2(500) := 'DB1';
l_target_table VARCHAR2(500) := 'TABLE2';
l_target_table_owner VARCHAR2(500) := 'DB2';
BEGIN
l_insert_stmt := 'INSERT INTO ' || l_target_table || ' ( ';
FOR rec IN (SELECT column_name FROM all_tab_columns
WHERE TABLE_name = l_target_table AND owner = l_target_table_owner)
LOOP
l_insert_stmt := l_insert_stmt || l_comma || rec.column_name;
l_comma := ',';
END LOOP;
l_insert_stmt := l_insert_stmt || ' ) ';
l_insert_stmt := l_insert_stmt || ' SELECT ';
l_comma := ' ';
FOR rec IN (SELECT column_name FROM all_tab_columns
WHERE TABLE_name = l_src_table AND owner = l_src_table_owner)
LOOP
l_insert_stmt := l_insert_stmt || l_comma || rec.column_name;
l_comma := ',';
END LOOP;
l_insert_stmt := l_insert_stmt || ' FROM ' || l_src_table;
dbms_output.put_line(l_insert_stmt);
END;
回答by Dinesh Bhat
If you need to do this often enough,then another option is to use a schema synchronization tool. Toad, dbsolo and probably a few other tools can be used. It has saved me a lot of time and effort.
如果您需要经常这样做，那么另一种选择是使用模式同步工具。可以使用 Toad、dbsolo 和其他一些工具。它为我节省了很多时间和精力。
