[20120926]普通表变成分区表.txt

生产系统有1个表很大,占到系统40%空间.我的想法是改成分区表,一些不修改的数据变成只读,仅仅备份一次,以后不再备份,

这样减少备份时间.这里做一个测试,看看这样操作是否可行.

SQL> select * from v$version ;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

create  tablespace test2010 datafile '/data/testtest/test2010.dbf' size 88k;

create  tablespace test2011 datafile '/data/testtest/test2011.dbf' size 88k;

create  tablespace test2012 datafile '/data/testtest/test2012.dbf' size 88k;

ALTER USER SCOTT QUOTA UNLIMITED ON TEST2010;

ALTER USER SCOTT QUOTA UNLIMITED ON TEST2011;

ALTER USER SCOTT QUOTA UNLIMITED ON TEST2012;

CREATE table t as

 SELECT ROWNUM ID, SYSDATE - 2 * 365 +rownum+ MOD (ROWNUM, 24) / 24 + MOD (ROWNUM, 60) / 1440 cdate, 'testtest' NAME

   FROM DUAL

CONNECT BY LEVEL <= 800;

SQL> select min(cdate),max(cdate) from t ;

MIN(CDATE)          MAX(CDATE)

------------------- -------------------

2010-09-28 09:50:18 2012-12-05 17:09:18

SQL> select trunc(cdate,'YYYY'),count(*) from t group by trunc(cdate,'YYYY');

TRUNC(CDATE,'YYYY')   COUNT(*)

------------------- ----------

2010-01-01 00:00:00         94

2012-01-01 00:00:00        340

2011-01-01 00:00:00        366

--数据分布在2010~2012年之间.

2.开始:

create table t_swap (id number,cdate date,name char(8)) partition by range (cdate) (partition ptmax values less than (maxvalue));

SQL> alter table t_swap exchange partition ptmax with table t including indexes  without validation;

Table altered.

SQL> select count(*) from t_swap ;

  COUNT(*)

----------

       800

SQL> select count(*) from t ;

  COUNT(*)

----------

         0

--已经交换为分区表.

SQL> drop table t purge;

Table dropped.

SQL> alter table t_swap rename to t;

Table altered.

--分割数据.

ALTER TABLE t SPLIT PARTITION PTMAX  AT (TO_DATE('2010-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')) INTO (PARTITION PT2010 tablespace test2010,PARTITION PTMAX);

ALTER TABLE t SPLIT PARTITION PTMAX  AT (TO_DATE('2011-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')) INTO (PARTITION PT2011 tablespace test2011,PARTITION PTMAX);

ALTER TABLE t SPLIT PARTITION PTMAX  AT (TO_DATE('2012-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')) INTO (PARTITION PT2012 tablespace test2012,PARTITION PTMAX);

select count(*) from t partition (pt2010)

union all

select count(*) from t partition (pt2011)

union all

select count(*) from t partition (pt2012)

union all

select count(*) from t partition (ptmax);

  COUNT(*)

----------

        94

       366

       340

         0

--设置2010年,2011年的数据对应表空间为只读.这样这些表空间以后的数据无法更改以及加入.

SQL> ALTER TABLESPACE TEST2010 READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE TEST2011 READ ONLY;

Tablespace altered.

--测试是否能插入数据:

SQL> insert into t values(801,'2010-10-30','xxxx');

insert into t values(801,'2010-10-30','xxxx')

                                            *

ERROR at line 1:

ORA-00372: file 9 cannot be modified at this time

ORA-01110: data file 9: '/data/testtest/test2010.dbf'

SQL> insert into t values(801,'2012-10-30','xxxx');

1 row created.

SQL> commit ;

Commit complete.

--2012年的数据依旧可以插入.不知道这样操作是否可行,主要在产生的日志上.