`

oracle通过表分区实现新增记录存储到其它磁盘

阅读更多
问题需求:
原有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;

后续会整理一个更详细的文档来分享。
分享到:
评论

相关推荐

    oracle数据表分区知识

    通过分区技术,可以根据指定的分区键将数据分散到不同的物理位置,从而实现更高效的数据管理和查询性能。表分区使得数据能够根据分区键的不同值分布到不同的分区中,并且这些分区可以存储在不同的表空间中。 **1.1 ...

    Oracle分区表在广播监测系统的应用探索 (1).pdf

    3. **均衡IO**:通过将分区映射到不同的磁盘,可以有效平衡输入输出操作,提升系统整体性能。 4. **优化查询性能**:查询时,仅需扫描目标分区,大大加快了检索速度。 Oracle提供了多种分区方法,如范围分区、列表...

    Oracle 高水位概念(hwm)

    - **空间管理**:HWM有助于Oracle确定何时需要为表分配新的数据块,以容纳新增的数据。当新数据插入并超过HWM时,数据库会自动向段添加新的区。 - **查询优化**:在执行全表扫描时,Oracle可以跳过高水位以上未...

    oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 连接字符串

    说明:用于连接到oracle数据库,也可实现用户的切换 用法:conn 用户名/密码 [as sysdba/sysoper] 注意:当用特权用户连接时,必须带上sysdba或sysoper 例子: 3. 断开连接(disc) 说明:断开与当前数据库的连接 ...

    Oracle-10g数据库基础教程(XXXX) 第07章逻辑存储结构.pptx

    系统表空间包括SYSTEM和SYSAUX,SYSAUX表空间是Oracle 10g新增的辅助系统表空间,主要用于存储数据库组件等信息,以减小SYSTEM表空间的负荷。非系统表空间包括撤销表空间、临时表空间和用户表空间。撤销表空间专门...

    oracle 视图、索引(自用)

    1. 定义:视图是从一个或多个表中创建的虚拟表,它并不实际存储数据,而是存储查询语句。当用户查询视图时,Oracle会执行视图背后的SQL语句并返回结果。 2. 创建视图:使用CREATE VIEW语句,可以创建基于特定查询...

    基于Oracle的邮件系统的存储优化.pdf

    【Oracle 数据库中的大对象(LOB)存储优化】 在Oracle数据库中,大对象(LOB)是一...通过上述方法,可以实现基于Oracle的邮件系统的存储优化,提高系统的响应速度和资源利用率,为企业提供更稳定、高效的邮件服务。

    oracle 9i课程(oracle教材)

    5. **Partitioning**: Oracle 9i加强了分区功能,允许大型表和索引按逻辑或物理方式分割,提高查询性能和管理效率。 6. **Materialized Views**: 提供预计算的数据视图,加速复杂查询和数据汇总,特别适用于数据...

    Oracle+书籍《Oracle+11g+实用教程》

    物理存储层涉及到磁盘上的数据文件、控制文件、重做日志文件等;逻辑存储层则包括表空间、段、区和块;网络层负责处理数据库的网络通信,确保远程访问的安全性和效率。 ### 3. SQL与PL/SQL SQL(Structured Query ...

    oracle 优化重量级

    例如,通过分区技术来分散数据的物理存储位置,可以显著减少查询响应时间。 2. **物理存储布局**:包括表空间管理、数据块大小设置等。正确选择这些参数能够有效提升I/O效率。 3. **系统参数调优**:合理设置Oracle...

    Oracle Database10g

    - 版本比较表总结了Oracle 10g与其他版本的主要区别。 - **重要特性**:提供了版本间功能差异的概览,有助于决策升级策略。 **1.32 新特性回顾** - 新特性回顾部分总结了Oracle 10g的所有新特性。 - **重要特性**:...

    Oracle10G性能优化宝典

    这份资料不仅覆盖了Oracle 10g的新功能,还深入探讨了索引原理、磁盘实现方法以及自动存储管理(ASM)等关键领域,为提升数据库性能提供了宝贵的知识。 ### 1. Oracle Database 10g新功能 #### 安装改进 Oracle 10g...

    Oracle Database 12c 数据库100个新特性与案例总结V2.0

    - **应用场景**:当需要调整存储布局时非常有用,例如从一个磁盘移动到另一个磁盘。 ### 1.3 表分区或子分区的在线迁移 - **功能介绍**:允许用户在不影响在线业务的情况下,重新组织表的分区结构。 - **优点**:...

    Oracle 11G RAC超详细带截图安装文档

    - **1.2.4 停止ntp时间同步**: 对于Oracle 11g新增的检查项,确保ntp服务关闭。 - **1.2.5 配置网络**: 设置IP地址、子网掩码等网络参数。 - **1.2.6 配置DNS服务器**: 指定DNS服务器地址。 - **1.2.7 设置grid...

    数据库安装文档_AIX 6.1下安装Oracle 11g R2单实例

    由于Oracle 11g R2相较于之前的版本如10g有所更新,因此在安装前准备过程中,需要特别注意检查安装目录的磁盘大小,以及新增的scanIP地址的申请。本安装指南分为几个主要步骤:安装前的准备工作、集群安装、数据库...

    oracle压缩.txt

    - **减少存储成本**:通过压缩数据减少所需的物理磁盘空间。 - **提高I/O效率**:压缩后的数据能够更快地读取到内存中,从而提高应用程序响应速度。 - **降低CPU消耗**:虽然压缩和解压缩操作会增加CPU负载,但对于...

    自编ORACLE RAC 安装

    自编Oracle RAC安装涉及到在虚拟环境中详细记录安装步骤、升级过程以及应用关键补丁的过程。以下是对文档中提到的知识点的详细阐述。 一、使用的软件及其版本 Oracle RAC安装首先要确定安装的数据库版本和集群软件...

    Oracle 11gR2 New Feaure's Guide

    总结来说,Oracle 11gR2通过一系列新特性的引入,在数据库管理、性能优化、数据保护以及安全性方面实现了显著提升。这些新特性不仅有助于企业更好地应对日益增长的数据量挑战,还为企业提供了更为强大的工具来满足其...

Global site tag (gtag.js) - Google Analytics