一、需求
配合开发人员，对业务临时表进行分区改造(业务认为的临时表，只需要保留近一月数据，并非oracle临时表类型)
二、如下记录完整过程
开发需求 TS_PM 以time_key分区 1.沟通明确方案需求 发现开发提供的分区字段，类型是number,需要按天分区 也就是说，需要达到开发的需求，我们需要创建一年365个小分区(列表，后续维护一年后，也需要添加365个分区)!!! 咨询开发人员分区的目的是啥，发现只是为了删除数据快一点，业务上的临时表，只存储1个月的数据，并且分区字段可以改， 不一定需要time_key number,也可以是start_time,这样就可以尝试使用11g新特效扩展分区，也就是所我创建一个分区表，根据插入的数据，自动间隔1天添加新分区，运维省事了。 沟通明确后，整体干活的思路有了：
1)创建分区表,字段进行分区改造
2)插入开发人员需要的数据,本次只需要10月1日后的数据
3)rename 旧表,rename 新分区表至业务表名称，达到分区改造的目的
4)业务测试读写无异常后,进行后续处理,创建分区本地索引、表的列注释信息、表的约束信息
5)对历史旧表，进行expdp导出备份后，drop删除清理回收空间
2.操作步骤 2.1 创建分区表 SQL>select start_time,count(*) from TS_PM sample block(10) group by start_time order by 1 ; 查询确认分区字段查询出的数值，简要查询 2018-7-3 xxx 确认分区字段类型 START_TIME DATE列 ，间隔日分区。 使用11g新特效， https://www.cnblogs.com/yuxiaole/p/9809294.html NUMTODSINTERVAL(1, 'day') 创建间隔分区语法 【 create table test_part ( ID NUMBER(20) not null, REMARK VARCHAR2(1000), create_time DATE ) PARTITION BY RANGE (CREATE_TIME) INTERVAL (NUMTODSINTERVAL(1, 'day')) (partition part_t01 values less than(to_date('2018-11-12', 'yyyy-mm-dd')));】 -- Create table 实际开发人员提出需要10月1日后续的数据(本次配置第一个分区是9月30日,用于存储历史数据保留一个分区) create table TS_PM_tmp ( power_cons_monitor_id VARCHAR2(36 CHAR) not null, start_time DATE, end_time DATE, time_key NUMBER(10) not null, ······· ) tablespace BSP PARTITION BY RANGE (start_time) INTERVAL (NUMTODSINTERVAL(1, 'day')) (partition part_t01 values less than(to_date('2019-09-30', 'yyyy-mm-dd'))) ; 插入数据
insert into TS_tmp select * from TS_PM where time_key='20191001';
insert into TS_tmp select * from TS_PM where time_key='20191002';
insert into TS_tmp select * from TS_PM where time_key='20191003';
insert into TS_tmp select * from TS_PM where time_key='20191004';
insert into TS_tmp select * from TS_PM where time_key='20191005';
insert into TS_tmp select * from TS_PM where time_key in('20191006','20191007','20191008','20191009','20191010');
insert into TS_tmp select * from TS_PM where time_key
in('20191011','20191012','20191013','20191014','20191015','20191016','20191017');
commit;
Rename操作 alter table TS_PM rename to TS_old; alter table TS_tmp rename to TS_PM; 业务人员反馈无异常后。 创建索引 名称更换后，加上local参数即可 create index BSP.POWER_CONS_MONITOR_PM_5_new on bsp.TS_PM (START_TIME, STATS_CYCLE, OBJ_TYPE, ORG_ID, OIL_AMOUNT_DIFF, GAS_AMOUNT_DIFF, ELEC_AMOUNT_DIFF) tablespace BSP local; 添加主键约束 报错 ORA-14039 14039, 00000, "partitioning columns must form a subset of key columns of a UNIQUE index" *Cause: User attempted to create a UNIQUE partitioned index whose partitioning columns do not form a subset of its key columns which is illegal *Action: If the user, indeed, desired to create an index whose partitioning columns do not form a subset of its key columns, it must be created as non-UNIQUE; otherwise, correct the list of key and/or partitioning columns to ensure that the index' partitioning columns form a subset of its key columns 唯一索引中无分区列，无法创建唯一索引，因此修改主键约束列,添加分区列 alter table TS_PM add constraint POWER_MONITOR_PM_new primary key (POWER_CONS_MONITOR_ID,START_TIME) using index local tablespace BSP ; -- Add comments to the columns comment on column TS_tmp.time_key is '如果是实时统计则该字段无值 如果是日统计该数值20150722 如果是周统计该数值一周的第一天 如果是月统计该数值201507 如果是季度统计该数值是季度的第一个月 如果是年统计该数值2015'; 对历史表进行导出备份
$ expdp "'/ as sysdba'" directory=HISDATA
dumpfile=TS_OLD%U.dump
logfile=TS_OLD_20191016.log
tables=bsp.TS_OLD cluster=n parallel=4 COMPRESSION=all
*********************************************************************
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
/home/oracle/rmanvplus/hisdata/TS_OLD01.dump
/home/oracle/rmanvplus/hisdata/TS_OLD02.dump
/home/oracle/rmanvplus/hisdata/TS_OLD03.dump
/home/oracle/rmanvplus/hisdata/TS_OLD04.dump
$ mv TS_OLD01.dump TS_OLD01.dump_20191016.bak
$ mv TS_OLD02.dump TS_OLD02.dump_20191016.bak
$ mv TS_OLD03.dump TS_OLD03.dump_20191016.bak
$ mv TS_OLD04.dump TS_OLD04.dump_20191016.bak
SQL> drop table bsp.TS_OLD purge;
校验
SQL>select index_name,PARTITION_NAME,HIGH_VALUE,STATUS,TABLESPACE_NAME from dba_ind_partitions where index_owner='BSP' and index_name='POWER_CONS_MONITOR_PM_5_NEW';
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS TABLESPACE_NAME
------------------------------ ------------------------------ -------------------------------------------------------------------------------- -------- ------------------------------
POWER_CONS_MONITOR_PM_5_NEW PART_T01 TO_DATE(' 2019-09-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USABLE BSP
POWER_CONS_MONITOR_PM_5_NEW SYS_P25121 TO_DATE(' 2019-10-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USABLE BSP
POWER_CONS_MONITOR_PM_5_NEW SYS_P25122 TO_DATE(' 2019-10-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USABLE BSP
POWER_CONS_MONITOR_PM_5_NEW SYS_P25123 TO_DATE(' 2019-10-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USABLE BSP
POWER_CONS_MONITOR_PM_5_NEW SYS_P25124 TO_DATE(' 2019-10-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USABLE BSP
POWER_CONS_MONITOR_PM_5_NEW SYS_P25125 TO_DATE(' 2019-10-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USABLE BSP
POWER_CONS_MONITOR_PM_5_NEW SYS_P25126 TO_DATE(' 2019-10-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USABLE BSP
POWER_CONS_MONITOR_PM_5_NEW SYS_P25127 TO_DATE(' 2019-10-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USABLE BSP
POWER_CONS_MONITOR_PM_5_NEW SYS_P25128 TO_DATE(' 2019-10-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USABLE BSP
POWER_CONS_MONITOR_PM_5_NEW SYS_P25129 TO_DATE(' 2019-10-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USABLE BSP
POWER_CONS_MONITOR_PM_5_NEW SYS_P25130 TO_DATE(' 2019-10-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USABLE BSP
POWER_CONS_MONITOR_PM_5_NEW SYS_P25131 TO_DATE(' 2019-10-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USABLE BSP
POWER_CONS_MONITOR_PM_5_NEW SYS_P25132 TO_DATE(' 2019-10-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USABLE BSP
POWER_CONS_MONITOR_PM_5_NEW SYS_P25133 TO_DATE(' 2019-10-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USABLE BSP
POWER_CONS_MONITOR_PM_5_NEW SYS_P25134 TO_DATE(' 2019-10-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USABLE BSP
POWER_CONS_MONITOR_PM_5_NEW SYS_P25135 TO_DATE(' 2019-10-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USABLE BSP
16 rows selected.
SQL>select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME from dba_tab_partitions where TABLE_OWNER='BSP' and TABLE_NAME='TS_PM';
TABLE_OWNE TABLE_NAME PARTITION_ HIGH_VALUE TABLESPACE
---------- ------------------------------ ---------- -------------------------------------------------------------------------------- ----------
BSP TS_PM PART_T01 TO_DATE(' 2019-09-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA BSP
BSP TS_PM SYS_P25041 TO_DATE(' 2019-10-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA BSP
BSP TS_PM SYS_P25042 TO_DATE(' 2019-10-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA BSP
BSP TS_PM SYS_P25043 TO_DATE(' 2019-10-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA BSP
BSP TS_PM SYS_P25044 TO_DATE(' 2019-10-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA BSP
BSP TS_PM SYS_P25045 TO_DATE(' 2019-10-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA BSP
BSP TS_PM SYS_P25046 TO_DATE(' 2019-10-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA BSP
BSP TS_PM SYS_P25047 TO_DATE(' 2019-10-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA BSP
BSP TS_PM SYS_P25048 TO_DATE(' 2019-10-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA BSP
BSP TS_PM SYS_P25049 TO_DATE(' 2019-10-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA BSP
BSP TS_PM SYS_P25050 TO_DATE(' 2019-10-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA BSP
BSP TS_PM SYS_P25051 TO_DATE(' 2019-10-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA BSP
BSP TS_PM SYS_P25052 TO_DATE(' 2019-10-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA BSP
BSP TS_PM SYS_P25053 TO_DATE(' 2019-10-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA BSP
BSP TS_PM SYS_P25054 TO_DATE(' 2019-10-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA BSP
BSP TS_PM SYS_P25055 TO_DATE(' 2019-10-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA BSP
16 rows selected.