Oracle分区表数据导入导出.docx
《Oracle分区表数据导入导出.docx》由会员分享，可在线阅读，更多相关《Oracle分区表数据导入导出.docx（16页珍藏版）》请在冰豆网上搜索。
Oracle分区表数据导入导出
导入导出Oracle分区表数据
--导入导出Oracle分区表数据
--****************************
导入导出Oracle分区表数据是OracleDBA经常完成的任务之一。
分区表的导入导出同样普通表的导入导出方式，只不过导入导出需要考虑到分区的特殊性，如分区索引，将分区迁移到普通表，或使用原始分区表导入到新的分区表。
下面将描述使用imp/exp，impdp/expdp导入导出
分区表数据。
有关分区表的特性请参考：
Oracle分区表
SQLserver2005切换分区表
SQLserver2005基于已存在的表创建分区
有关导入导出工具请参考：
数据泵EXPDP导出工具的使用
数据泵IMPDP导入工具的使用
有关导入导出的官方文档请参考：
OriginalExportandImport
一、分区级别的导入导出
可以导出一个或多个分区，也可以导出所有分区（即整个表）。
可以导入所有分区（即整个表），一个或多个分区以及子分区。
对于已经存在数据的表，使用imp导入时需要使用参数IGNORE=y，而使用impdp，加table_exists_action=append|replace参数。
二、创建演示环境
1.查看当前数据库的版本
SQL>select*fromv$versionwhererownum<2;
BANNER
--------------------------------------------------------------------------------
OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production
2.创建一个分区表select*fromuser_tab_partitions;查询有分区的表
SQL>altersessionsetnls_date_format='yyyy-mm-dd';
SQL>CREATETABLEtb_pt（
sal_dateDATENOTNULL,
sal_idNUMBERNOTNULL,
sal_rowNUMBER（12）NOTNULL）
partitionbyrange（sal_date）
（
partitionsal_11valueslessthan（to_date（'2012-01-01','YYYY-MM-DD'））,
partitionsal_12valueslessthan（to_date（'2013-01-01','YYYY-MM-DD'））,
partitionsal_13valueslessthan（to_date（'2014-01-01','YYYY-MM-DD'））,
partitionsal_14valueslessthan（to_date（'2015-01-01','YYYY-MM-DD'））,
partitionsal_15valueslessthan（to_date（'2016-01-01','YYYY-MM-DD'））,
partitionsal_16valueslessthan（to_date（'2017-01-01','YYYY-MM-DD'））,
partitionsal_othervalueslessthan（maxvalue）
）nologging;
3.创建一个唯一索引
CREATEUNIQUEINDEXtb_pt_ind1
ONtb_pt（sal_date）nologging;
4.为分区表生成数据
SQL>INSERTINTOtb_pt
SELECTTRUNC（SYSDATE）+ROWNUM,dbms_random.random,ROWNUM
FROMdual
CONNECTBYLEVEL<=5000;
SQL>commit;
SQL>selectcount
（1）fromtb_ptpartition（sal_11）;
COUNT
（1）
----------
300
SQL>selectcount
（1）fromtb_ptpartition（sal_other）;
COUNT
（1）
----------
2873
SQL>select*fromtb_ptpartition（sal_12）whererownum<3;
SAL_DATESAL_IDSAL_ROW
-----------------------------
01-JAN-12-1.356E+09301
02-JAN-12-761530183302
三、使用exp/imp导出导入分区表数据
1.导出整个分区表
[oracle@node1~]$expscott/tigerfile='/u02/dmp/tb_pt.dmp'log='/u02/dmp/tb_pt.log'tables=tb_pt
Export:
Release11.2.0.1.0-ProductiononWedMar913:
52:
182011
Copyright（c）1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.
Connectedto:
OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production
WiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,
DataMiningandRealApplicationTestingo
ExportdoneinUS7ASCIIcharactersetandAL16UTF16NCHARcharacterset
serverusesZHS16GBKcharacterset（possiblecharsetconversion）
AbouttoexportspecifiedtablesviaConventionalPath...
..exportingtableTB_PT
..exportingpartitionSAL_11300rowsexported
..exportingpartitionSAL_12366rowsexported
..exportingpartitionSAL_13365rowsexported
..exportingpartitionSAL_14365rowsexported
..exportingpartitionSAL_15365rowsexported
..exportingpartitionSAL_16366rowsexported
..exportingpartitionSAL_OTHER2873rowsexported
EXP-00091:
Exportingquestionablestatistics.
EXP-00091:
Exportingquestionablestatistics.
Exportterminatedsuccessfullywithwarnings.
[oracle@node1~]$oerrexp00091
00091,00000,"Exportingquestionablestatistics."
//*Cause:
Exportwasableexportstatistics,butthestatisticsmaynotbe
//usuable.Thestatisticsarequestionablebecauseoneormoreof
//thefollowinghappenedduringexport:
arowerroroccurred,client
//charactersetorNCHARSETdoesnotmatchwiththeserver,aquery
//clausewasspecifiedonexport,onlycertainpartitionsor
//subpartitionswereexported,orafatalerroroccurredwhile
//processingatable.
//*Action:
Toexportnon-questionablestatistics,changetheclientcharacter
//setorNCHARSETtomatchtheserver,exportwithnoqueryclause,
//exportcompletetables.Ifdesired,importparameterscanbe
//suppliedsothatonlynon-questionablestatisticswillbeimported,
//andallquestionablestatisticswillberecalculated.
在上面的导出中出现了错误提示，即EXP-00091，该错误表明exp工具所在的环境变量中的NLS_LANG与DB中的NLS_CHARACTERSET不一致
尽管该错误对最终的数据并无影响，但调整该参数来避免异常还是有必要的。
因此需要将其设置为一致即可解决上述的错误提示。
SQL>selectuserenv（'language'）fromdual;
USERENV（'LANGUAGE'）
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
[oracle@node1~]$exportNLS_LANG='AMERICAN_AMERICA.ZHS16GBK'
经过上述设置之后再次导出正常，过程略。
2.导出单个分区
[oracle@node1~]$expscott/tigerfile='/u02/dmp/tb_pt_sal_16.dmp'log='/u02/dmp/tb_pt_sal_16.log'tables=tb_pt:
sal_16
Export:
Release11.2.0.1.0-ProductiononWedMar913:
52:
382011
Copyright（c）1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.
Connectedto:
OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production
WiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,
DataMiningandRealApplicationTestingo
ExportdoneinZHS16GBKcharactersetandAL16UTF16NCHARcharacterset
AbouttoexportspecifiedtablesviaConventionalPath...
..exportingtableTB_PT
..exportingpartitionSAL_16366rowsexported
EXP-00091:
Exportingquestionablestatistics.
EXP-00091:
Exportingquestionablestatistics.
Exportterminatedsuccessfullywithwarnings
在上面的导出过程中再次出现了统计信息错误的情况，因此采取了对该对象收集统计信息，但并不能解决该错误，但在exp命令行中增
加statistics=none即可，如下：
[oracle@node1~]$expscott/tigerfile='/u02/dmp/tb_pt_sal_16.dmp'log='/u02/dmp/tb_pt_sal_16.log'/
>tables=tb_pt:
sal_16statistics=none
如果要导出多个分区，则在tables参数中增加分区数。
如：
tables=（tb_pt:
sal_15,tb_pt:
sal_16）
3.使用imp工具生成创建分区表的DDL语句
[oracle@node1~]$impscott/tigertables=tb_ptindexfile='/u02/dmp/cr_tb_pt.sql'/
>file='/u02/dmp/tb_pt.dmp'ignore=y
Export:
Release11.2.0.1.0-ProductiononWedMar913:
54:
382011
Copyright（c）1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.
Connectedto:
OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production
WiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,
DataMiningandRealApplicationTestingo
ExportfilecreatedbyEXPORT:
V11.02.00viaconventionalpath
importdoneinUS7ASCIIcharactersetandAL16UTF16NCHARcharacterset
importserverusesZHS16GBKcharacterset（possiblecharsetconversion）
..skippingpartition"TB_PT":
"SAL_11"
..skippingpartition"TB_PT":
"SAL_12"
..skippingpartition"TB_PT":
"SAL_13"
..skippingpartition"TB_PT":
"SAL_14"
..skippingpartition"TB_PT":
"SAL_15"
..skippingpartition"TB_PT":
"SAL_16"
..skippingpartition"TB_PT":
"SAL_OTHER"
Importterminatedsuccessfullywithoutwarnings.
4.导入单个分区（使用先前备份的单个分区导入文件）
SQL>altertabletb_pttruncatepartitionsal_16;--导入前先将分区实现truncate
Tabletruncated.
SQL>selectcount
（1）fromtb_ptpartition（sal_16）;
COUNT
（1）
----------
0
SQL>hoimpscott/tigertables=tb_pt:
sal_16file='/u02/dmp/tb_pt_sal_16.dmp'ignore=y
Export:
Release11.2.0.1.0-ProductiononWedMar913:
55:
392011
Copyright（c）1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.
Connectedto:
OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production
WiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,
DataMiningandRealApplicationTestingo
ExportfilecreatedbyEXPORT:
V11.02.00viaconventionalpath
importdoneinUS7ASCIIcharactersetandAL16UTF16NCHARcharacterset
importserverusesZHS16GBKcharacterset（possiblecharsetconversion）
.importingSCOTT'sobjectsintoSCOTT
.importingSCOTT'sobjectsintoSCOTT
..importingpartition"TB_PT":
"SAL_16"
IMP-00058:
ORACLEerror1502encountered
ORA-01502:
index'SCOTT.TB_PT_IND1'orpartitionofsuchindexisinunusablestate
Importterminatedsuccessfullywithwarnings.
收到了ORA-01502错误，下面查看索引的状态，并对其重建索引后再执行导入
SQL>selectindex_name,statusfromdba_indexeswheretable_name='TB_PT';--查看索引的状态
INDEX_NAMESTATUS
--------------------------------------
TB_PT_IND1UNUSABLE
SQL>alterindexTB_PT_IND1rebuildonline;--重建索引
Indexaltered.
SQL>hoimpscott/tigertables=tb_pt:
sal_16file='/u02/dmp/tb_pt_sal_16.dmp'ignore=y--再次导入成功
Export:
Release11.2.0.1.0-ProductiononWedMar913:
56:
152011
Copyright（c）1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.
Connectedto:
OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production
WiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,
DataMiningandRealApplicationTestingo
ExportfilecreatedbyEXPORT:
V11.02.00viaconventionalpath
importdoneinUS7ASCIIcharactersetandAL16UTF16NCHARcharacterset
importserverusesZHS16GBKcharacterset（possiblecharsetconversion）
.importingSCOTT'sobjectsintoSCOTT
.importingSCOTT'sobjectsintoSCOTT
..importingpartition"TB_PT":
"SAL_16"366rowsimported
Importterminatedsuccessfullywithoutwarnings.
SQL>selectcount（*）fromtb_ptpartition（sal_16）;
COUNT（*）
----------
366
5.导入整个表
SQL>truncatetabletb_pt;--首先truncate整个表
Tabletruncated.
SQL>hoimpscott/tigertables=tb_ptfile='/u02/dmp/tb_pt.dmp'ignore=yindexes=y
Export:
Release11.2.0.1.0-ProductiononWedMar913:
57:
102011
Copyright（c）1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.
Connectedto:
OracleDatabase11gEnterpriseEditionR
升级会员 