导入导出 Oracle 分区表数据
--****************************
--?導入導出?Oracle?分區表數據
--****************************
???
????導入導入Oracle?分區表數據是Oracle DBA?經常完成的任務之一。分區表的導入導出同樣普通表的導入導出方式，只不過導入導出需要考
慮到分區的特殊性，如分區索引，將分區遷移到普通表，或使用原始分區表導入到新的分區表。下面將描述使用imp/exp，impdp/expdp導入導出
分區表數據。
?
????有關分區表的特性請參考：?
????????Oracle?分區表
????????SQL server 2005?切換分區表
????????SQL server 2005?基于已存在的表創建分區
?
????有關導入導出工具請參考：
????????數據泵EXPDP?導出工具的使用
????????數據泵IMPDP?導入工具的使用
?
????有關導入導出的官方文檔請參考：
????????Original Export and Import?????
?
一、分區級別的導入導出
????可以導出一個或多個分區，也可以導出所有分區(即整個表)。
????可以導入所有分區(即整個表)，一個或多個分區以及子分區。
????對于已經存在數據的表，使用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錯誤。
總結
以上是生活随笔為你收集整理的导入导出 Oracle 分区表数据的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Automatic Diagnostic
- 下一篇： Nagios 请检查HTTP服务器关于该