资源描述：
《普通表转分区表》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
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))TABLESPACESYSTEMPC
2、TUSED40PCTFREE10INITRANS1MAXTRANS255STORAGE(INITIAL104KNEXT104KMINEXTENTS1MAXEXTENTS505PCTINCREASE100FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULT)LOGGINGNOCOMPRESSNOCACHENOPARALLELMONITORING;--ThereisnostatementforindexSCOTT.SYS_C0093796.--Theobjectiscreatedau
3、tomaticallybyOraclewhentheparentobjectiscreated.CREATEORREPLACETRIGGERSCOTT.after_ins_upd_on_empbeforeinsertorupdateONSCOTT.EMPforeachrowbegin:new.ename:=upper(:new.ename);end;/ALTERTABLESCOTT.EMPADD(CONSTRAINTEMP_VALID_JOBCHECK(jobin('CLERK','SALESMAN','MANA
4、GER','ANALYST','PRESIDENT')),PRIMARYKEY(EMPNO)USINGINDEXTABLESPACESYSTEMPCTFREE10INITRANS2MAXTRANS255STORAGE(INITIAL104KNEXT104KMINEXTENTS1MAXEXTENTS505PCTINCREASE100FREELISTS1FREELISTGROUPS1));GRANTDELETE,INSERT,SELECT,UPDATEONSCOTT.EMPTOPUBLIC;3.exporexpdp数
5、据1.expdp1.创建directory并赋权SQL>CREATEDIRECTORYDUMPAS'/u01/oracle/';Directorycreated.SQL>GRANTREAD,WRITEONDIRECTORYDUMPTOAPPS;Grantsucceeded.2.查看directory目录权限SELECTPRIVILEGE,DIRECTORY_NAME,DIRECTORY_PATHFROMUSER_TAB_PRIVST,ALL_DIRECTORIESDWHERET.TABLE_NAME(+)=D.D
6、IRECTORY_NAMEORDERBY2,1;3.导出数据[ebstst@finappdb]$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=
7、emp.logjob_name=expdpempparallel=5;Export:Release11.1.0.7.0-64bitProductiononFriday,28June,201311:21:44Copyright(c)2003,2007,Oracle.Allrightsreserved.Connectedto:OracleDatabase11gEnterpriseEditionRelease11.1.0.7.0-64bitProductionWiththePartitioning,OLAP,DataM
8、iningandRealApplicationTestingoptionsStarting"APPS"."SYS_EXPORT_TABLE_01":apps/********tables=scott.EMP,scott.DEPTparallel=5directory=DUMPdumpfile=emp.dmplogfile=emp.logEstimateinprogress
