blocks|key|3974709|text|如果数据库模式中同时存在源表和目标表，则可以遵循以下方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3974710|a.)使用定义规则的元数据信息创建一个配置表，并为这些表创建自定义insert语句。(可选)|3974711|b.)使用将数据从一个表移动到另一个表的逻辑编写一个过程。|3974712|c.)使用循环中的DBMS_SCHEDULER编写一个过程，以调度调用步骤b)中定义的复制过程。调度程序将采用打开和关闭会话的情况。|3974713|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

If both the source and target tables are present in the Database schema then you can follow below approach:

a.) Create a configuration table with the metadata information to define the rules and create the custom insert statement for the tables. (Optional)

b.) Write a procedure with the logic to move the data from one table to another.

c.) Write a procedure with DBMS_SCHEDULER in a loop to schedule the call the copy procedure defined in step b). The scheduler will take case of opening and closing a session.

blocks|key|5443332|text|我认为你可以使用一些ETL工具。以不同的方式或表格迁移所有数据。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5443333|有些是像五角大楼的ETL工具。在这里你可以创建你的图表并运行它。一切都会好起来的。Pentaho也有一个通用的服务器工具。Pentaho+Data+Integration+or+ETL|offset|length|5443334|ETL工具将使您有机会提取数据，转换为您想要的任何内容，并加载到任何表或数据库中。|5443335|更新|style|BOLD|5443336|这是ETL工具。据我记忆所及，它适用于windows和linux，也许还有MAC。|5443337|ETL+community+tool|5443338|entityMap|0|LINK|mutability|MUTABLE|url|https://www.etltool.com/etl-vendors/pentaho-data-integration-kettle/|1|https://github.com/cwarden/kettle^0|0|1P|V|0|0|0|0|2|0|0|0|I|1|0^^$0|@$1|2|3|4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|10|8|@]|9|@$D|11|E|12|1|13]]|A|$]]|$1|F|3|G|5|6|7|14|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|15|8|@$D|16|E|17|J|K]]|9|@]|A|$]]|$1|L|3|M|5|6|7|18|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|19|8|@]|9|@$D|1A|E|1B|1|1C]]|A|$]]|$1|P|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]]|Q|$R|$5|S|T|U|A|$V|W]]|X|$5|S|T|U|A|$V|Y]]]]

I think that you can use some ETL tool. To get migrate all data in diferents ways or tables. 

Some like Pentaho ETL tool. Here you can create your diagram and run that. And all will be ok. Pentaho have a commuty server tool too. 
<a href="https://www.etltool.com/etl-vendors/pentaho-data-integration-kettle/" rel="nofollow noreferrer">Pentaho Data Integration or ETL</a>

The ETL tool will give you the chance to extract your data, transform in whatever you want and load in any table or database.

Update

This is the ETL tool. As I can remmember is aviable for windows and linux and maybe MAC.

<a href="https://github.com/cwarden/kettle" rel="nofollow noreferrer">ETL community tool</a>

blocks|key|1426038|text|The+fastest+way+to+do+this+will+probably+be+to+use+the+SQL*Plus+COPY+command。甲骨文上上下下发誓，他们将弃用COPY，但考虑到他们的客户广泛使用它，我认为他们会在相当长的一段时间内保留它。请注意，如果您希望同时复制多个表，则必须使用到数据库的多个连接，因为据我所知，在发出另一个复制命令之前，没有办法发出一个不等待复制操作完成的复制命令。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1426039|祝你好运。|1426040|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.oracle.com/database/121/SQPUG/apb.htm#SQPUG140^0|0|24|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

<a href="https://docs.oracle.com/database/121/SQPUG/apb.htm#SQPUG140" rel="nofollow noreferrer">The fastest way to do this will probably be to use the SQL*Plus COPY command</a>. Oracle swears up and down they're going to deprecate COPY but given that it's widely used by their customers I think they'll keep it around for quite a while. Note that if you want to copy multiple tables simultaneously you'll have to use multiple connections to the database, because as far as I'm aware there's no way to issue a COPY command which doesn't wait for the copy operation to finish before letting you issue another COPY command.

Best of luck.

