4.导入导出 Oracle 分区表数据
转载自:https://blog.csdn.net/leshami/article/details/6246738
导入导入Oracle 分区表数据是Oracle DBA 经常完成的任务之一。
分区表的导入导出同样普通表的导入导出方式,只不过导入导出需要考虑到分区的特殊性,如分区索引,将分区迁移到普通表,或使用原始分区表导入到新的分区表。
下面将描述使用imp/exp,impdp/expdp导入导出分区表数据。
一、分区级别的导入导出
可以导出一个或多个分区,也可以导出所有分区(即整个表)。 可以导入所有分区(即整个表),一个或多个分区以及子分区。 对于已经存在数据的表,使用imp导入时需要使用参数IGNORE=y,而使用impdp,加table_exists_action=append | replace 参数。
二、创建演示环境
--1.查看当前数据库的版本 SQL> select * from v$version where rownum < 2; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production --2.创建一个分区表 SQL> alter session set nls_date_format='yyyy-mm-dd'; SQL> CREATE TABLE tb_pt ( sal_date DATE NOT NULL, sal_id NUMBER NOT NULL, sal_row NUMBER(12) NOT NULL) partition by range(sal_date) ( partition sal_11 values less than(to_date('2012-01-01','YYYY-MM-DD')) , partition sal_12 values less than(to_date('2013-01-01','YYYY-MM-DD')) , partition sal_13 values less than(to_date('2014-01-01','YYYY-MM-DD')) , partition sal_14 values less than(to_date('2015-01-01','YYYY-MM-DD')) , partition sal_15 values less than(to_date('2016-01-01','YYYY-MM-DD')) , partition sal_16 values less than(to_date('2017-01-01','YYYY-MM-DD')) , partition sal_other values less than (maxvalue) ) nologging; --3.创建一个唯一索引 CREATE UNIQUE INDEX tb_pt_ind1 ON tb_pt(sal_date) nologging; --4.为分区表生成数据 SQL> INSERT INTO tb_pt SELECT TRUNC(SYSDATE)+ROWNUM, dbms_random.random, ROWNUM FROM dual CONNECT BY LEVEL<=5000; SQL> commit; SQL> select count(1) from tb_pt partition(sal_11); COUNT(1) ---------- 300 SQL> select count(1) from tb_pt partition(sal_other); COUNT(1) ---------- 2873 SQL> select * from tb_pt partition(sal_12) where rownum < 3; SAL_DATE SAL_ID SAL_ROW --------- ---------- ---------- 01-JAN-12 -1.356E+09 301 02-JAN-12 -761530183 302
三、使用exp/imp导出导入分区表数据
--1.导出整个分区表 [oracle@node1 ~]$ exp scott/tiger file='/u02/dmp/tb_pt.dmp' log='/u02/dmp/tb_pt.log' tables=tb_pt Export: Release 11.2.0.1.0 - Production on Wed Mar 9 13:52:18 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing o Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses ZHS16GBK character set (possible charset conversion) About to export specified tables via Conventional Path ... . . exporting table TB_PT . . exporting partition SAL_11 300 rows exported . . exporting partition SAL_12 366 rows exported . . exporting partition SAL_13 365 rows exported . . exporting partition SAL_14 365 rows exported . . exporting partition SAL_15 365 rows exported . . exporting partition SAL_16 366 rows exported . . exporting partition SAL_OTHER 2873 rows exported EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. Export terminated successfully with warnings. [oracle@node1 ~]$ oerr exp 00091 00091, 00000, "Exporting questionable statistics." // *Cause: Export was able export statistics, but the statistics may not be // usuable. The statistics are questionable because one or more of // the following happened during export: a row error occurred, client // character set or NCHARSET does not match with the server, a query // clause was specified on export, only certain partitions or // subpartitions were exported, or a fatal error occurred while // processing a table. // *Action: To export non-questionable statistics, change the client character // set or NCHARSET to match the server, export with no query clause, // export complete tables. If desired, import parameters can be // supplied so that only non-questionable statistics will be imported, // and all questionable statistics will be recalculated. 在上面的导出中出现了错误提示,即EXP-00091,该错误表明exp工具所在的环境变量中的NLS_LANG与DB中的NLS_CHARACTERSET不一致 尽管该错误对最终的数据并无影响,但调整该参数来避免异常还是有必要的。因此需要将其设置为一致即可解决上述的错误提示。 SQL> select userenv('language') from dual; USERENV('LANGUAGE') ---------------------------------------------------- AMERICAN_AMERICA.ZHS16GBK [oracle@node1 ~]$ export NLS_LANG='AMERICAN_AMERICA.ZHS16GBK' 经过上述设置之后再次导出正常,过程略。 --2.导出单个分区 [oracle@node1 ~]$ exp scott/tiger file='/u02/dmp/tb_pt_sal_16.dmp' log='/u02/dmp/tb_pt_sal_16.log' tables=tb_pt:sal_16 Export: Release 11.2.0.1.0 - Production on Wed Mar 9 13:52:38 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing o Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set About to export specified tables via Conventional Path ... . . exporting table TB_PT . . exporting partition SAL_16 366 rows exported EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. Export terminated successfully with warnings 在上面的导出过程中再次出现了统计信息错误的情况,因此采取了对该对象收集统计信息,但并不能解决该错误,但在exp命令行中增 加statistics=none即可,如下: [oracle@node1 ~]$ exp scott/tiger file='/u02/dmp/tb_pt_sal_16.dmp' log='/u02/dmp/tb_pt_sal_16.log' / > tables=tb_pt:sal_16 statistics=none 如果要导出多个分区,则在tables参数中增加分区数。如:tables=(tb_pt:sal_15,tb_pt:sal_16) --3.使用imp工具生成创建分区表的DDL语句 [oracle@node1 ~]$ imp scott/tiger tables=tb_pt indexfile='/u02/dmp/cr_tb_pt.sql' / > file='/u02/dmp/tb_pt.dmp' ignore=y Export: Release 11.2.0.1.0 - Production on Wed Mar 9 13:54:38 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing o Export file created by EXPORT:V11.02.00 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses ZHS16GBK character set (possible charset conversion) . . skipping partition "TB_PT":"SAL_11" . . skipping partition "TB_PT":"SAL_12" . . skipping partition "TB_PT":"SAL_13" . . skipping partition "TB_PT":"SAL_14" . . skipping partition "TB_PT":"SAL_15" . . skipping partition "TB_PT":"SAL_16" . . skipping partition "TB_PT":"SAL_OTHER" Import terminated successfully without warnings. --4.导入单个分区(使用先前备份的单个分区导入文件) SQL> alter table tb_pt truncate partition sal_16; --导入前先将分区实现truncate Table truncated. SQL> select count(1) from tb_pt partition(sal_16); COUNT(1) ---------- 0 SQL> ho imp scott/tiger tables=tb_pt:sal_16 file='/u02/dmp/tb_pt_sal_16.dmp' ignore=y Export: Release 11.2.0.1.0 - Production on Wed Mar 9 13:55:39 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing o Export file created by EXPORT:V11.02.00 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses ZHS16GBK character set (possible charset conversion) . importing SCOTT's objects into SCOTT . importing SCOTT's objects into SCOTT . . importing partition "TB_PT":"SAL_16" IMP-00058: ORACLE error 1502 encountered ORA-01502: index 'SCOTT.TB_PT_IND1' or partition of such index is in unusable state Import terminated successfully with warnings. 收到了ORA-01502错误,下面查看索引的状态,并对其重建索引后再执行导入 SQL> select index_name ,status from dba_indexes where table_name='TB_PT'; --查看索引的状态 INDEX_NAME STATUS ------------------------------ -------- TB_PT_IND1 UNUSABLE SQL> alter index TB_PT_IND1 rebuild online; --重建索引 Index altered. SQL> ho imp scott/tiger tables=tb_pt:sal_16 file='/u02/dmp/tb_pt_sal_16.dmp' ignore=y --再次导入成功 Export: Release 11.2.0.1.0 - Production on Wed Mar 9 13:56:15 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing o Export file created by EXPORT:V11.02.00 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses ZHS16GBK character set (possible charset conversion) . importing SCOTT's objects into SCOTT . importing SCOTT's objects into SCOTT . . importing partition "TB_PT":"SAL_16" 366 rows imported Import terminated successfully without warnings. SQL> select count(*) from tb_pt partition(sal_16); COUNT(*) ---------- 366 --5.导入整个表 SQL> truncate table tb_pt; --首先truncate 整个表 Table truncated. SQL> ho imp scott/tiger tables=tb_pt file='/u02/dmp/tb_pt.dmp' ignore=y indexes=y Export: Release 11.2.0.1.0 - Production on Wed Mar 9 13:57:10 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing o Export file created by EXPORT:V11.02.00 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses ZHS16GBK character set (possible charset conversion) . importing SCOTT's objects into SCOTT . importing SCOTT's objects into SCOTT . . importing partition "TB_PT":"SAL_11" 298 rows imported . . importing partition "TB_PT":"SAL_12" 366 rows imported . . importing partition "TB_PT":"SAL_13" 365 rows imported . . importing partition "TB_PT":"SAL_14" 365 rows imported . . importing partition "TB_PT":"SAL_15" 365 rows imported . . importing partition "TB_PT":"SAL_16" 366 rows imported . . importing partition "TB_PT":"SAL_OTHER" 2875 rows imported Import terminated successfully without warnings. SQL> select count(1) from tb_pt partition(sal_other); COUNT(1) ---------- 2875
四、使用expdp/impdb来实现分区表的导入导出
--1.查看导入导出的目录设置 SQL> select directory_name,directory_path from dba_directories where directory_name='DMP'; DIRECTORY_NAME DIRECTORY_PATH ------------------------------ ------------------------------------------------------------ DMP /u02/dmp --2.为分区表创建一个本地索引 create index tb_pt_local_idx on tb_pt(sal_id) local (partition local1, partition local2, partition local3, partition local4, partition local5, partition local6, partition local7); --3.导出整个表 [oracle@node1 ~]$ expdp scott/tiger directory=dmp dumpfile=tb_pt.dmp logfile=tb_pb.log tables=tb_pt parallel=3 Export: Release 11.2.0.1.0 - Production on Wed Mar 9 14:04:28 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=dmp dumpfile=tb_pt.dmp logfile=tb_pb.log tables= tb_pt parallel=3 Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 512 KB . . exported "SCOTT"."TB_PT":"SAL_OTHER" 71.63 KB 2875 rows . . exported "SCOTT"."TB_PT":"SAL_11" 12.54 KB 298 rows . . exported "SCOTT"."TB_PT":"SAL_12" 14.22 KB 366 rows . . exported "SCOTT"."TB_PT":"SAL_13" 14.18 KB 365 rows . . exported "SCOTT"."TB_PT":"SAL_14" 14.18 KB 365 rows . . exported "SCOTT"."TB_PT":"SAL_15" 14.19 KB 365 rows . . exported "SCOTT"."TB_PT":"SAL_16" 14.23 KB 366 rows Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: /u02/dmp/tb_pt.dmp Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:04:51 --4.导出多个分区 [oracle@node1 ~]$ expdp scott/tiger directory=dmp dumpfile=tb_pts.dmp logfile=tb_pt.log / > tables=(tb_pt:sal_16,tb_pt:sal_other) parallel=2 Export: Release 11.2.0.1.0 - Production on Wed Mar 9 14:08:06 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=dmp dumpfile=tb_pts.dmp logfile=tb_pt.log tables=(tb_pt:sal_16,tb_pt:sal_other) parallel=2 --*/ Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 192 KB . . exported "SCOTT"."TB_PT":"SAL_OTHER" 71.63 KB 2875 rows . . exported "SCOTT"."TB_PT":"SAL_16" 14.23 KB 366 rows Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: /u02/dmp/tb_pts.dmp Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:08:17 --5.截断分区sal_other SQL> alter table tb_pt truncate partition(sal_other); Table truncated. SQL> select count(*) from tb_pt partition(sal_other); COUNT(*) ---------- 0 SQL> select index_name,status,partitioned from dba_indexes where table_name='TB_PT'; --查看索引的状态, TB_PT_IND1不可用 INDEX_NAME STATUS PAR ------------------------------ -------- --- TB_PT_IND1 UNUSABLE NO TB_PT_LOCAL_IDX N/A YES SQL> select index_name ,partition_name, status from dba_ind_partitions where index_owner='SCOTT'; INDEX_NAME PARTITION_NAME STATUS ------------------------------ ------------------------------ -------- TB_PT_LOCAL_IDX LOCAL1 USABLE TB_PT_LOCAL_IDX LOCAL2 USABLE TB_PT_LOCAL_IDX LOCAL3 USABLE TB_PT_LOCAL_IDX LOCAL4 USABLE TB_PT_LOCAL_IDX LOCAL5 USABLE TB_PT_LOCAL_IDX LOCAL6 USABLE TB_PT_LOCAL_IDX LOCAL7 USABLE --6.导入单个分区 [oracle@node1 ~]$ impdp scott/tiger directory=dmp dumpfile=tb_pts.dmp logfile=tb_pt_imp.log / > tables=tb_pt:sal_other skip_unusable_indexes=y table_exists_action=replace Import: Release 11.2.0.1.0 - Production on Wed Mar 9 14:13:28 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=dmp dumpfile=tb_pts.dmp logfile=tb_pt_imp.log tables=tb_pt:sal_other skip_unusable_indexes=y table_exists_action=replace --*/ Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "SCOTT"."TB_PT":"SAL_OTHER" 71.63 KB 2875 rows Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 14:13:33 SQL> select index_name,status,partitioned from dba_indexes where table_name='TB_PT'; INDEX_NAME STATUS PAR ------------------------------ -------- --- TB_PT_IND1 VALID NO TB_PT_LOCAL_IDX N/A YES 从上面的导入情况可以看出,尽管执行了truncate partition,然而使用impdp导入工具,并且使用参数table_exists_action=replace 可以避免使用imp导入时唯一和主键索引需要重建的问题。注意,如果没有使用table_exists_action=replace参数,将会收到ORA-39151 错误,如下 ORA-39151: Table "SCOTT"."TB_PT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip --7.导入整个表 [oracle@node1 ~]$ impdp scott/tiger directory=dmp dumpfile=tb_pt.dmp logfile=tb_pt_fullimp.log / > tables=tb_pt skip_unusable_indexes=y table_exists_action=replace Import: Release 11.2.0.1.0 - Production on Wed Mar 9 14:17:35 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=dmp dumpfile=tb_pt.dmp logfile=tb_pt_fullimp.log tables=tb_pt skip_unusable_indexes=y table_exists_action=replace --*/ Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "SCOTT"."TB_PT":"SAL_OTHER" 71.63 KB 2875 rows . . imported "SCOTT"."TB_PT":"SAL_11" 12.54 KB 298 rows . . imported "SCOTT"."TB_PT":"SAL_12" 14.22 KB 366 rows . . imported "SCOTT"."TB_PT":"SAL_13" 14.18 KB 365 rows . . imported "SCOTT"."TB_PT":"SAL_14" 14.18 KB 365 rows . . imported "SCOTT"."TB_PT":"SAL_15" 14.19 KB 365 rows . . imported "SCOTT"."TB_PT":"SAL_16" 14.23 KB 366 rows Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 14:17:40
五、参数skip_unusable_indexes的作用
SQL> show parameter skip NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ skip_unusable_indexes boolean TRUE 该参数允许在导入分区数据时延迟对索引的处理,即先将数据导入,导入后再来重建索引分区。 在命令行导入中未指定导入参数skip_unusable_indexes时,则对于索引相关的问题,根据数据库初始化参数的值来确定。 在命令行导入中如果指定了参数skip_unusable_indexes时,则该参数的值优先于数据库初始化参数的设定值。 skip_unusable_indexes=y对unique index不起作用,因为此时的unique index扮演者constraint的作用,所以在insert数据时index必须被更新。 对于单个分区导入时PK,unique index的处理,必须先重建索引然后进行导入。 使用impdp数据泵实现导入并使用参数table_exists_action=replace可以解决上述问题,即ORA-01502错误