问题需求:
原有oracle数据库数据文件放在D盘,但是D盘空间剩不太多了,老大建议转到E盘下。上次给表空间新建oracle数据文件时,发现大表没办法新建,所以暂时还没有处理。
解决办法:
最近在网上看了一些oracle的资料,想到一种思路,在家里的数据库上进行了验证。把日志表转变为分区表,然后把后续新增的日志数据都存到新的分区中,新的分区可以放在其它磁盘上。
理论依据
1.不同的表空间可以很方便的放在不同的磁盘上,也不会有大表的问题
2.分区表中不同分区的数据可以存放在不同的表空间
3.可以通过表的重定义把一个现有的表转化为分区表
4.对一个用户来说查询分区表的时候不需要额外的操作(带分区之类的)
具体参考前面两篇文章。
大体步骤
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, 'LOGSMSHALL_MUTUAL', DBMS_REDEFINITION.CONS_USE_PK);
或者(不需要主键)
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'LOGSMSHALL_MUTUAL', DBMS_REDEFINITION.cons_use_rowid);
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 ,
"RECTYPE" VARCHAR2(16) DEFAULT 'NotRec' ,
"RECRESULT" CHAR(1) DEFAULT '1' ,
"RECRESULTCODE" VARCHAR2(32) ,
"RECRESULTDESC" VARCHAR2(256) ,
"PLATFORMHANDLENODENAME" VARCHAR2(32) ,
"PLATFORMHANDLETIME" TIMESTAMP DEFAULT sysdate ,
"PLATFORMHANDLERESULT" CHAR(1) DEFAULT '2' ,
"PLATFORMHANDLERESULTCODE" VARCHAR2(32) ,
"PLATFORMHANDLERESULTDESC" VARCHAR2(1024) ,
"REPLYCONTENT" VARCHAR2(1024) ,
"REPLYINDEXID" INTEGER ,
"SENDSMSNODENAME" VARCHAR2(32) ,
"SENDSMSTIME" TIMESTAMP DEFAULT sysdate ,
"SENDSMSRESULT" CHAR(1) DEFAULT '1' ,
"SENDSMSRESULTCODE" VARCHAR2(32) ,
"SENDSMSRESULTDESC" VARCHAR2(256) ,
"COSTSECONDS" INTEGER ,
"NLIBIZNAME" VARCHAR2(32) ,
"BIZNAME" VARCHAR2(128) ,
"OPERATIONNAME" VARCHAR2(16) ,
"PARMSKEYANDVALUE" VARCHAR2(128) ,
"CHECKFLAG" CHAR(1) DEFAULT '0',
"CHECKTIME" TIMESTAMP DEFAULT sysdate
)
PARTITION BY RANGE (RECEIVETIME)
(PARTITION P1 VALUES LESS THAN (TO_DATE('2012-4-10', 'YYYY-MM-DD')));
4.开始表的重定义
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'LOGSMSHALL_MUTUAL', 'LOGSMSHALL_MUTUAL_NEW', 'ID ID', DBMS_REDEFINITION.cons_use_rowid);
异常路径:
如果表的数据量特别大,会报错误ORA-04031: 无法分配 12519000 字节的共享内存,是因为oracle给每个连接使用的共享内存区,大小有限制,给成专用内存区即可(本人尝试过千万级的数据)
要按下面步骤执行一下,然后从步骤4(重定义)开始再执行
a.终止重定义
EXEC dbms_redefinition.abort_redef_table(USER, 'LOGSMSHALL_MUTUAL', 'LOGSMSHALL_MUTUAL_NEW');
b.修改使用专有内存区(其中unimandb是数据库实例的名称)
alter system set dispatchers='(PROTOCOL=TCP)(SERVICE=unimandb)';
c.重启oracle服务
shutdown——startup的方式,或者重启oracle进程的方式均可(我试的是重启进程的方式,比较彻底)
重新执行步骤四
5.结束表的重定义
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'LOGSMSHALL_MUTUAL', 'LOGSMSHALL_MUTUAL_NEW');
该过程将自动完成
. 应用快照日志中的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
-- 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 ECSS_LOG_NEW DATAFILE 'D:\oracle\product\10.2.0\oradata\ECSS_LOG_NEW_data' SIZE 1024M AUTOEXTEND ON NEXT 256M MAXSIZE unlimited;
8.给原表增加分区,顺便指定表空间
ALTER TABLE LOGSMSHALL_MUTUAL ADD PARTITION P_NEW VALUES LESS THAN(TO_DATE('2099-12-31','YYYY-MM-DD')) TABLESPACE ECSS_LOG_NEW;
因为原来的分区容纳的数据都是小于2012-4-10日的,大于2012-4-10的数据就会存放在新的分区P_NEW中
验证下表LOGSMSHALL_MUTUAL的分区
SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='LOGSMSHALL_MUTUAL' ,会看到两个
9.验证
插入日期大于2012-4-10的一条数据进入LOGSMSHALL_MUTUAL表
INSERT INTO LOGSMSHALL_MUTUAL(ReceiveTime) VALUES (to_date('2012-4-20','YYYY-MM-DD'));
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;
后续会整理一个更详细的文档来分享。
分享到:
相关推荐
Hash分区是Oracle实现表分区的三种基本分区方式之一。对于那些无法有效划分分区范围的大表,或者出于某些特殊考虑的设计,需要使用Hash分区,下面介绍使用方法
支持自动ORACLE大表分区: 版本进度: 31. 20110420 V2.2 支持任意表任意时间字段分区 以下为安装部署部分: 1.分区相关脚本部署执行顺序,安装前请确保该用户拥有管理员权限, 同时请执行GRANT CREATE ANY TABLE ...
oracle表分区详解
Oracle分区表详解 大家可以参考下 网上找的资料共享一下
ORACLE分区ORACLE分区ORACLE分区ORACLE分区ORACLE分区ORACLE分区ORACLE分区ORACLE分区ORACLE分区ORACLE分区ORACLE分区ORACLE分区ORACLE分区ORACLE分区
文件是本人oracle10g分区表自动按时间创建、删除分区的存储过程,测试代码,通过job调用存储过程,每天午夜12点运行一次。妥妥!跟大家分享下!
oracle创建分区表
主要介绍了oracle普通表转化为分区表的方法,官方给出了四种操作方法,本文主要对第四种方法进行详细分析,需要的朋友可以参考下。
oracle分区表总结oracle分区表总结oracle分oracle分区表总结区表总结oracle分区表总结
Oracle 表分区技术详解: 1.表空间及分区表的概念 2.表分区的具体作用 3.表分区的优缺点 4.表分区的几种类型及操作方法 5.对表分区的维护性操作.
oracle 数据库的表分区操作实例,适合学习操作对表进行分区。
oracle 分区表管理oracle 分区表管理oracle 分区表管理oracle 分区表管理oracle 分区表管理
Oracle大表分区的技术,网上找的,比较详细,收藏中
Oracle提供了对表和索引进行分区的技术,以改善大型应用系统的性能
oracle资源分区表oracle资源分区表oracle资源分区表oracle资源分区表
虽然存储介质和数据处理技术的发展也很快,但是仍然不能满足用户的需求,为了使用户的大量的数据在读写操作和查询中速度更快,Oracle提供了对表和索引进行分区的技术,以改善大型应用系统的性能。
oracle表分区实例.doc oracle表分区实例.doc oracle表分区实例.doc
分享一个自己学习和实践的关于Oracle表自动按月分区知识点,已经在项目上线并且有效的方案。
Oracle的相关知识,建表空间,创建用户,给用户授权, 删除用户,给表多列加锁,导出和导入,范围分区,散列分区,列表分区,复合分区、、、
对于oracle分区表分区索引的详细说明。 详细描述了分区表的类型,分区索引的类型 分类 。 删除或truncate 表分区时,什么样的情况索引会失效 需要重建 ,什么时候 对索引 没影响 。