Oracle分区表数据导入导出Word文档格式.docx
《Oracle分区表数据导入导出Word文档格式.docx》由会员分享,可在线阅读,更多相关《Oracle分区表数据导入导出Word文档格式.docx(16页珍藏版)》请在冰豆网上搜索。
![Oracle分区表数据导入导出Word文档格式.docx](https://file1.bdocx.com/fileroot1/2022-11/27/da7e94c4-1ff3-46ca-8b4f-517b2fc63775/da7e94c4-1ff3-46ca-8b4f-517b2fc637751.gif)
对于已经存在数据的表,使用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;
查询有分区的表
altersessionsetnls_date_format='
yyyy-mm-dd'
;
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'
partitionsal_13valueslessthan(to_date('
2014-01-01'
partitionsal_14valueslessthan(to_date('
2015-01-01'
partitionsal_15valueslessthan(to_date('
2016-01-01'
partitionsal_16valueslessthan(to_date('
2017-01-01'
partitionsal_othervalueslessthan(maxvalue)
)nologging;
3.创建一个唯一索引
CREATEUNIQUEINDEXtb_pt_ind1
ONtb_pt(sal_date)nologging;
4.为分区表生成数据
INSERTINTOtb_pt
SELECTTRUNC(SYSDATE)+ROWNUM,dbms_random.random,ROWNUM
FROMdual
CONNECTBYLEVEL<
=5000;
commit;
selectcount
(1)fromtb_ptpartition(sal_11);
COUNT
(1)
----------
300
selectcount
(1)fromtb_ptpartition(sal_other);
2873
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.
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不一致
尽管该错误对最终的数据并无影响,但调整该参数来避免异常还是有必要的。
因此需要将其设置为一致即可解决上述的错误提示。
selectuserenv('
language'
)fromdual;
USERENV('
LANGUAGE'
)
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
[oracle@node1~]$exportNLS_LANG='
AMERICAN_AMERICA.ZHS16GBK'
经过上述设置之后再次导出正常,过程略。
2.导出单个分区
/u02/dmp/tb_pt_sal_16.dmp'
/u02/dmp/tb_pt_sal_16.log'
tables=tb_pt:
sal_16
382011
ExportdoneinZHS16GBKcharactersetandAL16UTF16NCHARcharacterset
Exportterminatedsuccessfullywithwarnings
在上面的导出过程中再次出现了统计信息错误的情况,因此采取了对该对象收集统计信息,但并不能解决该错误,但在exp命令行中增
加statistics=none即可,如下:
/
>
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='
ignore=y
54:
ExportfilecreatedbyEXPORT:
V11.02.00viaconventionalpath
importdoneinUS7ASCIIcharactersetandAL16UTF16NCHARcharacterset
importserverusesZHS16GBKcharacterset(possiblecharsetconversion)
..skippingpartition"
TB_PT"
:
"
SAL_11"
SAL_12"
SAL_13"
SAL_14"
SAL_15"
SAL_16"
SAL_OTHER"
Importterminatedsuccessfullywithoutwarnings.
4.导入单个分区(使用先前备份的单个分区导入文件)
altertabletb_pttruncatepartitionsal_16;
--导入前先将分区实现truncate
Tabletruncated.
selectcount
(1)fromtb_ptpartition(sal_16);
0
hoimpscott/tigertables=tb_pt:
sal_16file='
55:
392011
.importingSCOTT'
sobjectsintoSCOTT
..importingpartition"
IMP-00058:
ORACLEerror1502encountered
ORA-01502:
index'
SCOTT.TB_PT_IND1'
orpartitionofsuchindexisinunusablestate
Importterminatedsuccessfullywithwarnings.
收到了ORA-01502错误,下面查看索引的状态,并对其重建索引后再执行导入
selectindex_name,statusfromdba_indexeswheretable_name='
TB_PT'
--查看索引的状态
INDEX_NAMESTATUS
--------------------------------------
TB_PT_IND1UNUSABLE
alterindexTB_PT_IND1rebuildonline;
--重建索引
Indexaltered.
ignore=y--再次导入成功
56:
152011
366rowsimported
selectcount(*)fromtb_ptpartition(sal_16);
COUNT(*)
366
5.导入整个表
truncatetabletb_pt;
--首先truncate整个表
hoimpscott/tigertables=tb_ptfile='
ignore=yindexes=y
57:
102011
OracleDatabase11gEnterpriseEditionR