Oracle分区表数据导入导出.docx
- 文档编号:4009434
- 上传时间:2022-11-27
- 格式:DOCX
- 页数:16
- 大小:20.45KB
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 分区表 数据 导入 导出