--****************************
-- 導入導出 Oracle 分區表資料
導入導入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);
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.
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
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
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
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);
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
. 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
. . importing partition "TB_PT":"SAL_16" 366 rows imported
SQL> select count(*) from tb_pt partition(sal_16);
COUNT(*)
366
5.導入整個表
SQL> truncate table tb_pt; --首先truncate 整個表
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
. . 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_OTHER" 2875 rows imported
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
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
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 --*/
Total estimation using BLOCKS method: 192 KB
/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);
SQL> select count(*) from tb_pt partition(sal_other);
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
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 --*/
. . imported "SCOTT"."TB_PT":"SAL_OTHER" 71.63 KB 2875 rows
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';
TB_PT_IND1 VALID NO
從上面的導入情況可以看出，盡管執行了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
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 --*/
. . 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
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錯誤。
六、更多參考
有關性能優化請參考
<a href="http://blog.csdn.net/robinson_0612/archive/2011/02/25/6208268.aspx">共享池的調整與優化(Shared pool Tuning)</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2011/02/25/6208594.aspx">Buffer cache 的調整與優化(一)</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2011/03/06/6226767.aspx">Oracle 表緩存(caching table)的使用</a>
有關閃回特性請參考
<a href="http://blog.csdn.net/robinson_0612/archive/2010/12/29/6105327.aspx">Oracle 閃回特性(FLASHBACK DROP & RECYCLEBIN)</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2011/01/02/6112607.aspx">Oracle 閃回特性(Flashback Query、Flashback Table)</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2011/01/02/6112981.aspx">Oracle 閃回特性(Flashback Version、Flashback Transaction)</a>
有關基于使用者管理的備份和備份恢複的概念請參考：
有關RMAN的恢複與管理請參考：
有關Oracle體系結構請參考：
<a href="http://blog.csdn.net/robinson_0612/archive/2010/04/29/5542983.aspx">Oracle 資料庫執行個體啟動關閉過程</a>
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)