普通表转分区表.docx

普通表转分区表
方法一、
1.查看数据类型，行数
如果存在long、raw long类型无法进行分区表
SELECT COUNT(*) FROM EMP
2.备份创建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 SYSTEM
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 104K
NEXT 104K
MINEXTENTS 1
MAXEXTENTS 505
PCTINCREASE 100
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

-- There is no statement for index SCOTT.SYS_C0093796.
-- The object is created automatically by Oracle when the parent object is created.

CREATE OR REPLACE TRIGGER SCOTT.after_ins_upd_on_emp
before insert or update
ON SCOTT.EMP for each row
begin
: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
(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.expdp
1.创建directory并赋权
SQL> CREATE DIRECTORY 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 D
WHERE T.TABLE_NAME(+) = D.DIRECTORY_NAME ORDER BY 2, 1;
3.导出数据
[ebstst@finapp db]$ expdp apps/apps directory=DUMP tables=scott.EMP,scott.DEPT dumpfile=emp.dmp logfile=emp.log job_name=expdpemp parallel=5;
[ebstst@finapp db]$ expdp apps/apps directory=DUMP tables=scott.EMP,scott.DEPT dumpfile=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:44

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "APPS"."SYS_EXPORT_TABLE_01": apps/******** tables=scott.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_DATA
Total estimation using BLOCKS method: 240 KB
. . exported "SCOTT"."DEPT" 5.929 KB 4 rows
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/POST_TABLE_ACTION
Master table "APPS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for APPS.SYS_EXPORT_TABLE_01 is:
/u01/oracle/emp.dmp
Job "APPS"."SYS_EXPORT_TABLE_01" successfully completed at 11:27:25

查询运行JOB
SELECT * FROM DBA_DATAPUMP_JOBS

如果意外中断可进行重新执行

2.EXP
[ebstst@finapp 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;
[ebstst@finapp 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 11.1.0.7.0 - Production on Fri Jun 28 11:31:15 2013

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table EMP 14 rows exported
. . exporting table DEPT 4 rows exported
Export terminated successfully without warnings.
都说EXPDP速度快，但是我测了两个table是exp速度快，不知为什么会这样
4.DROP TABLE
DROP TABLE SCOTT.EMP
DROP TABLE SCOTT.DEPT
5.创建基于时间的分区表
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 CUXI
PARTITION BY RANGE(HIREDATE)
(
PARTITION EMP_P1 VALUES LESS THAN(TO_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-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.imp
[ebstst@finapp oracle]$ imp apps/apps file=emp01.dmp log=emp01.log fromuser=scott touser=scott buffer=100000000 ignore=y indexes=y rows=y grants=y;
[ebstst@finapp oracle]$ imp apps/apps file=emp01.dmp log=emp01.log fromuser=scott touser=scott buffer=100000000 ignore=y;

Import: Release 11.1.0.7.0 - Production on Fri Jun 28 15:14:56 2013

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.01.00 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
IMP-00046: using FILESIZE value from export file of 2147483648
. importing SCOTT's objects into SCOTT
. . importing table "EMP" 14 rows imported
. . importing table "DEPT"
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated
Column 1 10
Column 2 ACCOUNTING
Column 3 NEW YORK
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated
Column 1 20
Column 2 RESEARCH
Column 3 DALLAS
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated
Column 1 30
Column 2 SALES
Column 3 CHICAGO
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated
Column 1 40
Column 2 OPERATIONS
Column 3 BOSTON 0 rows imported
About to enable constraints...
Import terminated successfully with warnings.

2.impdp
测试此方法无法将index带过来，还得继续测试
[ebstst@finapp oracle]$ impdp apps/apps directory=DUMP dumpfile=emp.dmp logfile=emp.log table_exists_action=append PARALLEL=5;
[ebstst@finapp oracle]$ impdp apps/apps directory=DUMP dumpfile=emp.dmp logfile=emp.log table_exists_action=append PARALLEL=5;

Import: Release 11.1.0.7.0 - 64bit Production on Friday, 28 June, 2013 15:49:25

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "APPS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "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/TABLE
ORA-39152: Table "SCOTT"."DEPT" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
ORA-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 append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-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 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "APPS"."SYS_IMPORT_FULL_01" completed with 3 error(s) at 15:49:27
8.查看数据是否相同
SELECT 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 CUXI
PARTITION BY RANGE(HIREDATE)
(
PARTITION EMP_P1 VALUES LESS THAN(TO_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
);
2.进行数据导入
INSERT INTO EMP_P SELECT * FROM EMP;
COMMIT;
3.表进行更名
DROP TABLE EMP;
ALTER TABLE EMP_P RENAME TO EMP;
4查看是否是分区表
SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMP';
5.建相应index/constraint/赋权等
CREATE OR REPLACE TRIGGER SCOTT.after_ins_upd_on_emp
before insert or update
ON SCOTT.EMP for each row
begin
: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
(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;
方法三
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 USERS
PARTITION BY RANGE(HIREDATE)
(
PARTITION EMP_P1 VALUES LESS THAN(TO_DATE('1981-05-01','YYYY-MM-DD')) 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 < TO_DATE('1981-05-01','YYYY-MM-DD');
CREATE TABLE EMPT_P2 AS SELECT * FROM EMP WHERE HIREDATE BETWEEN TO_DATE('1981-05-01','YYYY-MM-DD') AND TO_DATE('1981-12-02','YYYY-MM-DD');
CREATE TABLE EMPT_P3 AS SELECT * FROM EMP WHERE HIREDATE BETWEEN TO_DATE('1982-12-03','YYYY-MM-DD') AND TO_DATE('1990-01-01','YYYY-MM-DD');
SELECT * FROM EMPT_P1;
SELECT * FROM EMPT_P2;
SELECT * FROM EMPT_P3;
3.进行exchange
------------因为exchange要保证临时表结构与原表完全相同，所以需进行更改------------
ALTER TABLE EMPT_P1 MODIFY EMPNO NOT NULL;
ALTER TABLE EMPT_P2 MODIFY EMPNO NOT NULL;
ALTER TABLE EMPT_P3 MODIFY EMPNO NOT NULL;
ALTER TABLE EMP_P EXCHANGE PARTITION EMP_P1 WITH TABLE EMPT_P1;
ALTER TABLE EMP_P EXCHANGE PARTITION EMP_P2 WITH TABLE EMPT_P2;
ALTER TABLE EMP_P EXCHANGE PARTITION EMP_P3 WITH TABLE EMPT_P3;
SELECT * FROM EMP_P;
4.进行DROP表重命名，和方法二相同
方法四
DBMS_REDEFINITION此pkg用于在线重定义，如果系统无业务使用，则建议不采取此方法，此方法过程复杂，且还要建立与原表相同结构的过度表
1．赋权
GRANT EXECUTE_CATALOG_ROLE TO SCOTT;
GRANT CREATE ANY TABLE TO SCOTT;
GRANT ALTER ANY TABLE TO SCOTT;
GRANT DROP ANY TABLE TO SCOTT;
GRANT LOCK ANY TABLE TO SCOTT;
GRANT SELECT ANY TABLE TO SCOTT;
2.创建原始表和过度分区表
原始表
CREATE TABLE EMPT AS SELECT * FROM EMP;
ALTER TABLE EMPT ADD CONSTRAINT EMPT_CONSTRAINT PRIMARY KEY(EMPNO);
过度表
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 USERS
PARTITION BY RANGE(HIREDATE)
(
PARTITION EMP_P1 VALUES LESS THAN(TO_DATE('1981-05-01','YYYY-MM-DD')) 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
);
ALTER TABLE EMP_P ADD CONSTRAINT EMP_P_CONSTRAINT PRIMARY KEY(EMPNO);
3.check源表是否满足在线重定义
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT', 'EMPT');

因为在线重定义是基于主键或rowid进行的，因此需创建主键
ALTER TABLE EMPT ADD CONSTRAINT EMPT_P PRIMARY KEY (EMPNO);
再次检查无报错
4.开始执行重定义
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
UNAME => 'SCOTT',
ORIG_TABLE => 'EMPT',
INT_TABLE => 'EMP_P');
END;
5.进行同步动作
为了减少finishe时间
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
UNAME => 'SCOTT',
ORIG_TABLE => 'EMPT',
INT_TABLE => 'EMP_P');
END;
6.进行FINISH
BEGIN
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';
SELECT * 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) 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 US