如何将分区从一个表导入到 Oracle 中的另一个表中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2501160/
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
How can I import a partition from one table into another in Oracle?
提问by DaTroop
I would like to know if the following steps are possible and how fast this is:
我想知道以下步骤是否可行以及速度有多快：
- Create a partition named part1 in Table A
- Drop partition part1 in Table B
- Import the Table A partition part1 into Table B
- 在表A中创建一个名为part1的分区
- 删除表 B 中的分区 part1
- 将表A分区part1导入表B
Can you provide me with an example if it is possible indeed? Or any resources I can look at?
如果确实可能，你能给我举个例子吗？或者有什么资源我可以看看？
Note that the tables would have the exact same structure.
请注意，这些表将具有完全相同的结构。
回答by Vincent Malgrat
You can do something similar with the ALTER TABLE ... EXCHANGE PARTITIONcommand. This would exchange a single partition with a table that has the same structure.
您可以使用该ALTER TABLE ... EXCHANGE PARTITION命令执行类似操作。这会将单个分区与具有相同结构的表交换。
A little example:
一个小例子：
/* Partitionned Table Creation */
SQL> CREATE TABLE table_a (
2 ID NUMBER PRIMARY KEY,
3 DATA VARCHAR2(200)
4 )
5 PARTITION BY RANGE (ID) (
6 PARTITION part100 VALUES LESS THAN (100),
7 PARTITION part200 VALUES LESS THAN (200)
8 );
Table created
/* Swap table creation */
SQL> CREATE TABLE swap_table (
2 ID NUMBER PRIMARY KEY,
3 DATA VARCHAR2(200)
4 );
Table created
SQL> INSERT INTO swap_table SELECT ROWNUM, 'a' FROM dual CONNECT BY LEVEL <= 99;
99 rows inserted
SQL> select count(*) from table_a partition (part100);
COUNT(*)
----------
0
This will exchange the partition part100with the transition table swap_table:
这将part100与转换表交换分区swap_table：
SQL> ALTER TABLE table_a EXCHANGE PARTITION part100 WITH TABLE swap_table;
Table altered
SQL> select count(*) from table_a partition (part100);
COUNT(*)
----------
99
