Oracle 普通table 轉換為partition table

1.建立eg table
SQL> create table pp (id int,dt date);

Table created.

SQL> create index pp_index on pp(id);

index created.

begin
for i in 1..10000000 loop
if mod(i,3)=1 then
   insert into pp values(i,to_date('2013/6/6','yyyy/mm/dd'));
elsif mod(i,3)=2 then
   insert into pp values(i,to_date('2014/6/6','yyyy/mm/dd'));
else
   insert into pp values(i,to_date('2015/5/5','yyyy/mm/dd'));
end if;
end loop;
commit;
end;
/

現在將pp錶轉換為partition table.

2.create partition table
SQL> edit
Wrote file afiedt.buf

  1  create table pp_copy
  2  (id int,dt date)
  3  partition by range(dt)
  4  (partition p1 values less than(to_date('2014/01/01','yyyy/mm/dd')) tablespace users,
  5   partition p2 values less than(to_date('2015/01/01','yyyy/mm/dd')) tablespace users,
  6   partition p3 values less than(to_date('2016/01/01','yyyy/mm/dd')) tablespace users,
  7*  partition p4 values less than(maxvalue) tablespace users)
SQL> /

SQL> insert into pp_copy select * from pp;

10000000 rows created.

SQL> commit;

Commit complete.

SQL> drop table pp purge;

Table dropped.

SQL> alter table pp_copy rename to pp;

Table altered.

SQL> create index pp_index on pp(id) tablespace users local;

Index created.

如果有package，procedure,view則需要重新編譯。
alter package package_name compile;
alter procedure procedure_name compile;
alter view view_name compile;