oracle表分区

1、by range

示例：

--手台轨迹表
CREATE TABLE HandSetTrack(
    recordID      VARCHAR2(36)     NOT NULL,
    HandSetID     VARCHAR2(36)     NOT NULL,
    longitude     VARCHAR2(128)    NOT NULL,
    latitude      VARCHAR2(128)    NOT NULL,
    recordTime    DATE             DEFAULT sysdate,
    CONSTRAINT PK_HandSetTrack PRIMARY KEY (recordID)
)
partition by range(recordTime)
interval(numtoymInterval(1, 'MONTH'))
(
   partition handset_trace_deadData values less than(to_date('2017-07-25', 'yyyy-mm-dd')) 
)
;

2、by hash

示例：

--车台轨迹表
CREATE TABLE CarSetTrack(
    recordID      VARCHAR2(36)     NOT NULL,
    CarSetID      VARCHAR2(36)     NOT NULL,
    longitude     VARCHAR2(128)    NOT NULL,
    latitude      VARCHAR2(128)    NOT NULL,
    recordTime    DATE             DEFAULT sysdate,
    CONSTRAINT PK_CarSetTrack PRIMARY KEY (recordID)
)
partition by hash(CarSetID)
(
  partition carset_trace_001,
  partition carset_trace_002,
  partition carset_trace_003,
  partition carset_trace_004,
  partition carset_trace_005,
  partition carset_trace_006,
  partition carset_trace_007,
  partition carset_trace_008,
  partition carset_trace_009,
  partition carset_trace_010
)
;

3、查看分区

select table_name,partition_name from user_tab_partitions；

SELECT * FROM ALL_PART_TABLES WHERE TABLE_NAME = 'CARSETTRACK';

4、删除分区

ALTER TABLE HANDSETTRACK DROP PARTITION PART_01;

5、删除表

drop table HandSetTrack;
drop table CarSetTrack;
purge recyclebin;