下面就是毫无技术含量并且不断重复的就借助中间表将my_p_siteaccesslog中的2009年数据切换到中my_p_siteaccesslog_2009中的代码：
01 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p200901 WITH TABLE my_p_siteaccesslog_exchange;
02 ALTER TABLE my_p_siteaccesslog_2009 EXCHANGE PARTITION p200901 WITH TABLE my_p_siteaccesslog_exchange;
03
04 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p200902 WITH TABLE my_p_siteaccesslog_exchange;
05 ALTER TABLE my_p_siteaccesslog_2009 EXCHANGE PARTITION p200902 WITH TABLE my_p_siteaccesslog_exchange;
06
07 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p200903 WITH TABLE my_p_siteaccesslog_exchange;
08 ALTER TABLE my_p_siteaccesslog_2009 EXCHANGE PARTITION p200903 WITH TABLE my_p_siteaccesslog_exchange;
09
10 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p200904 WITH TABLE my_p_siteaccesslog_exchange;
11 ALTER TABLE my_p_siteaccesslog_2009 EXCHANGE PARTITION p200904 WITH TABLE my_p_siteaccesslog_exchange;
12
13 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p200905 WITH TABLE my_p_siteaccesslog_exchange;
14 ALTER TABLE my_p_siteaccesslog_2009 EXCHANGE PARTITION p200905 WITH TABLE my_p_siteaccesslog_exchange;
15
16 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p200906 WITH TABLE my_p_siteaccesslog_exchange;
17 ALTER TABLE my_p_siteaccesslog_2009 EXCHANGE PARTITION p200906 WITH TABLE my_p_siteaccesslog_exchange;
18
19 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p200907 WITH TABLE my_p_siteaccesslog_exchange;
20 ALTER TABLE my_p_siteaccesslog_2009 EXCHANGE PARTITION p200907 WITH TABLE my_p_siteaccesslog_exchange;
21
22 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p200908 WITH TABLE my_p_siteaccesslog_exchange;
23 ALTER TABLE my_p_siteaccesslog_2009 EXCHANGE PARTITION p200908 WITH TABLE my_p_siteaccesslog_exchange;
24
25 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p200909 WITH TABLE my_p_siteaccesslog_exchange;
26 ALTER TABLE my_p_siteaccesslog_2009 EXCHANGE PARTITION p200909 WITH TABLE my_p_siteaccesslog_exchange;
27
28 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p200910 WITH TABLE my_p_siteaccesslog_exchange;
29 ALTER TABLE my_p_siteaccesslog_2009 EXCHANGE PARTITION p200910 WITH TABLE my_p_siteaccesslog_exchange;
30
31 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p200911 WITH TABLE my_p_siteaccesslog_exchange;
32 ALTER TABLE my_p_siteaccesslog_2009 EXCHANGE PARTITION p200911 WITH TABLE my_p_siteaccesslog_exchange;
33
34 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p200912 WITH TABLE my_p_siteaccesslog_exchange;
35 ALTER TABLE my_p_siteaccesslog_2009 EXCHANGE PARTITION p200912 WITH TABLE my_p_siteaccesslog_exchange;
顺便创建存放2010年数据的分区表
01 create table my_p_siteaccesslog_2010
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 p201001 values less than ('2010-02-01'),
23;;partition p201002 values less than ('2010-03-01'),
24;;partition p201003 values less than ('2010-04-01'),
25;;partition p201004 values less than ('2010-05-01'),
26;;partition p201005 values less than ('2010-06-01'),
27;;partition p201006 values less than ('2010-07-01'),
28;;partition p201007 values less than ('2010-08-01'),
29;;partition p201008 values less than ('2010-09-01'),
30;;partition p201009 values less than ('2010-10-01'),
31;;partition p201010 values less than ('2010-11-01'),
32;;partition p201011 values less than ('2010-12-01'),
33;;partition p201012 values less than ('2011-01-01')
34 )
并将1月份的数据切换到表my_p_siteaccesslog_2010中：
1 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p201001 WITH TABLE my_p_siteaccesslog_exchange;
2 ALTER TABLE my_p_siteaccesslog_2010 EXCHANGE PARTITION p201001 WITH TABLE my_p_siteaccesslog_exchange;
经过一轮折腾之后，我们看看现各个表的情况：
01 test$ora8i@4.20 SQL> l
021* select segment_name,sum(bytes/1048576) MB from user_extents group by segment_name order by 2 desc
03 test$ora8i@4.20 SQL> /
04
05 SEGMENT_NAME;;MB
06 ---------------------------------------- ----------
07 P_SITEACCESSLOG 536
08 MY_P_SITEACCESSLOG_2009 435
09 MY_P_SITEACCESSLOG_2008; 51
10 MY_P_SITEACCESSLOG_2010;;45.6875
11 MY_P_SITEACCESSLOG; 35.1875
12 MY_P_SITEACCESSLOG_EXCHANGE.0625
表MY_P_SITEACCESSLOG以后将会改名为P_SITEACCESSLOG正式上线使用，其中仅保留1~2个月的数据用于查询，当需要查询旧数据的时候，应用可以不动，然后将旧的数据切换到MY_P_SITEACCESSLOG（也就是以后的P_SITEACCESSLOG）中就可以使用了。
分区表的切换产生的redo非常少，主要都是用于数据字典，因此系统的灵活性变得更高了。
标签：
