ORACLE11g自动创建分区

----分区查询----
     SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='T_APP_FAULT_HANDLE_GIS';
     
     select count(*) from pssc_mid.t_app_fault_handle_gis partition(SYS_P532); 
--*1--Oracle11g提供自动创建分区的功能；
CREATE TABLE pssc_mid.t_temp_1 (MOBILE_PHONE,X,Y,RECODE_TIME,CREATE_TIME,MAC,GPS_STATUS) 
PARTITION BY RANGE (CREATE_TIME)--仍然以时间为划分节点
interval(numtoyminterval(1,'month'))--时间间隔设置为1个月
store in(PSSC_MID_DATA)--设置存储表空间PSSC_MID_DATA
(partition t0 values less than(to_date('2019/01/01 00:00:00','yyyy/mm/dd hh24:mi:ss')))--初始分区p0
AS SELECT MOBILE_PHONE,X,Y,RECODE_TIME,CREATE_TIME,MAC,GPS_STATUS FROM pssc_mid.t_app_fault_handle_gis where 1=2;

select sysdate from dual;


alter session force parallel dml parallel 8;
alter session force parallel query parallel 8;
alter session force parallel ddl parallel 4;
 
--4--执行重定义操作
begin
  dbms_redefinition.start_redef_table('pssc_mid','t_app_fault_handle_gis','t_temp_1',null,dbms_redefinition.cons_use_rowid);
end;
--说明：dbms_redefinition.start_redef_table('用户名','源表','中间表',映射关系(可选),方式(主键或Rowid) );
--5--迁移关联数据,如索引、触发器等等
declare
 num_errors pls_integer;
begin
  dbms_redefinition.copy_table_dependents
  (uname => 'pssc_mid',
   orig_table => 't_app_fault_handle_gis',
   int_table => 't_temp_1',
   copy_indexes => 0,
   copy_triggers => TRUE,
   copy_constraints => FALSE,
   copy_privileges => TRUE,
   ignore_errors => FALSE,
   num_errors => num_errors,
   copy_statistics => FALSE
  );
end;
--6--执行数据同步
begin
  dbms_redefinition.sync_interim_table('pssc_mid','t_app_fault_handle_gis','t_temp_1');
end;  
--7--完成重定义
begin
  dbms_redefinition.finish_redef_table('pssc_mid','t_app_fault_handle_gis','t_temp_1');
end;