由于数据库是Oracle 10g，不是11g，无法支持interval分区，只能多创建几个未来的分区了。
表创建好之后，就导数据流，由于该表不算太大，500多兆的插入还是可以接受的。
1 insert into my_p_siteaccesslog select * from P_SITEACCESSLOG ;
2 commit;
下一步使用分区切换技术，将旧的数据搬走（不是删掉）
创建用于存放2008年数据大表
01 create table my_p_siteaccesslog_2008
02 (
03;;logIDvarchar(40) not null,
04;;infoType;;;;;varchar(20) default 'site',
05;;siteId; varchar(30),
06;;columnId;;;;;varchar(30),
07;;infoId; varchar(30),
08;;url;;varchar(300),
09;;location;;;;;varchar(80),
10;;action; varchar(20),
11;;accessDate;;;varchar(10),
12;;accessTime;;;varchar(8),
13;;sessionID;;;;varchar(90),
14;;userName;;;;;varchar(30),
15;;userBrowser;;varchar(50),
16;;userOS; varchar(30),
17;;userIp; varchar(30),
18;;localization;varchar(100)
19 );
使用分区切换，将2008年的数据切换出来：
1 ALTER TABLE my_p_siteaccesslog EXCHANGE PARTITION pbf2009 WITH TABLE my_p_siteaccesslog_2008;
创建专门用于存放2009年数据的分区表：
01 create table my_p_siteaccesslog_2009
02 (
03;;logIDvarchar(40) not null,
04;;infoType;;;;;varchar(20) default 'site',
05;;siteId; varchar(30),
06;;columnId;;;;;varchar(30),
07;;infoId; varchar(30),
08;;url;;varchar(300),
09;;location;;;;;varchar(80),
10;;action; varchar(20),
11;;accessDate;;;varchar(10),
12;;accessTime;;;varchar(8),
13;;sessionID;;;;varchar(90),
14;;userName;;;;;varchar(30),
15;;userBrowser;;varchar(50),
16;;userOS; varchar(30),
17;;userIp; varchar(30),
18;;localization;varchar(100)
19 )
20 partition by range (accessdate)
21 (
22;;partition p200901 values less than ('2009-02-01'),
23;;partition p200902 values less than ('2009-03-01'),
24;;partition p200903 values less than ('2009-04-01'),
25;;partition p200904 values less than ('2009-05-01'),
26;;partition p200905 values less than ('2009-06-01'),
27;;partition p200906 values less than ('2009-07-01'),
28;;partition p200907 values less than ('2009-08-01'),
29;;partition p200908 values less than ('2009-09-01'),
30;;partition p200909 values less than ('2009-10-01'),
31;;partition p200910 values less than ('2009-11-01'),
32;;partition p200911 values less than ('2009-12-01'),
33;;partition p200912 values less than ('2010-01-01')
34 )
令人非常郁闷的是Oracle竟然不支持两个分区表之间的分区切换，一定要借助一个中间表，这个功能连SQL Server都有了啊。
于是创建一个专门用于切换的中间表：
01 create table my_p_siteaccesslog_exchange
02 (
03;;logIDvarchar(40) not null,
04;;infoType;;;;;varchar(20) default 'site',
05;;siteId; varchar(30),
06;;columnId;;;;;varchar(30),
07;;infoId; varchar(30),
08;;url;;varchar(300),
09;;location;;;;;varchar(80),
10;;action; varchar(20),
11;;accessDate;;;varchar(10),
12;;accessTime;;;varchar(8),
13;;sessionID;;;;varchar(90),
14;;userName;;;;;varchar(30),
15;;userBrowser;;varchar(50),
16;;userOS; varchar(30),
17;;userIp; varchar(30),
18;;localization;varchar(100)
19 );
标签：
