创建 导入导出 Oracle 分区表数据.docx
《创建 导入导出 Oracle 分区表数据.docx》由会员分享，可在线阅读，更多相关《创建 导入导出 Oracle 分区表数据.docx（26页珍藏版）》请在冰豆网上搜索。
创建导入导出Oracle分区表数据
导入导出Oracle分区表数据
Createtable（创建分区表）
createtableBILL_MONTHFEE_ZERO
（
SERV_ID NUMBER（20）notnull,
BILLING_CYCLE_MONTHNUMBER（6）notnull,
DATE_TYPE NUMBER
（1）,
ACC_NBR VARCHAR2（80）
）
partitionbyrange（BILLING_CYCLE_MONTH）
（partitionp_200407valueslessthan（200407）
tablespaceTS_ZIKEN
storage（initial100knext100kminextents1maxextentsunlimitedpctincrease0）,
partitionp_200408valueslessthan（200408）
tablespaceTS_ZIKEN
storage（initial100knext100kminextents1maxextentsunlimitedpctincrease0））
;
createindexidx_bill_monthfee_zero_idx01onbill_monthfee_zero（billing_cycle_month）
tablespaceTS_ZIKEN_idx
storage（initial100knext100kminextents1maxextentsunlimitedpctincrease0）nologging;
grantallonbill_monthfee_zerotodxsq_dev;
--增加分区表
altertableBILL_MONTHFEE_ZEROaddPartitionp_200409
valueslessthan（200409）tablespacets_ziken;
--删除一分区
altertablepart_tbldropPartitionpart_tbl_08;
--将一个分区分为两个分区
altertablebill_monthfee_zerosplitPartitionp_200409at（200409）
into（Partitionp_200409_1tablespacets_ziken,
Partitionp_200409_2tablespacets_ziken_idx）;
--合并分区
ALTERTABLEbill_monthfee_zero
MERGEPARTITIONSp_200408,p_200409INTOPARTITIONp_all
--将分区改名
altertablebill_monthfee_zerorenamePartitionp_200408top_fee_200408
--将分区改表空间
altertablebill_monthfee_zeromovePartitionp_200409
tablespacets_ziken_01nologging
--查询特定分区
selectcount（*）fromBILL_MONTHFEE_ZEROpartition（p_200407）;
--添加数据
insertintobill_monthfee_zeroselect*frombill_monthfee_zeropartition（p_200407）
--分区表的导出
userid=dxsq/teledoone@jndxsq154
buffer=102400
tables=bill_monthfee:
P_200401,
file=E:
\exp_para\exp_dxsq_tables.dmp
log=E:
\exp_para\exp_dxsq_tables.log
技巧：
删除表中一个字段：
altertablebill_monthfee_zerosetunusedcolumndate_type;
添加一个字段：
altertablebill_monthfee_zeroadddate_typenumber
（1）;
UNIX中ORACLE分区表导出
创建分区表：
createtabletest
（
join_dateVARCHAR2（8）,
nameVARCHAR2（50）,
sexchar
（1）,
addressvarchar2（100）
）
partitionbylist（statis_date）（partitionpart_20091101values（'20091101'））;
转移分区表
altertabletb_cust_view_daymovePartitionPART_20100505tablespaceTBS_USERnologging;
转移表空间，将该表20100505的分区转移至表空间TBS_USER，这样他就占用的是USER的空间
altertableTB_CUST_INFO_DAYdroppartitionPART_20100301;删除表分区
altertabletbnamedroppartitionpartname
altertabletbnamemovepartitionpartnametablespacetbspacename
1.分区表导出
--单表导出
exptest/testfile=/home/wtest/mail_cust12.dmptables=PERSON_MAIL_MON:
PART_200912
--多表导出
expfile=part_1.dmpuserid=test/test@orclbuffer=33554432recordlength=65535tables=TABLE_PART1:
PART_20091216TABLE_PART12:
PART_20091216
2.普通表多表导出
---月表：
导出201001的数据
expfile=tbale12userid=test/test@orclbuffer=33554432recordlength=65535tables=table_Atable_Bquery=\"whereDATE_MONTH='201001'\"
---日表导出20100218--20100220的数据
expfile=/home/test/table15_16.dmpuserid=test/test@orclbuffer=33554432recordlength=65535tables=table_aatable_bbquery=\"wheredate_daybetween'20100218'and'20100220'\"
关键字说明（默认）userid用户名/口令
buffer数据缓冲区大小
file输出文件（EXPDAT.DMP）
compress导入到一个区（Y）
grants导出权限（Y）
indexes导出索引（Y）
direct直接路径（N）
log屏幕输出的日志文件
rows导出数据行（Y）
consistent交叉表的一致性（N）
full导出整个文件（N）
owner所有者用户名列表
tables表名称列表
recordlengthIO记录的长度
inctype增量导出类型
record跟踪增量导出（Y）
triggers导出触发器（Y）
statistics分析对象（ESTIMATE）
parfile参数文件名
constraints导出的约束条件（Y）
object_consistent只在对象导出期间设置为读的事务处理（N）
feedback每x行的显示进度（0）
filesize每个转储文件的最大大小
flashback_scn用于将会话快照设置回以前状态的SCN
flashback_time用于获取最接近指定时间的SCN的时间
query用于导出表的子集的select子句
resumable遇到与空格相关的错误时挂起（N）
resumable_name用于标识可恢复语句的文本字符串
resumable_timeoutRESUMABLE的等待时间
tts_full_check对TTS执行完整的或部分相关性检查
tablespaces要导出的表空间列表
transport_tablespace导出可传输的表空间元数据（N）
template调用iAS模式导出的模板名
注意：
unix中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.创建一个分区表
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
升级会员 