普通表轉(zhuǎn)分區(qū)表

1、普通表轉(zhuǎn)分區(qū)表方法一、1.查看數(shù)據(jù)類(lèi)型，行數(shù)如果存在long、raw long類(lèi)型無(wú)法進(jìn)行分區(qū)表SELECT COUNT(*) FROM EMP2.備份創(chuàng)建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數(shù)據(jù)1.expdp1.創(chuàng)建directory并賦權(quán)SQL CREATE DIRECTO

5、RY DUMP AS /u01/oracle/;Directory created.SQL GRANT READ,WRITE ON DIRECTORY DUMP TO APPS;Grant succeeded.2.查看directory目錄權(quán)限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.導(dǎo)出數(shù)據(jù)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查詢(xún)運(yùn)行JOBSELECT * FROM DBA_DATAPUMP_JOBS如果意外中斷可進(jìn)行重新執(zhí)行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.都說(shuō)EXPDP速度快，但是我測(cè)了兩個(gè)table是exp速度快，不知為什么會(huì)這樣4.DROP TABLEDROP TABLE

14、SCOTT.EMPDROP TABLE SCOTT.DEPT5.創(chuàng)建基于時(shí)間的分區(qū)表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);如果分區(qū)不夠用，可進(jìn)行添加ALTER TABLE EMP_P ADD PARTITION EMP_P4 VALUES LESS THAN(TO_DATE(1991-01-01,YYYY

16、-MM-DD);6.查看創(chuàng)建情況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數(shù)據(jù)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測(cè)試此方法無(wú)法將index帶過(guò)來(lái)，還得繼續(xù)測(cè)試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.查看數(shù)據(jù)是否相同SE

28、LECT COUNT(*) FROM EMP方法二1.創(chuàng)建分區(qū)表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.進(jìn)行數(shù)據(jù)導(dǎo)入INSERT INTO EMP_P SELECT * FROM EMP;COMMIT;3.表進(jìn)行更名DROP TABLE EMP;ALTER TABLE EMP_P RENAME TO

30、 EMP;4查看是否是分區(qū)表SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME=EMP;5.建相應(yīng)index/constraint/賦權(quán)等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.創(chuàng)建分區(qū)表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.創(chuàng)建臨時(shí)表CREATE TABLE EMPT_P1 AS SELECT * FROM EMP WHERE HIREDATE SCOTT, ORIG_TABLE = EMPT, INT_TABLE = EMP_P); END;5.

34、進(jìn)行同步動(dòng)作為了減少finishe時(shí)間BEGIN DBMS_REDEFINITION.SYNC_INTERIM_TABLE( UNAME = SCOTT, ORIG_TABLE = EMPT, INT_TABLE = EMP_P); END;6.進(jìn)行FINISHBEGIN DBMS_REDEFINITION.FINISH_REDEF_TABLE( UNAME = SCOTT, ORIG_TABLE = EMPT, INT_TABLE = EMP_P); END;7.查看結(jié)果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.查看兩張表腳本發(fā)現(xiàn)index,constraint名稱(chēng)都已對(duì)換名稱(chēng)，另過(guò)度表變成非分區(qū)表而且里面還有了數(shù)據(jù)源表腳本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);過(guò)渡表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)