PLSQL里操作,直接上代码:
- --目的:用表分区转换大表数据,演示中只是用5000条数据;
- --建表T
- create table t(id number,name varchar2(10));
- insert into t select rownum+4,'1,2,3,4' from dual connect by rownum<=5000;
- commit;
- select count(1) from t ;
- --创建表空间
- create tablespace ts_1 datafile 'E:\oracle\product\10.2.0\oradata\orcl\ts_1.dbf' size 50m reuse;
- create tablespace ts_2 datafile 'E:\oracle\product\10.2.0\oradata\orcl\ts_2.dbf' size 50m reuse;
- create tablespace ts_3 datafile 'E:\oracle\product\10.2.0\oradata\orcl\ts_3.dbf' size 50m reuse;
- --创建新表及分区
- create table t_new partition by range(id)(
- partition p1 values less than (2000) tablespace ts_1,
- partition p2 values less than (4000) tablespace ts_2,
- partition p3 values less than (maxvalue) tablespace ts_3)
- as select * from t;
- --删除老表并更换名字
- truncate table t;
- drop table t;
- alter table t_new rename to t;
- --检查各分区的数据
- select count(*) from t partition (p1);
- select count(*) from t partition (p2);
- select count(*) from t partition (p3);
本文转自danni505 51CTO博客,原文链接:http://blog.51cto.com/danni505/1163711,如需转载请自行联系原作者