oracle通过表分区实现新增记录存储到其它磁盘问题需求：原有oracle数据库数据文件放在D盘，但是D盘空间剩不太多了，老大建议转到E盘下。上次给表空间新建oracle数据文件时，发现大表没办法新建，所以暂时还没有处...SyntaxHighlighter.all();
oracle通过表分区实现新增记录存储到其它磁盘
问题需求：
原有oracle
数据库数据文件放在D盘，但是D盘空间剩不太多了，老大建议转到E盘下。上次给表空间新建oracle数据文件时，发现大表没办法新建，所以暂时还没有处理。
解决办法：
最近在网上看了一些oracle的资料，想到一种思路，在家里的数据库上进行了验证。把日志表转变为分区表，然后把后续新增的日志数据都存到新的分区中，新的分区可以放在其它磁盘上。
理论依据
1.不同的表空间可以很方便的放在不同的磁盘上，也不会有大表的问题
2.分区表中不同分区的数据可以存放在不同的表空间
3.可以通过表的重定义把一个现有的表转化为分区表
4.对一个用户来说查询分区表的时候不需要额外的操作（带分区之类的）
具体参考前面两篇文章。 www.2cto.com
大体步骤
1.通过在线重定义，把日志表转化为分区表
2.新建表空间到新的磁盘，用户仍然从属于原表空间的用户（方便到时候查询）
3.给日志表增加一个分区，新分区的数据文件在新的表空间上
o了。
详细步骤
以下所有语句均在SQLPLUS中执行：
1.给Mutual表(与下面的LOGSMSHALL_MUTUAL_NEW定义一致的)添加主键（因为重定义表要有主键）（这个步骤不是必须的，可能在9i下是必须的，不过我在136数据库上验证的时候先执行了）
ALTER TABLE LOGSMSHALL_MUTUAL ADD constraint PK_MUTUAL primary key (id);
2.开启表允许重定义
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, &＃39;LOGSMSHALL_MUTUAL&＃39;, DBMS_REDEFINITION.CONS_USE_PK);
3.创建新的临时表
CREATE TABLE LOGSMSHALL_MUTUAL_NEW (
ID NUMBER(20) primary key NOT NULL,
"SESSIONID" VARCHAR2(28) ,
"REQUESTID" VARCHAR2(32) ,
"USERTELNO" VARCHAR2(16) ,
"USERCITYNAME" VARCHAR2(8) ,
"USERBRANDNAME" VARCHAR2(16) ,
"USERCONTENT" VARCHAR2(512) ,
"RECEIVETIME" TIMESTAMP DEFAULT sysdate ,
"PROCESSTYPE" VARCHAR2(16) ,
"PROCESSNODENAME" VARCHAR2(32) ,
"RECNODENAME" VARCHAR2(32) ,
"RECTIME" TIMESTAMP www.2cto.com ,
"RECTYPE" VARCHAR2(16) DEFAULT &＃39;NotRec&＃39; ,
"RECRESULT" CHAR(1) DEFAULT &＃39;1&＃39; ,
"RECRESULTCODE" VARCHAR2(32) ,
"RECRESULTDESC" VARCHAR2(256) ,
"PLATFORMHANDLENODENAME" VARCHAR2(32) ,
"PLATFORMHANDLETIME" TIMESTAMP DEFAULT sysdate ,
"PLATFORMHANDLERESULT" CHAR(1) DEFAULT &＃39;2&＃39; ,
"PLATFORMHANDLERESULTCODE" VARCHAR2(32) ,
"PLATFORMHANDLERESULTDESC" VARCHAR2(1024) ,
"REPLYCONTENT" VARCHAR2(1024) ,
"REPLYINDEXID" INTEGER ,
"SENDSMSNODENAME" VARCHAR2(32) ,
"SENDSMSTIME" TIMESTAMP DEFAULT sysdate ,
"SENDSMSRESULT" CHAR(1) DEFAULT &＃39;1&＃39; ,
"SENDSMSRESULTCODE" VARCHAR2(32) ,
"SENDSMSRESULTDESC" VARCHAR2(256) ,
"COSTSECONDS" INTEGER ,
"NLIBIZNAME" VARCHAR2(32) ,
"BIZNAME" VARCHAR2(128) ,
"OPERATIONNAME" VARCHAR2(16) ,
"PARMSKEYANDVALUE" VARCHAR2(128) ,
"CHECKFLAG" CHAR(1) DEFAULT &＃39;0&＃39;,
"CHECKTIME" TIMESTAMP DEFAULT sysdate
)
PARTITION BY RANGE (RECEIVETIME)
(PARTITION P1 VALUES LESS THAN (TO_DATE(&＃39;2012-4-10&＃39;, &＃39;YYYY-MM-DD&＃39;)));
4.开始表的重定义
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, &＃39;LOGSMSHALL_MUTUAL&＃39;, &＃39;LOGSMSHALL_MUTUAL_NEW&＃39;, &＃39;ID ID&＃39;, DBMS_REDEFINITION.cons_use_rowid);
5.结束表的重定义
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, &＃39;LOGSMSHALL_MUTUAL&＃39;, &＃39;LOGSMSHALL_MUTUAL_NEW&＃39;); www.2cto.com
该过程将自动完成
. 应用快照日志中的DML到中间表
. 互换原表与中间表的名字，包括所有可能出现的数据字典
. 但是需要注意的是，并不对换约束，索引，触发器的名称，这些需要手工修改
7.删除中间表
DROP TABLE LOGSMSHALL_MUTUAL_NEW;
6.修改触发器
CREATE OR REPLACE TRIGGER "TIB_LOGSMSHALL_MUTUAL" BEFORE INSERT
ON "LOGSMSHALL_MUTUAL" FOR EACH ROW
DECLARE
INTEGRITY_ERROR EXCEPTION;
ERRNO INTEGER;
ERRMSG CHAR(200);
DUMMY INTEGER;
FOUND BOOLEAN;
BEGIN www.2cto.com
-- COLUMN "ID" USES SEQUENCE S_LOGSMSHALL_MUTUAL
SELECT S_LOGSMSHALL_MUTUAL.NEXTVAL INTO :NEW.ID FROM DUAL;
-- ERRORS HANDLING
EXCEPTION
WHEN INTEGRITY_ERROR THEN
RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);
END;
/
7.新建表空间
CREATE TABLESPACE E
CSS_LOG_NEW DATAFILE &＃39;D:\oracle\product\10.2.0\oradata\ECSS_LOG_NEW_data&＃39; SIZE 1024M AUTOEXTEND ON NEXT 256M MAXSIZE unlimited;
8.给原表增加分区
ALTER TABLE LOGSMSHALL_MUTUAL ADD PARTITION P_NEW VALUES LESS THAN(TO_DATE(&＃39;2099-12-31&＃39;,&＃39;YYYY-MM-DD&＃39;));
因为原来的分区容纳的数据都是小于2012-4-10日的，大于2012-4-10的数据就会存放在新的分区P_NEW中 www.2cto.com
验证下表LOGSMSHALL_MUTUAL的分区
SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME=&＃39;LOGSMSHALL_MUTUAL&＃39; ，会看到两个
9.验证
插入日期大于2012-4-10的一条数据进入LOGSMSHALL_MUTUAL表
INSERT INTO LOGSMSHALL_MUTUAL(ReceiveTime) VALUES (to_date(&＃39;2012-4-20&＃39;,&＃39;YYYY-MM-DD&＃39;));
commit;
再执行3条语句验证记录是否插入新的分区
select count(*) cn from logsmshall_mutual partition (P1);
select count(*) cn from logsmshall_mutual partition (P_NEW);
select count(*) cn from logsmshall_mutual;
后续会整理一个更详细的文档来分享。
作者 Ajita
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有