将普通表转换成分区表有4种方法:
- Export/import method
- Insert with a subquery method
- Partition exchange method
- DBMS_REDEFINITION
1 插入: Insert with a subquery method
1.1 Oracle 11g的Interval
在11g里的Interval创建,这种方法对没有写全的分区会自动创建。 比如我这里只写了1月日期,如果插入的数据有其他月份的,会自动生成对应的分区。
CREATE TABLE DCM_CWS_CASE_HISTORY_OLD1 PARTITION BY RANGE (starttime) INTERVAL (NUMTOYMINTERVAL(1, 'YEAR'))(
PARTITION part1 VALUES LESS THAN ( TO_DATE ('01/01/2012', 'MM/DD/YYYY') )
) AS SELECT * FROM DCM_CWS_CASE_HISTORY;
SQL> select table_name,partition_name from user_tab_partitions where table_name='INTERVALDAVE';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
INTERVALDAVE PART1
INTERVALDAVE SYS_P24
INTERVALDAVE SYS_P25
INTERVALDAVE SYS_P26
INTERVALDAVE SYS_P33
INTERVALDAVE SYS_P27
INTERVALDAVE SYS_P28
1.2 Oracle 10g 版本
在10g里面,我需要写全所有的分区。
sql> create table pdba (id, time) partition by range (time)
2 (partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),
3 partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),
4 partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),
5 partition p4 values less than (maxvalue))
6 as select id, time_fee from dba;
表已创建。
SQL> select table_name,partition_name from user_tab_partitions where table_name='PDBA';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
PDBA P1
PDBA P2
PDBA P3
PDBA P4
现在分区表已经建好了,但是表名不一样,需要用rename对表重命名一下:
SQL> rename dba to dba_old;
表已重命名。
SQL> rename pdba to dba;
表已重命名。
SQL> select table_name,partition_name from user_tab_partitions where table_name='DBA';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
DBA P1
DBA P2
DBA P3
DBA P4
修改表分区
alter table DCM_CWS_CASE_HISTORY_OLD drop partition p14;
alter table DCM_CWS_CASE_HISTORY_OLD add PARTITION p15 VALUES LESS THAN (MAXVALUE) );
普通表不能直接添加分区信息