blocks|key|5443420|text|在同一数据库中复制表的最快方法是使用Oracle并行性，而不是并发性。一次并行运行SQL，而不是同时运行多条语句。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5443421|并行和并发这两个词在Oracle中的含义略有不同。并行性是Enterprise+Edition提供的一个特性；数据库将创建并行会话来划分和监视工作。并发性是指您手动创建和监视同时执行任务的独立进程。|5443422|并行通常比并发容易得多。它通常只需要一个提示来启用它(尽管这些简单提示有许多复杂的分支)。并发性要求您创建和监视调度程序作业。|5443423|并行通常比并发快得多。在平均分配工作方面，Oracle可能比您做得更好。并行性将工作分解为多个粒度，并根据需要将它们分配给并行进程。它不是完美的，但通常是相当好的。将其与并发进行比较，在并发中，您负责查找处理速度相同的X表。找到X个大小相等的表比您想象的要困难得多，如果找错了会对性能造成破坏。|5443424|下面是一个过程，它将加载一组具有并行性的表。你可能想要验证每条语句是否获得了直接路径写入和并行性--你可能需要禁用外键，启用NOLOGGING，禁用触发器等。|5443425|create+or+replace+procedure+parallel_load
--Purpose:+Copy+data+from+source+tables+to+destination+tables.
--This+procedure+truncates+target+tables+-+there+is+no+turning+back!
(
++++p_source_schema+in+varchar2,
++++p_target_schema+in+varchar2,
++++p_tables+sys.odcivarchar2list,
++++p_dop+number
)+authid+current_user+is
++++v_sql+varchar2(32767);
begin
++++--Enable+parallel+DML+for+parallel+writing.
++++execute+immediate+'alter+session+enable+parallel+dml';

++++--Loop+through+the+tables.
++++for+i+in+1+..+p_tables.count+loop
++++++++begin
++++++++++++--Truncate+the+target+table.
++++++++++++execute+immediate+'truncate+table+'%7C%7Cp_target_schema%7C%7C'.'%7C%7Cp_tables(i);

++++++++++++--Create+parallel+insert.
++++++++++++v_sql+:=+
++++++++++++++++replace(replace(replace(replace(
++++++++++++++++++++q'[
++++++++++++++++++++++++insert+/*%2B+parallel(#DOP#)+*/+into+#TARGET_SCHEMA#.#TABLE_NAME#
++++++++++++++++++++++++select+*+from+#SOURCE_SCHEMA#.#TABLE_NAME#
++++++++++++++++++++]'
++++++++++++++++,+'#DOP#',+p_dop)
++++++++++++++++,+'#TARGET_SCHEMA#',+p_target_schema)
++++++++++++++++,+'#TABLE_NAME#',+p_tables(i))
++++++++++++++++,+'#SOURCE_SCHEMA#',+p_source_schema);

++++++++++++--Execute+the+parallel+insert.
++++++++++++execute+immediate+v_sql;

++++++++++++--Must+commit+if+direct-path+writes+are+used.
++++++++++++commit;
++++++++exception+when+others+then
++++++++++++raise_application_error(-20000,+'Error+with+this+SQL:+'%7C%7Cv_sql%7C%7Cchr(10)%7C%7C
++++++++++++++++sys.dbms_utility.format_error_stack%7C%7Csys.dbms_utility.format_error_backtrace);
++++++++end;
++++end+loop;
end;
/|code-block|syntax|javascript|5443426|下面是一个调用它的示例。(找到正确的DOP很棘手，可以尝试使用不同的数字，并查看V$PARAMETER中的cpu_count和%25parallel%25参数。)|offset|length|style|CODE|5443427|begin
++++parallel_load(
++++++++p_source_schema+=>+'source_schema',
++++++++p_target_schema+=>+'target_schema',
++++++++p_tables++++++++=>+sys.odcivarchar2list('test1',+'test2',+'test3'),
++++++++p_dop+++++++++++=>+8
++++);
end;
/|5443428|几乎可以肯定的是，您不想使用DBMS_SCHEDULER、ETL程序或COPY命令。这些解决方案将比并行复杂得多，速度也慢得多。(请不要冒犯那些发布这些答案的人。它们被张贴在批判性评论之前，解释说这些表在同一个数据库中。)|5443429|entityMap^0|0|0|0|0|0|0|14|B|1H|9|1R|A|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|12|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|13|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|14|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|15|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|16|8|@$Q|17|R|18|S|T]|$Q|19|R|1A|S|T]|$Q|1B|R|1C|S|T]]|9|@]|A|$]]|$1|U|3|V|5|L|7|1D|8|@]|9|@]|A|$M|N]]|$1|W|3|X|5|6|7|1E|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1F|8|@]|9|@]|A|$]]]|Z|$]]

