将非分区表转换为分区表

很多数据库在设计时未能很好的规划数据库的容量, 导致在某些情况下, 导致一个表中存放了数百万, 数千万甚至上亿条记录, 维护起来非常的不便, 下面介绍一种将非分区表转换为分区表的方法.[@more@]testtb_nopart是一个非分区表, 基本信息如下:

SQL> desc testtb_nopart Name Null? Type ----------------------------------------- -------- ---------------------------- TIMESTAMP DATE OWNER VARCHAR2(30) ACTION VARCHAR2(6) OBJECT_NAME VARCHAR2(128) MESSENG VARCHAR2(169) SQL> select to_char(timestamp, 'mm-yyyy'), count(*) from testtb_nopart group by to_char(timestamp, 'mm-yyyy'); TO_CHAR COUNT(*) ------- ---------- 12-2005 2058125 11-2005 1363431 08-2005 150951 10-2005 628149 07-2005 1006340 09-2005 730410 6 rows selected.

创建分区表testtb_part, 结构和testtb_nopart一致.

create table testtb_part (timestamp date, owner varchar2(30), action varchar2(6), object_name varchar2(128) , messeng varchar2(169)) tablespace testts partition by range(timestamp)( partition rest values less than (maxvalue))

交换testtb_nopart和testtb_part中part分区的数据

SQL> alter table testtb_part exchange partition rest with table testtb_nopart; Table altered. SQL> select to_char(timestamp, 'mm-yyyy'), count(*) from testtb_nopart group by to_char(timestamp, 'mm-yyyy'); no rows selected SQL> c/nopart/part/ 1* select to_char(timestamp, 'mm-yyyy'), count(*) from testtb_part group by to_char(timestamp, 'mm-yyyy') SQL> / TO_CHAR COUNT(*) ------- ---------- 12-2005 2058125 11-2005 1363431 08-2005 150951 10-2005 628149 07-2005 1006340 09-2005 730410 6 rows selected.

testtb_nopart中大约有50万条记录, 占用600m左右空间, 交换大约用了不到30秒钟, testtb_part和testtb_nopart所在的表空间也没有明显增长.

对rest分区进行拆分

SQL> alter table testtb_part split partition rest at (to_date('200508','yyyymm')) 2 into (partition part08, partition rest); Table altered. SQL> alter table testtb_part split partition rest at (to_date('200509','yyyymm')) 2 into (partition part08, partition rest); Table altered. SQL> alter table testtb_part split partition rest at (to_date('200510','yyyymm')) 2 into (partition part09, partition rest); Table altered. SQL> alter table testtb_part split partition rest at (to_date('200511','yyyymm')) 2 into (partition part10, partition rest); Table altered. SQL> alter table testtb_part split partition rest at (to_date('200512','yyyymm')) 2 into (partition part11, partition rest); Table altered. SQL> alter table testtb_part split partition rest at (to_date('200601','yyyymm')) 2 into (partition part12, partition rest); Table altered.

最后可以重命名表, 使得转换过程不影响用户程序.

alter table testtb_part rename to testtb_nopart

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/262387/viewspace-865781/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/262387/viewspace-865781/