普通表转分区表脚本

测试表搭建

/* Formatted on 2020/5/13 上午 10:09:04 (QP5 v5.163.1008.3004) */
CREATE TABLE MONKEY.NORMAL_TO_PAR(ID NUMBER,NAME VARCHAR2 (50),AGE NUMBER,ADD_DATE DATE);

ALTER TABLE MONKEY.NORMAL_TO_PAR ADD(CONSTRAINT PK_NORMAL_TO_PAR PRIMARY KEY(ID));

CREATE INDEX MONKEY.IX_NORMAL_TO_PAR_NAME ON MONKEY.NORMAL_TO_PAR(NAME);

CREATE SEQUENCE MONKEY.SEQ_NORMAL_TO_PAR_ID 
CACHE 20;

CREATE TRIGGER MONKEY.TRI_NORMAL_TO_PAR BEFORE INSERT ON MONKEY.NORMAL_TO_PAR FOR EACH ROW BEGIN SELECT MONKEY.SEQ_NORMAL_TO_PAR_ID.NEXTVAL INTO :NEW.ID FROM DUAL;END;

CREATE PROCEDURE MONKEY.NORMAL_TO_PAR_PRO
AS
BEGIN
INSERT INTO MONKEY.NORMAL_TO_PAR(NAME,AGE,ADD_DATE) VALUES ('AA',20,SYSDATE);
END;
/
GRANT SELECT ON MONKEY.NORMAL_TO_PAR TO SYN_MONKEY;
CREATE SYNONYM SYN_MONKEY.NORMAL_TO_PAR FOR MONKEY.NORMAL_TO_PAR;

1.查看表是否有OGG

　如果表是OGG目标端，需要关闭OGG，防止新数据进入

2.确保表中无新数据进入

　在第1步中，如果表是OGG目标端，那么停掉OGG后就可以保证表无新数据进入

   如果是业务表，要么在无业务期间作业，要么锁定账号，防止表的DML语句

--锁定账号，防止新数据进入
ALTER USER  MONKEY_DEV ACCOUNT LOCK;
--杀掉会话，防止锁账号之前会话的DML
select 'ALTER SYSTEM KILL SESSION  '''||SID||','||SERIAL#||''' IMMEDIATE;' ,program from v$session where username='MONKEY_DEV';

　验证：

SELECT MAX(ADD_DATE) FROM MONKEY.NORMAL_TO_PAR;

3.打开并行

select * from v$parameter where name like '%parallel%'; 
alter system set parallel_max_servers=30;
alter system set parallel_servers_target=30;

4.新建DIRECTORY（使用以前的也可以）

SELECT * FROM DBA_DIRECTORIES;
CREATE DIRECTORY MYDIR AS '/Data/expdp/monkey'；

5.导出旧数据

导出全部数据

vi MONKEY_NORMAL_TO_PAR_ALL.par

DIRECTORY=MYDIR
CONTENT=DATA_ONLY
DUMPFILE=MONKEY_NORMAL_TO_PAR_ALL_%U.dmp
LOGFILE=MONKEY_NORMAL_TO_PAR_ALL.log
TABLES=MONKEY.NORMAL_TO_PAR
COMPRESSION=ALL
FILESIZE=4G
CLUSTER=N
PARALLEL=6

导出部分数据

vi MONKEY_NORMAL_TO_PAR_TENNAGERS.par

DIRECTORY=MYDIR
CONTENT=DATA_ONLY
DUMPFILE=MONKEY_NORMAL_TO_PAR_TENNAGERS_%U.dmp
LOGFILE=MONKEY_NORMAL_TO_PAR_TENNAGERS.log
TABLES=MONKEY.NORMAL_TO_PAR
QUERY=MONKEY.NORMAL_TO_PAR:" WHERE AGE <=18 "
FILESIZE=4G
CLUSTER=N
PARALLEL=6

vi MONKEY_NORMAL_TO_PAR_THISYEAR.par

DIRECTORY=MYDIR
CONTENT=DATA_ONLY
DUMPFILE=MONKEY_NORMAL_TO_PAR_THISYEAR_%U.dmp
LOGFILE=MONKEY_NORMAL_TO_PAR_THISYEAR.log
TABLES=MONKEY.NORMAL_TO_PAR
QUERY=MONKEY.NORMAL_TO_PAR:" WHERE ADD_DATE >TO_DATE('2020/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')"
FILESIZE=4G
CLUSTER=N
PARALLEL=6

expdp monkey/xxxxx parfile=MONKEY_NORMAL_TO_PAR_ALL.par

6.新建分区表

普通分区表

