day13_普通表转换成分区表——具体实现
set long 99999999
select dbms_metadata.get_ddl('TABLE','YX_PRIZE_RECORD','HDPLAT_NEW') from dual;
CREATE TABLE lipengfei
(
ID VARCHAR2(32) unique,
PLATFORM_ID VARCHAR2(32),
AC_ID VARCHAR2(32),
TYPE_ID VARCHAR2(32),
USER_ID VARCHAR2(32),
PRIZE_ID VARCHAR2(32),
PRIZE_LEVEL VARCHAR2(3),
WIN_DATE DATE,
IS_WIN VARCHAR2(3),
IS_GET VARCHAR2(3),
GET_TIME DATE,
RAND_CODE VARCHAR2(32),
PRIZE_ONLY VARCHAR2(40),
PUB_DATE DATE,
USER_MAIL_ID VARCHAR2(32),
PRO_CODE VARCHAR2(20),
OPP_ID VARCHAR2(32),
TARGET_USER VARCHAR2(50)
)
PARTITION BY LIST (PRO_CODE)
(
PARTITION P_098 VALUES ('098'),
PARTITION P_011 VALUES ('098_011'),
PARTITION P_013 VALUES ('098_013'),
PARTITION P_018 VALUES ('098_018'),
PARTITION P_019 VALUES ('098_019'),
PARTITION P_010 VALUES ('098_010'),
PARTITION P_091 VALUES ('098_091'),
PARTITION P_090 VALUES ('098_090'),
PARTITION P_097 VALUES ('098_097'),
PARTITION P_031 VALUES ('098_031'),
PARTITION P_034 VALUES ('098_034'),
PARTITION P_036 VALUES ('098_036'),
PARTITION P_030 VALUES ('098_030'),
PARTITION P_038 VALUES ('098_038'),
PARTITION P_075 VALUES ('098_075'),
PARTITION P_017 VALUES ('098_017'),
PARTITION P_076 VALUES ('098_076'),
PARTITION P_071 VALUES ('098_071'),
PARTITION P_074 VALUES ('098_074'),
PARTITION P_051 VALUES ('098_051'),
PARTITION P_059 VALUES ('098_059'),
PARTITION P_050 VALUES ('098_050'),
PARTITION P_083 VALUES ('098_083'),
PARTITION P_081 VALUES ('098_081'),
PARTITION P_085 VALUES ('098_085'),
PARTITION P_086 VALUES ('098_086'),
PARTITION P_079 VALUES ('098_079'),
PARTITION P_084 VALUES ('098_084'),
PARTITION P_087 VALUES ('098_087'),
PARTITION P_070 VALUES ('098_070'),
PARTITION P_088 VALUES ('098_088'),
PARTITION P_089 VALUES ('098_089'),
PARTITION P_NULL VALUES (default)
);
2、创建一个新表lipengfei，与YX_PRIZE_RECORD表结构一模一样,lipengfei是分区表，但是没有数据！ Alter table lipengfei NOLOGGING; 操作lipengfei表不记录日志，加快操作速度！
3、编写存储过程，从YX_PRIZE_RECORD_HISTORY查询数据insert到lipengfei表，批量提交！
4、alter table YX_PRIZE_RECORD_HISTORY rename to YX_PRIZE_RECORD_HISTORY_BAK;(原历史表保留，等改造成功后，再删除！)
5、alter table lipengfei rename to YX_PRIZE_RECORD_HISTORY;
6、create index I_AC_ID_IS_GET_PRO_CODE on YX_PRIZE_RECORD_HISTORY(ac_id,is_get,pro_code) local;
7、 Alter table YX_PRIZE_RECORD_HISTORY LOGGING; 操作A表记录日志！
mkdir -p /home/oracle/awr_rpt/cron
mkdir -p /yh_exp/cron/
chmod 777 /yh_exp/cron/
chmod 777 /home/oracle/awr_rpt/cron
cd /home/oracle/awr_rpt/cron
vi lipengfei.sh
#!/bin/sh
DATE="`date +%Y-%m-%d`"
EXP_D="/yh_exp/cron"
ORA_BIN="/oracle/app/oracle/product/10.2.0/db_1/bin"
${ORA_BIN}/sqlplus scott/lipengfei @${EXP_D}/lipengfei.sql >/home/oracle/awr_rpt/cron/lipengfei_${DATE}.log 2>&1
cd /yh_exp/cron
vi lipengfei.sql
set serveroutput on size 100000;
DECLARE
CURSOR cur IS
SELECT * FROM YX_PRIZE_RECORD_HISTORY;
TYPE rec IS TABLE OF YX_PRIZE_RECORD_HISTORY%ROWTYPE;
recs rec;
BEGIN
OPEN cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT
INTO recs LIMIT 10000;
FORALL i IN 1 .. recs.COUNT
INSERT INTO lipengfei VALUES recs (i);
COMMIT;
EXIT WHEN cur%NOTFOUND;
END LOOP;
CLOSE cur;
END;
/
commit;
exit;
EOF
nohup sh /home/oracle/awr_rpt/cron/lipengfei.sh &
18573695条【4分30秒左右】