The fastest way to copy tables within the same database is to use Oracle parallelism, not concurrency. Run the SQL one-at-a-time, in parallel, instead of running multiple statements at the same time.

The words parallelism and concurrency mean slightly different things in Oracle. Parallelism is a feature available with Enterprise Edition; the database will create parallel sessions to divide and monitor the work. Concurrency is when you manually create and monitor separate processes that do things at the same time.

Parallelism is usually much easier than concurrency. It often only requires a single hint to enable it (although there are many complex ramifications of those simple hints). Concurrency requires you to create and monitor scheduler jobs.

Parallelism is usually much faster than concurrency. Oracle can probably do a better job of evenly distributing work than you can. Parallelism breaks the work into granules and allocates them to parallel processes as needed. It's not perfect but it's usually pretty good. Compare that with concurrency, where you are responsible for finding X tables that process equally as fast. Finding X equally sized tables is more difficult than you might think, and getting it wrong is ruinous for performance.

Here's a procedure that will load a set of tables with parallelism. You'll probably want to verify that each statement is getting direct-path write and parallelism - you may need to disable foreign keys, enable NOLOGGING, disable triggers, etc. It can take hours to get this right.

<pre><code>create or replace procedure parallel_load
--Purpose: Copy data from source tables to destination tables.
--This procedure truncates target tables - there is no turning back!
(
 p_source_schema in varchar2,
 p_target_schema in varchar2,
 p_tables sys.odcivarchar2list,
 p_dop number
) authid current_user is
 v_sql varchar2(32767);
begin
 --Enable parallel DML for parallel writing.
 execute immediate 'alter session enable parallel dml';

 --Loop through the tables.
 for i in 1 .. p_tables.count loop
 begin
 --Truncate the target table.
 execute immediate 'truncate table '||p_target_schema||'.'||p_tables(i);

 --Create parallel insert.
 v_sql := 
 replace(replace(replace(replace(
 q'[
 insert /*+ parallel(#DOP#) */ into #TARGET_SCHEMA#.#TABLE_NAME#
 select * from #SOURCE_SCHEMA#.#TABLE_NAME#
 ]'
 , '#DOP#', p_dop)
 , '#TARGET_SCHEMA#', p_target_schema)
 , '#TABLE_NAME#', p_tables(i))
 , '#SOURCE_SCHEMA#', p_source_schema);

 --Execute the parallel insert.
 execute immediate v_sql;

 --Must commit if direct-path writes are used.
 commit;
 exception when others then
 raise_application_error(-20000, 'Error with this SQL: '||v_sql||chr(10)||
 sys.dbms_utility.format_error_stack||sys.dbms_utility.format_error_backtrace);
 end;
 end loop;
end;
/
</code></pre>

Here's an example of calling it. (Finding the right DOP is tricky, try experimenting with different numbers, and look at the <code>cpu_count</code> and <code>%parallel%</code> parameters in <code>V$PARAMETER</code>.)

<pre><code>begin
 parallel_load(
 p_source_schema =&gt; 'source_schema',
 p_target_schema =&gt; 'target_schema',
 p_tables =&gt; sys.odcivarchar2list('test1', 'test2', 'test3'),
 p_dop =&gt; 8
 );
end;
/
</code></pre>

You almost certainly do NOT want to use DBMS_SCHEDULER, an ETL program, or the COPY command. Those solutions will be much more complicated and slower than parallelism. (No offense to those who posted those answers. They were posted
 before the critical comment explaining that the tables are inside the same database.)

My Requirement : I have 72 oracle tables which have data inside it. Few tables have billion and million of records. I need to copy data from 72 tables to another 72 tables as it is. What is best way to do it ? I want to copy the data at one shot in 5-6 tables so that it should not go and load data one by one in-order to save the time. Not sure how should i achieve it in oracle.

Load data simultaneously in 10 table

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我的需求:我有72个oracle表，其中包含数据。很少有表有亿万条记录。我需要将数据从72个表复制到另外72个表中。做这件事最好的方法是什么？我想一次性复制5-6个表中的数据，这样就不会为了节省时间而一个接一个地加载数据。不确定我应该如何在oracle中实现它。

问同时加载10个表中的数据
EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问同时加载10个表中的数据EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问同时加载10个表中的数据
EN