普通表转分区表

1、普通表转分区表方法一、1.查看数据类型，行数如果存在long、raw long类型无法进行分区表SELECT COUNT(*) FROM EMP2.备份创建table脚本F4键CREATE TABLE SCOTT.EMP( EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10 BYTE), JOB VARCHAR2(9 BYTE), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2)TABLESPACE SYSTEMPCTUSED 40PCTFREE

2、 10INITRANS 1MAXTRANS 255STORAGE ( INITIAL 104K NEXT 104K MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 100 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT )LOGGING NOCOMPRESS NOCACHENOPARALLELMONITORING;- There is no statement for index SCOTT.SYS_C0093796.- The object is created automatically by Oracle

3、 when the parent object is created.CREATE OR REPLACE TRIGGER SCOTT.after_ins_upd_on_empbefore insert or updateON SCOTT.EMP for each rowbegin :new.ename := upper(:new.ename);end;/ALTER TABLE SCOTT.EMP ADD ( CONSTRAINT EMP_VALID_JOB CHECK (job in (CLERK,SALESMAN,MANAGER,ANALYST,PRESIDENT), PRIMARY KEY

4、 (EMPNO) USING INDEX TABLESPACE SYSTEM PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 104K NEXT 104K MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 100 FREELISTS 1 FREELIST GROUPS 1 );GRANT DELETE, INSERT, SELECT, UPDATE ON SCOTT.EMP TO PUBLIC;3.exp or expdp数据1.expdp1.创建directory并赋权SQL CREATE DIRECTO

5、RY DUMP AS /u01/oracle/;Directory created.SQL GRANT READ,WRITE ON DIRECTORY DUMP TO APPS;Grant succeeded.2.查看directory目录权限SELECT PRIVILEGE, DIRECTORY_NAME, DIRECTORY_PATH FROM USER_TAB_PRIVS T, ALL_DIRECTORIES DWHERE T.TABLE_NAME(+) = D.DIRECTORY_NAME ORDER BY 2, 1;3.导出数据ebststfinapp db$ expdp apps/

6、apps directory=DUMP tables=scott.EMP,scott.DEPT dumpfile=emp.dmp logfile=emp.log job_name=expdpemp parallel=5;ebststfinapp db$ expdp apps/apps directory=DUMP tables=scott.EMP,scott.DEPT dumpfile=emp.dmp logfile=emp.log job_name=expdpemp parallel=5;Export: Release .0 - 64bit Production on Fri

7、day, 28 June, 2013 11:21:44Copyright (c) 2003, 2007, Oracle. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting APPS.SYS_EXPORT_TABLE_01: apps/* tables=sco

8、tt.EMP,scott.DEPT parallel=5 directory=DUMP dumpfile=emp.dmp logfile=emp.log Estimate in progress using BLOCKS method.Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 240 KB. . exported SCOTT.DEPT 5.929 KB 4 rows. . exported SCOTT.EMP 8.562 KB 14 rowsProcessi

9、ng object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/TRIGGERProcessing object type TABLE_EXPORT/TABLE/POST_TABLE_ACTIONMaster table APPS.

10、SYS_EXPORT_TABLE_01 successfully loaded/unloaded*Dump file set for APPS.SYS_EXPORT_TABLE_01 is: /u01/oracle/emp.dmpJob APPS.SYS_EXPORT_TABLE_01 successfully completed at 11:27:25查询运行JOBSELECT * FROM DBA_DATAPUMP_JOBS如果意外中断可进行重新执行2.EXPebststfinapp 11.1.0$ exp apps/apps tables=scott.emp,scott.dept fil

11、e=/u01/oracle/emp01.dmp log=/u01/oracle/emp01.log indexes=y constraints=y grants=y rows=y filesize=2G;ebststfinapp 11.1.0$ exp apps/apps tables=scott.emp,scott.dept file=/u01/oracle/emp01.dmp log=/u01/oracle/emp01.log indexes=y constraints=y grants=y rows=y filesize=2G;Export: Release .0 - P

12、roduction on Fri Jun 28 11:31:15 2013Copyright (c) 1982, 2007, Oracle. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in UTF8 character set and AL16

13、UTF16 NCHAR character setAbout to export specified tables via Conventional Path .Current user changed to SCOTT. . exporting table EMP 14 rows exported. . exporting table DEPT 4 rows exportedExport terminated successfully without warnings.都说EXPDP速度快，但是我测了两个table是exp速度快，不知为什么会这样4.DROP TABLEDROP TABLE

14、SCOTT.EMPDROP TABLE SCOTT.DEPT5.创建基于时间的分区表CREATE TABLE SCOTT.EMP( EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10 BYTE), JOB VARCHAR2(9 BYTE), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2)TABLESPACE CUXIPARTITION BY RANGE(HIREDATE)(PARTITION EMP_P1 VALUES LESS THAN(TO

15、_DATE(1983-01-01,YYYY-MM-DD) TABLESPACE CUXI,PARTITION EMP_P2 VALUES LESS THAN(TO_DATE(1986-01-01,YYYY-MM-DD) TABLESPACE CUXI,PARTITION EMP_P3 VALUES LESS THAN(TO_DATE(1990-01-01,YYYY-MM-DD) TABLESPACE CUXI);如果分区不够用，可进行添加ALTER TABLE EMP_P ADD PARTITION EMP_P4 VALUES LESS THAN(TO_DATE(1991-01-01,YYYY

16、-MM-DD);6.查看创建情况SELECT * FROM DBA_TABLES WHERE TABLE_NAME=EMP;SELECT * FROM DBA_PART_TABLES WHERE TABLE_NAME=EMP;SELECT * FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME=EMP;7.imp或者impdp数据1.impebststfinapp oracle$ imp apps/apps file=emp01.dmp log=emp01.log fromuser=scott touser=scott buffer=100000000 ignor

17、e=y indexes=y rows=y grants=y;ebststfinapp oracle$ imp apps/apps file=emp01.dmp log=emp01.log fromuser=scott touser=scott buffer=100000000 ignore=y;Import: Release .0 - Production on Fri Jun 28 15:14:56 2013Copyright (c) 1982, 2007, Oracle. All rights reserved.Connected to: Oracle Database 1

18、1g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport file created by EXPORT:V11.01.00 via conventional pathimport done in UTF8 character set and AL16UTF16 NCHAR character setIMP-00046: using FILESIZE value from

19、 export file of 2147483648. importing SCOTTs objects into SCOTT. . importing table EMP 14 rows imported. . importing table DEPTIMP-00019: row rejected due to ORACLE error 1IMP-00003: ORACLE error 1 encounteredORA-00001: unique constraint (SCOTT.PK_DEPT) violatedColumn 1 10Column 2 ACCOUNTINGColumn 3

20、 NEW YORKIMP-00019: row rejected due to ORACLE error 1IMP-00003: ORACLE error 1 encounteredORA-00001: unique constraint (SCOTT.PK_DEPT) violatedColumn 1 20Column 2 RESEARCHColumn 3 DALLASIMP-00019: row rejected due to ORACLE error 1IMP-00003: ORACLE error 1 encounteredORA-00001: unique constraint (S

21、COTT.PK_DEPT) violatedColumn 1 30Column 2 SALESColumn 3 CHICAGOIMP-00019: row rejected due to ORACLE error 1IMP-00003: ORACLE error 1 encounteredORA-00001: unique constraint (SCOTT.PK_DEPT) violatedColumn 1 40Column 2 OPERATIONSColumn 3 BOSTON 0 rows importedAbout to enable constraints.Import termin

22、ated successfully with warnings.2.impdp测试此方法无法将index带过来，还得继续测试ebststfinapp oracle$ impdp apps/apps directory=DUMP dumpfile=emp.dmp logfile=emp.log table_exists_action=append PARALLEL=5;ebststfinapp oracle$ impdp apps/apps directory=DUMP dumpfile=emp.dmp logfile=emp.log table_exists_action=append PAR

23、ALLEL=5;Import: Release .0 - 64bit Production on Friday, 28 June, 2013 15:49:25Copyright (c) 2003, 2007, Oracle. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing

24、 optionsMaster table APPS.SYS_IMPORT_FULL_01 successfully loaded/unloadedStarting APPS.SYS_IMPORT_FULL_01: apps/* directory=DUMP dumpfile=emp.dmp logfile=emp.log table_exists_action=append PARALLEL=5 Processing object type TABLE_EXPORT/TABLE/TABLEORA-39152: Table SCOTT.DEPT exists. Data will be appe

25、nded to existing table but all dependent metadata will be skipped due to table_exists_action of appendORA-39152: Table SCOTT.EMP exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of appendProcessing object type TABLE_EXPORT/TABLE/TA

26、BLE_DATAORA-31693: Table data object SCOTT.DEPT failed to load/unload and is being skipped due to error:ORA-00001: unique constraint (SCOTT.PK_DEPT) violated. . imported SCOTT.EMP 8.570 KB 14 rowsProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAIN

27、T/CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob APPS.SYS_IMPORT_FULL_01 completed with 3 error(s) at 15:49:278.查看数据是否相同SE

28、LECT COUNT(*) FROM EMP方法二1.创建分区表CREATE TABLE SCOTT.EMP_P( EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10 BYTE), JOB VARCHAR2(9 BYTE), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2)TABLESPACE CUXIPARTITION BY RANGE(HIREDATE)(PARTITION EMP_P1 VALUES LESS THAN(TO_DATE(19

29、83-01-01,YYYY-MM-DD) TABLESPACE CUXI,PARTITION EMP_P2 VALUES LESS THAN(TO_DATE(1986-01-01,YYYY-MM-DD) TABLESPACE CUXI,PARTITION EMP_P3 VALUES LESS THAN(TO_DATE(1990-01-01,YYYY-MM-DD) TABLESPACE CUXI);2.进行数据导入INSERT INTO EMP_P SELECT * FROM EMP;COMMIT;3.表进行更名DROP TABLE EMP;ALTER TABLE EMP_P RENAME TO

30、 EMP;4查看是否是分区表SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME=EMP;5.建相应index/constraint/赋权等CREATE OR REPLACE TRIGGER SCOTT.after_ins_upd_on_empbefore insert or updateON SCOTT.EMP for each rowbegin :new.ename := upper(:new.ename);end;/ALTER TABLE SCOTT.EMP ADD ( CONSTRAINT EMP_VALID_JOB CHECK (job

31、 in (CLERK,SALESMAN,MANAGER,ANALYST,PRESIDENT), PRIMARY KEY (EMPNO) USING INDEX TABLESPACE SYSTEM PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 104K NEXT 104K MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 100 FREELISTS 1 FREELIST GROUPS 1 );GRANT DELETE, INSERT, SELECT, UPDATE ON SCOTT.EMP TO PUBLI

32、C;方法三1.创建分区表CREATE TABLE SCOTT.EMP_P( EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10 BYTE), JOB VARCHAR2(9 BYTE), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2)TABLESPACE USERSPARTITION BY RANGE(HIREDATE)(PARTITION EMP_P1 VALUES LESS THAN(TO_DATE(1981-05-01,YYYY-MM-DD

33、) TABLESPACE USERS,PARTITION EMP_P2 VALUES LESS THAN(TO_DATE(1981-12-03,YYYY-MM-DD) TABLESPACE USERS,PARTITION EMP_P3 VALUES LESS THAN(TO_DATE(1990-01-01,YYYY-MM-DD) TABLESPACE USERS);2.创建临时表CREATE TABLE EMPT_P1 AS SELECT * FROM EMP WHERE HIREDATE SCOTT, ORIG_TABLE = EMPT, INT_TABLE = EMP_P); END;5.

34、进行同步动作为了减少finishe时间BEGIN DBMS_REDEFINITION.SYNC_INTERIM_TABLE( UNAME = SCOTT, ORIG_TABLE = EMPT, INT_TABLE = EMP_P); END;6.进行FINISHBEGIN DBMS_REDEFINITION.FINISH_REDEF_TABLE( UNAME = SCOTT, ORIG_TABLE = EMPT, INT_TABLE = EMP_P); END;7.查看结果SELECT PARTITIONED FROM USER_TABLES WHERE TABLE_NAME = EMPT;S

35、ELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME=EMPT;SELECT * FROM USER_PART_TABLES WHERE TABLE_NAME=EMPT;8.查看两张表脚本发现index,constraint名称都已对换名称，另过度表变成非分区表而且里面还有了数据源表脚本ALTER TABLE SCOTT.EMPT DROP PRIMARY KEY CASCADE;DROP TABLE SCOTT.EMPT CASCADE CONSTRAINTS;CREATE TABLE SCOTT.EMPT( EMPNO NUMBER(4) NO

36、T NULL, ENAME VARCHAR2(10 BYTE), JOB VARCHAR2(9 BYTE), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2)TABLESPACE USERSPCTUSED 0PCTFREE 10INITRANS 1MAXTRANS 255STORAGE ( BUFFER_POOL DEFAULT )PARTITION BY RANGE (HIREDATE)( PARTITION EMP_P1 VALUES LESS THAN (TO_DATE( 1

37、981-05-01 00:00:00, SYYYY-MM-DD HH24:MI:SS, NLS_CALENDAR=GREGORIAN) LOGGING NOCOMPRESS TABLESPACE USERS PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED BUFFER_POOL DEFAULT ), PARTITION EMP_P2 VALUES LESS THAN (TO_DATE( 1981-12-03 00:00:00, SYYYY-MM-

38、DD HH24:MI:SS, NLS_CALENDAR=GREGORIAN) LOGGING NOCOMPRESS TABLESPACE USERS PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED BUFFER_POOL DEFAULT ), PARTITION EMP_P3 VALUES LESS THAN (TO_DATE( 1990-01-01 00:00:00, SYYYY-MM-DD HH24:MI:SS, NLS_CALENDAR=G

39、REGORIAN) LOGGING NOCOMPRESS TABLESPACE USERS PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED BUFFER_POOL DEFAULT )NOCOMPRESS NOCACHENOPARALLELMONITORING;CREATE UNIQUE INDEX SCOTT.EMP_P_CONSTRAINT ON SCOTT.EMPT(EMPNO)LOGGINGTABLESPACE USERSPCTFREE 1

40、0INITRANS 2MAXTRANS 255STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT )NOPARALLEL;ALTER TABLE SCOTT.EMPT ADD ( CONSTRAINT EMP_P_CONSTRAINT PRIMARY KEY (EMPNO) USING INDEX SCOTT.EMP_P_CONSTRAINT);过渡表ALTER TABLE SCOTT.EMP_P DROP PRIMARY KEY CASCADE;DROP TABLE SCOTT.EMP_P CASCADE CONSTRAINTS;CREATE TABLE SCOTT.EMP_P( EMPNO NUMBER(4), ENAME VARCHAR2(10 BYTE), JOB VARCHAR2(9 BYTE), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2)