普通表轉分區(qū)表

1、普通表轉分區(qū)表方法一、1.查看數據類型，行數如果存在long、raw long類型無法進行分區(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數據1.expdp1.創(chuàng)建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如果意外中斷可進行重新執(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.都說EXPDP速度快，但是我測了兩個table是exp速度快，不知為什么會這樣4.DROP TABLEDROP TABLE

14、SCOTT.EMPDROP TABLE SCOTT.DEPT5.創(chuàng)建基于時間的分區(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ū)不夠用，可進行添加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數據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帶過來，還得繼續(xù)測試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.創(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.進行數據導入INSERT INTO EMP_P SELECT * FROM EMP;COMMIT;3.表進行更名DROP TABLE EMP;ALTER TABLE EMP_P RENAME TO

30、 EMP;4查看是否是分區(qū)表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.創(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)建臨時表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.查看兩張表腳本發(fā)現index,constraint名稱都已對換名稱，另過度表變成非分區(qū)表而且里面還有了數據源表腳本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)