普通表转分区表.docx

普通表转分区表方法一、1.查看数据类型，行数如果存在long、raw long类型无法进行分区表SELECTCOUNT(*)FROMEMP2.备份创建table脚本F4键CREATETABLE SCOTT.EMP( EMPNO NUMBER(4)NOTNULL, ENAME VARCHAR2(10BYTE),JOBVARCHAR2(9BYTE), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2))TABLESPACESYSTEMPCTUSED40PCTFREE10INITRANS1MAXTRANS255STORAGE(INITIAL104KNEXT104KMINEXTENTS1MAXEXTENTS505PCTINCREASE100FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULT)LOGGINGNOCOMPRESSNOCACHENOPARALLELMONITORING;-- There is no statement for index SCOTT.SYS_C0093796.-- The object is created automatically by Oracle when the parent object is created.CREATEORREPLACETRIGGERSCOTT.after_ins_upd_on_empbeforeinsertorupdateON SCOTT.EMP foreachrowbegin:new.ename:=upper(:new.ename);end;/ALTERTABLE SCOTT.EMP ADD(CONSTRAINT EMP_VALID_JOBCHECK(jobin(CLERK,SALESMAN,MANAGER,ANALYST,PRESIDENT)),PRIMARYKEY(EMPNO)USINGINDEXTABLESPACESYSTEMPCTFREE10INITRANS2MAXTRANS255STORAGE(INITIAL104KNEXT104KMINEXTENTS1MAXEXTENTS505PCTINCREASE100FREELISTS1FREELISTGROUPS1));GRANTDELETE,INSERT,SELECT,UPDATEON SCOTT.EMP TOPUBLIC;3.exp or expdp数据1.expdp1.创建directory并赋权SQL CREATE DIRECTORY DUMP AS /u01/oracle/;Directory created.SQL GRANT READ,WRITE ON DIRECTORY DUMP TO APPS;Grant succeeded.2.查看directory目录权限SELECTPRIVILEGE, DIRECTORY_NAME, DIRECTORY_PATH FROM USER_TAB_PRIVS T, ALL_DIRECTORIES DWHERE T.TABLE_NAME(+) = D.DIRECTORY_NAME ORDERBY2, 1;3.导出数据[ebstst@finappdb]$ expdp apps/apps directory=DUMPtables=scott.EMP,scott.DEPTdumpfile=emp.dmp logfile=emp.log job_name=expdpemp parallel=5;[ebstst@finappdb]$ expdp apps/apps directory=DUMPtables=scott.EMP,scott.DEPTdumpfile=emp.dmp logfile=emp.log job_name=expdpemp parallel=5;Export: Release 11.1.0.7.0 - 64bit Production on Friday, 28 June, 2013 11:21:44Copyright (c) 2003, 2007, Oracle. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSt