oralce修改已有的表为分区表

1.利用原有表创建表——>对此新表创建分区,把原有表的数据都拷贝到新表当中，修改原来表的名称为其他名称，把新创建的表的名称改为原来表名称。
create table MESSAGE
(
  ID      NUMBER,
  PK_ID   NUMBER,
  CONTENT VARCHAR2(50)
)
insert into message values(99,99,'m99');
insert into message values(199,199,'m199');
insert into message values(299,299,'m299');
insert into message values(399,399,'m399');
insert into message values(1,1,'m1');

create table MESSAGE_NEW
(
  ID      NUMBER,
  PK_ID   NUMBER,
  CONTENT VARCHAR2(50)
)
partition by range(ID)
(
partition p100 values less than (100),
partition p200 values less than (200),
partition p300 values less than (300),
partition p400 values less than (400)
)

insert into message_new(id,pk_id,content)  select o.id,o.pk_id,o.content from message o;

rename message to  message_old;

rename message_new to  message;

select * from message partition(p100)

优点：方法简单易用，由于采用DDL语句，不会产生UNDO，且只产生少量REDO，效率相对较高，而且建表完成后数据已经在分布到各个分区中了。

不足：对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性，在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失，如果要保证一致性，需要在执行完语句后对数据进行检查，而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。

适用于修改不频繁的表，在闲时进行操作，表的数据量不宜太大。

 

2.使用交换分区方法。
select * from message t
create table MESSAGE
(
  ID      NUMBER,
  PK_ID   NUMBER,
  CONTENT VARCHAR2(50)
)
insert into message values(99,99,'m99');
insert into message values(199,199,'m199');
insert into message values(299,299,'m299');
insert into message values(399,399,'m399');
insert into message values(1,1,'m1');

create table MESSAGE_NEW
(
  ID      NUMBER,
  PK_ID   NUMBER,
  CONTENT VARCHAR2(50)
)
partition by range(ID)
(
partition p100 values less than (100),
partition p200 values less than (200),
partition p300 values less than (300),
partition p400 values less than (400)
)

delete from message where id>100;

ALTER TABLE MESSAGE_NEW EXCHANGE PARTITION p100 WITH TABLE MESSAGE;

insert into message_new(id,pk_id,content)  select o.id,o.pk_id,o.content from message o;

rename message to  message_old;

rename message_new to  message;

select * from message partition(p100)

不足：分区表中只能够有一个分区，或者是多个分区但是只有一个分区中有数据。

 

手动的为表添加分区：

alter table
TB_REAL_DATA
add partition P201303
values less than (TO_DATE(' 2013-03-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace NEWCGQ;

 

动态的为表添加分区：

create or replace procedure pro_add_partition(n number)

as
v_sql varchar2(4000);

begin

--v_sql:='alter table message add partition p500 values less than (500) tablespace users';
v_sql:='alter table message add partition p'||n||' values less than ('||n||') tablespace users';

execute immediate v_sql;

end;