签到成功

知道了

CNDBA社区CNDBA社区

Oracle 18c impdp时 直接将 分区表 转 非分区表

2018-08-29 11:44 3175 1 原创 Oracle 18c
作者： dave
本文链接：https://www.cndba.cn/cndba/dave/article/2975

1 背景说明

从Oracle 11g 开始，在使用数据泵导出导入分区表时，默认会按照原来的表进行操作，即原来是分区表导出的，那么导入的也是分区表。 如果想在导入的过程中，转成非分区表，那么需要impdp命令指定选项PARTITION_OPTIONS=merge。

https://www.cndba.cn/cndba/dave/article/2975
https://www.cndba.cn/cndba/dave/article/2975

关于该选项的详细说明可以直接参考官方手册，如下：https://www.cndba.cn/cndba/dave/article/2975https://www.cndba.cn/cndba/dave/article/2975

https://www.cndba.cn/cndba/dave/article/2975
https://www.cndba.cn/cndba/dave/article/2975
https://www.cndba.cn/cndba/dave/article/2975 

PARTITION_OPTIONS=[NONE | DEPARTITION | MERGE]
A value of none creates tables as they existed on the system from which the export operation was performed. You cannot use the none option or the merge option if the export was performed with the transportable method, along with a partition or subpartition filter. In such a case, you must use the departition option.
A value of departition promotes each partition or subpartition to a new individual table. The default name of the new table will be the concatenation of the table and partition name or the table and subpartition name, as appropriate.
A value of merge combines all partitions and subpartitions into one table.
Parallel processing during import of partitioned tables is subject to the following:
•    If a partitioned table is imported into an existing partitioned table, then Data Pump only processes one partition or subpartition at a time, regardless of any value that might be specified with the PARALLEL parameter.
•    If the table into which you are importing does not already exist and Data Pump has to create it, then the import runs in parallel up to the parallelism specified on the PARALLEL parameter when the import is started.

2 操作实例

2.1 创建测试分区表并导出表
[dave@www.cndba.cn ~]$ sqlplus system/oracle@dave

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Aug 26 18:13:46 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sun Aug 26 2018 18:13:27 -04:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL> CREATE TABLE Dave (ID, TIME) PARTITION BY RANGE (TIME) 
 (PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')), 
 PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')), 
 PARTITION P3 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')), 
 PARTITION P4 VALUES LESS THAN (MAXVALUE))
 AS SELECT ROWNUM, CREATED FROM DBA_OBJECTS; 
Table created.

SQL> create directory bak as '/u01/backup';
Directory created.

SQL> grant read,write on directory bak to system;
Grant succeeded.

[dave@www.cndba.cn u01]$ expdp system/oracle@dave  directory=BAK tables=Dave dumpfile=dave.dmp  logfile=dave.log

Export: Release 18.0.0.0.0 - Production on Sun Aug 26 18:13:57 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@dave directory=BAK tables=Dave dumpfile=dave.dmp logfile=dave.log 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SYSTEM"."DAVE":"P1"                            0 KB       0 rows
. . exported "SYSTEM"."DAVE":"P2"                            0 KB       0 rows
. . exported "SYSTEM"."DAVE":"P3"                            0 KB       0 rows
. . exported "SYSTEM"."DAVE":"P4"                        1.177 MB   72912 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /u01/backup/dave.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Sun Aug 26 18:14:28 2018 elapsed 0 00:00:28

[dave@www.cndba.cn u01]$
2.2 drop 表后直接导入

这里没有指定PARTITION_OPTIONS选项，即该值为None，此时导入后还是分区表：https://www.cndba.cn/cndba/dave/article/2975

SQL> drop table dave;
Table dropped.

[dave@www.cndba.cn u01]$ impdp system/oracle@dave  directory=BAK tables=Dave dumpfile=dave.dmp  logfile=dave.log

Import: Release 18.0.0.0.0 - Production on Sun Aug 26 18:18:02 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/********@dave directory=BAK tables=Dave dumpfile=dave.dmp logfile=dave.log 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SYSTEM"."DAVE":"P1"                            0 KB       0 rows
. . imported "SYSTEM"."DAVE":"P2"                            0 KB       0 rows
. . imported "SYSTEM"."DAVE":"P3"                            0 KB       0 rows
. . imported "SYSTEM"."DAVE":"P4"                        1.177 MB   72912 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Sun Aug 26 18:18:10 2018 elapsed 0 00:00:06

SQL> select table_name,T.PARTITIONED from user_tables t where table_name='DAVE';

TABLE_NAME               PARTIT
----------------------------  --------------------------------------
DAVE                     YES

SQL> select partition_name from user_tab_partitions where table_name='DAVE';
PARTITION_NAME
--------------------------------------------------------
P1
P2
P3
P4
2.3 drop 表后使用PARTITION_OPTIONS选项导入

此时导入后就变成了非分区表：https://www.cndba.cn/cndba/dave/article/2975

https://www.cndba.cn/cndba/dave/article/2975 

SQL> drop table dave;
Table dropped.

[dave@www.cndba.cn u01]$ impdp system/oracle@dave  directory=BAK tables=Dave dumpfile=dave.dmp  logfile=dave.log PARTITION_OPTIONS=MERGE 

Import: Release 18.0.0.0.0 - Production on Sun Aug 26 18:20:39 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/********@dave directory=BAK tables=Dave dumpfile=dave.dmp logfile=dave.log PARTITION_OPTIONS=MERGE 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SYSTEM"."DAVE":"P1"                            0 KB       0 rows
. . imported "SYSTEM"."DAVE":"P2"                            0 KB       0 rows
. . imported "SYSTEM"."DAVE":"P3"                            0 KB       0 rows
. . imported "SYSTEM"."DAVE":"P4"                        1.177 MB   72912 rows
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Sun Aug 26 18:20:48 2018 elapsed 0 00:00:06

SQL> select table_name,T.PARTITIONED from user_tables t where table_name='DAVE';

TABLE_NAME               PARTIT
----------------------------  --------------------------------------
DAVE                     NO

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的：当他回首往事的时候，他不会因为虚度年华而悔恨，也不会因为碌碌无为而羞耻；这样，在临死的时候，他就能够说：“我的整个生命和全部精力，都已经献给世界上最壮丽的事业....."

  • 2248
    原创
  • 3
    翻译
  • 557
    转载
  • 188
    评论
  • 访问：7224380次
  • 积分：4284
  • 等级：核心会员
  • 排名：第1名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