CREATE TABLE MONKEY.NORMAL_TO_PAR_BAK(ID NUMBER,NAME VARCHAR2 (50),AGE NUMBER,ADD_DATE DATE)
TABLESPACE USERS
PARTITION BY RANGE (ADD_DATE)
(PARTITION DCS_P202001 VALUES LESS THAN(TO_DATE (' 2020-02-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')),
 PARTITION DCS_P202002 VALUES LESS THAN(TO_DATE (' 2020-03-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')),
 PARTITION DCS_P202003 VALUES LESS THAN(TO_DATE (' 2020-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')),
 PARTITION DCS_P202004 VALUES LESS THAN(TO_DATE (' 2020-05-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')),
 PARTITION DCS_P202005 VALUES LESS THAN(TO_DATE (' 2020-06-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')),
 PARTITION DCS_P202006 VALUES LESS THAN(TO_DATE (' 2020-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')),
 PARTITION DCS_MAX VALUES LESS THAN (MAXVALUE))
COMPRESS FOR OLTP        --報表庫需要壓縮,生產庫需要去掉此參數
ENABLE ROW MOVEMENT;

自动分区表

CREATE TABLE MONKEY.NORMAL_TO_PAR_BAK(ID NUMBER,NAME VARCHAR2 (50),AGE NUMBER,ADD_DATE DATE)
TABLESPACE USERS
PARTITION BY RANGE (ADD_DATE)
INTERVAL( NUMTOYMINTERVAL (1, 'MONTH'))
(PARTITION DCS_P202001 VALUES LESS THAN(TO_DATE (' 2020-02-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')))
COMPRESS FOR OLTP        --報表庫需要壓縮,生產庫需要去掉此參數
ENABLE ROW MOVEMENT;

创建分区表时，要允许表行迁移，防止有可能的分区栏位更新，同时如果分区栏位可能会插入空值，则建议使用普通的分区表

7.导入数据

vi MONKEY_NORMAL_TO_PAR_20200101_IMP.par

DIRECTORY=MYDIR
DUMPFILE=MONKEY_NORMAL_TO_PAR_ALL_%U.dmp
LOGFILE=MONKEY_NORMAL_TO_PAR_20200101_IMP.log
REMAP_TABLE=MONKEY.NORMAL_TO_PAR:NORMAL_TO_PAR_BAK
QUERY=MONKEY.NORMAL_TO_PAR:" WHERE ADD_DATE >TO_DATE('2020/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')"
PARALLEL=4

impdp monkey/**** parfile=MONKEY_NORMAL_TO_PAR_20200101_IMP.par 

8.记录下原表索引创建脚本

9.重命名索引名，约束名，表名

SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' RENAME TO '||INDEX_NAME||'_OLD;' FROM DBA_INDEXES WHERE OWNER='MONKEY' AND TABLE_NAME='NORMAL_TO_PAR';
ALTER TABLE MONKEY.NORMAL_TO_PAR RENAME CONSTRAINT PK_NORMAL_TO_PAR TO PK_NORMAL_TO_PAR_OLD;
ALTER TABLE MONKEY.NORMAL_TO_PAR RENAME TO NORMAL_TO_PAR_OLD;

10.重命名新建表名

ALTER TABLE MONKEY.NORMAL_TO_PAR_BAK RENAME TO NORMAL_TO_PAR;

11.创建索引和约束，表赋权

CREATE INDEX MONKEY.IX_NORMAL_TO_PAR_NAME ON MONKEY.NORMAL_TO_PAR(NAME) TABLESPACE USERS LOCAL PARALLEL 6 ;
ALTER INDEX MONKEY.IX_NORMAL_TO_PAR_NAME NOPARALLEL;
ALTER TABLE MONKEY.NORMAL_TO_PAR ADD(CONSTRAINT PK_NORMAL_TO_PAR PRIMARY KEY(ID));
GRANT SELECT ON MONKEY.NORMAL_TO_PAR TO MONKEY_DEV;
GRANT SELECT ON MONKEY.NORMAL_TO_PAR TO SYN_MONKEY;

12.重建TRIGGER

DROP TRIGGER MONKEY.TRI_NORMAL_TO_PAR;
CREATE TRIGGER MONKEY.TRI_NORMAL_TO_PAR
   BEFORE INSERT
   ON MONKEY.NORMAL_TO_PAR
   FOR EACH ROW
BEGIN
   SELECT MONKEY.SEQ_NORMAL_TO_PAR_ID.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

13.重编译USERBY

14.收集统计信息

execute dbms_stats.gather_table_stats(ownname => 'MONKEY',tabname => 'NORMAL_TO_PAR' ,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'for all  columns size auto' ,cascade => true ,degree=>6);

15.解锁账号

ALTER USER MONKEY_DEV ACCOUNT UNLOCK;

16.关闭并行

alter system set parallel_servers_target=0;
alter system set parallel_max_servers=0;

17.通知测试