资源描述：
《普通表转分区表.docx》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、普通表转分区表方法一、1.查看数据类型，行数如果存在long、rawlong类型无法进行分区表SELECTCOUNT(*)FROMEMP2.备份创建table脚本F4键CREATETABLESCOTT.EMP(EMPNONUMBER(4)NOTNULL,ENAMEVARCHAR2(10BYTE),JOBVARCHAR2(9BYTE),MGRNUMBER(4),HIREDATEDATE,SALNUMBER(7,2),COMMNUMBER(7,2),DEPTNONUMBER(2))TABLESPACESYSTEMPCTUSED40P
2、CTFREE10INITRANS1MAXTRANS255STORAGE(INITIAL104KNEXT104KMINEXTENTS1MAXEXTENTS505PCTINCREASE100FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULT)LOGGINGNOCOMPRESSNOCACHENOPARALLELMONITORING;--ThereisnostatementforindexSCOTT.SYS_C.--TheobjectiscreatedautomaticallybyOraclewhen
3、theparentobjectiscreated.CREATEORREPLACETRIGGERSCOTT.after_ins_upd_on_empbeforeinsertorupdateONSCOTT.EMPforeachrowbegin:new.ename:=upper(:new.ename);end;/ALTERTABLESCOTT.EMPADD(CONSTRAINTEMP_VALID_JOBCHECK(jobin('CLERK','SALESMAN','MANAGER','ANALYST','PRESIDENT')),PR
4、IMARYKEY(EMPNO)USINGINDEXTABLESPACESYSTEMPCTFREE10INITRANS2MAXTRANS255STORAGE(INITIAL104KNEXT104KMINEXTENTS1MAXEXTENTS505PCTINCREASE100FREELISTS1FREELISTGROUPS1));GRANTDELETE,INSERT,SELECT,UPDATEONSCOTT.EMPTOPUBLIC;3.exporexpdp数据1.expdp1.创建directory并赋权SQL>CREATEDIREC
5、TORYDUMPAS'/u01/oracle/';Directorycreated.SQL>GRANTREAD,WRITEONDIRECTORYDUMPTOAPPS;Grantsucceeded.2.查看directory目录权限SELECTPRIVILEGE,DIRECTORY_NAME,DIRECTORY_PATHFROMUSER_TAB_PRIVST,ALL_DIRECTORIESDWHERET.TABLE_NAME(+)=D.DIRECTORY_NAMEORDERBY2,1;3.导出数据[ebstst@finappdb]
6、$expdpapps/appsdirectory=DUMPtables=scott.EMP,scott.DEPTdumpfile=emp.dmplogfile=emp.logjob_name=expdpempparallel=5;[ebstst@finappdb]$expdpapps/appsdirectory=DUMPtables=scott.EMP,scott.DEPTdumpfile=emp.dmplogfile=emp.logjob_name=expdpempparallel=5;Export:Release11.1.0
7、.7.0-64bitProductiononFriday,28June,201311:21:44Copyright(c)2003,2007,Oracle.Allrightsreserved.Connectedto:OracleDatabase11gEnterpriseEditionRelease11.1.0.7.0-64bitProductionWiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptionsStarting"APPS"."SYS_EXPORT
8、_TABLE_01":apps/********tables=scott.EMP,scott.DEPTparallel=5directory=DUMPdumpfile=emp.dmplogfile=emp.logEstimateinprogressusingBL
