oracle普通表转化为分区表的方法

上一篇文章中我们了解了oracle数据与文本导入导出源码示例的相关内容，接下来我们看看，oracle中如何将普通表转化为分区表的方法。

oracle官方建议当表的大小大于2GB的时候就使用分区表进行管理，分区表相对于小的表管理和性能上都有很大的优势，本文档暂时不介绍具体的优势，主要介绍几种普通表转换成分区表的方法。

【方法概述】oracle官方给了以下四种操作的方法：

 A)  Export/import method（导入导出）
 B)  Insert with a subquery method（插入子查询的方法）
 C)  Partition exchange method（交换分区法）
 D)  DBMS_REDEFINITION（在线重定义）

这些方法的思路都是创建一个新的分区表，然后把旧表的数据转移到新表上面，接着转移相应的依赖关系，最后进行表的重命名，把新表和旧表rename。

其中A、B、C这三种方法都会影响到系统的正常使用，本文档不做详细的介绍，本文档主要介绍D方法，这种方法是目前普遍在进行普通表转换成分区表的方法。

【在线重定义进行分区表的操作】整个操作的思路如下，以SCOTT下的EMP表为例

1. 先确认下表能不能进行分区

基于主键的确认

BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('SOCTT','EMP',DBMS_REDEFINITION.CONS_USE_PK);
END;
/
PL/SQL procedure successfully completed.显示的是没有问题的

2.进行临时表的创建，以DEPTNO作为分区的选项

CREATE TABLE SCOTT.EMP_1
(
 EMPNONUMBER(4),
 ENAMEVARCHAR2(10 BYTE),
 JOBVARCHAR2(9 BYTE),
 MGRNUMBER(4),
 HIREDATEDATE,
 SALNUMBER(7,2),
 COMMNUMBER(7,2),
 DEPTNONUMBER(2)
)
PARTITION BY RANGE (DEPTNO)
(
 PARTITION EMP_A1 VALUES LESS THAN (20),
 PARTITION EMP_A2 VALUES LESS THAN (30),
 PARTITION EMP_A3 VALUES LESS THAN (40),
 PARTITION EMP_A4 VALUES LESS THAN (50),
 PARTITION EMP_A5 VALUES LESS THAN (60)
   )

3.开始执行数据的迁移

EXEC DBMS_REDEFINITION.START_REDEF_TABLE('SCOTT', 'EMP', 'EMP_1');

4.如果表的数据很多，3步的时候可能会很长，这期间系统可能会继续对表EMP进行写入或者更新数据，那么可以执行以下的语句，这样在执行最后一步的时候可以避免长时间的锁定(该过程可选可不选)

BEGIN
 DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT', 'EMP', 'EMP_1');
END;
/

5.进行权限对象的迁移

DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCOTT', 'EMP','EMP_1',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/

6.查询相关错误，在操作之前先检查，查询DBA_REDEFINITION_ERRORS试图查询错误：

select object_name, base_table_name, ddl_txt from  DBA_REDEFINITION_ERRORS;

7.结束整个重定义

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('scott', 'emp', 'emp_1');
END;
/

做过一个大小2.3GB，总行数360万行的表，整个过程大概花了56秒的时间，整个过程还是相当快的。建议具体的生产环境的执行需要经过严格测试后执行，测试的过程中大概就能知道整个过程的执行时间长度。

另如果再执行的过程中发生错误，可以通过以下语句结束整个过程：

BEGIN
DBMS_REDEFINITION.ABORT_REDEF_TABLE(uname => 'SCOTT',
orig_table => 'EMP',
int_table => 'EMP_1'
);
END;

总结

以上就是本文关于oracle普通表转化为分区表的方法的全部内容，希望对大家有所帮助。感兴趣的朋友可以继续参阅本站：oracle数据库导入导出命令解析、ORACLE SQL语句优化技术要点解析等，有什么问题可以直接留言，小编会及时回复大家的。感谢朋友们对本站的支持！

赞 (0)

相关推荐

  • ORACLE 分区表的设计

    分区表的概念 分区致力于解决支持极大表和索引的关键问题.它采用他们分解成较小和易于管理的称为分区的片(piece)的方法.一旦分区被定义,SQL语句就可以访问的操作某一个分区而不是整个表,因而提高管理的效率.分区对于数据仓库应用程序非常有效,因为他们常常存储和分析巨量的历史数据. 分区表的分类 Range partitioning(范围分区) Hash partitioning(哈希分区) List partitioning(列表分区) Composite range-hash partitio

  • oracle分区表之hash分区表的使用及扩展

    Hash分区是通过对分区键运用Hash算法从而决定数据的分区归属.使用Hash分区有什么优点呢? 常用的分区表所具有的优点:如提高数据可用行,减少管理负担,改善语句性能等优点,hash分区同样拥有.此外,由于Hash分区表是按分区键的hash计算结果来决定其分区的,而特定的分区键其hash值是固定的,也就是说Hash分区表的数据是按分区键值来聚集的,同样的分区键肯定在同一分区.比如,在证券行业,我们经常查询某一只股票的K线,假设表的结构如下: 复制代码 代码如下: create table eq

  • oracle普通表转化为分区表的方法

    上一篇文章中我们了解了oracle数据与文本导入导出源码示例的相关内容,接下来我们看看,oracle中如何将普通表转化为分区表的方法. oracle官方建议当表的大小大于2GB的时候就使用分区表进行管理,分区表相对于小的表管理和性能上都有很大的优势,本文档暂时不介绍具体的优势,主要介绍几种普通表转换成分区表的方法. [方法概述]oracle官方给了以下四种操作的方法:  A)  Export/import method(导入导出)  B)  Insert with a subquery meth

  • Oracle查看表结构的几种方法示例代码

    1,DESCRIBE 命令 使用方法如下: SQL> describe nchar_tst(nchar_tst为表名) 显示的结果如下: 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- NAME NCHAR(6) ADDR NVARCHAR2(16) SAL NUMBER(9,2) 2,DBMS_METADATA.GET_DDL包 使用方法如下: SQL> S

  • 优化Oracle库表设计的若干方法

    正在看的ORACLE教程是:优化Oracle库表设计的若干方法. 前言 绝大多数的Oracle数据库性能问题都是由于数据库设计不合理造成的,只有少部分问题根植于Database Buffer.Share Pool.Redo Log Buffer等内存模块配置不合理,I/O争用,CPU争用等DBA职责范围上.所以除非是面对一个业已完成不可变更的系统,否则我们不应过多地将关注点投向内存.I/O.CPU等性能调整项目上,而应关注数据库表本身的设计是否合理,库表设计的合理性才是程序性能的真正执牛耳者.

  • Oracle表空间数据文件移动的方法

    实现把用户表空间中的数据文件从某一个路径移动到另一个路径 一.针对可offline的非系统表空间 本例移动oracle的案例表空间(EXAMPLE表空间),将其从 D:\ORADATA\ORCL\ 移动到 D:\ORACLE\ORADATA\ 1.查看要改变的表空间的数据文件信息 SQL> select tablespace_name,file_name,online_status from dba_data_files where tablespace_name='EXAMPLE'; TABL

  • Oracle修改表空间大小的方法

    本文讲述了Oracle修改表空间大小的方法.分享给大家供大家参考,具体如下: 1)查看各表空间分配情况 SQL> select tablespace_name, sum(bytes) / 1024 / 1024 from dba_data_files group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024 ------------------------------ -------------------- UNDOTBS1

  • Oracle触发器表发生了变化 触发器不能读它的解决方法(必看)

    出现原因,是因为在更新的的表和读取的表是同一个表. CREATE or replace TRIGGER T_userupdateT BEFORE update ON T_user REFERENCING OLD AS old NEW AS N_ROW FOR EACH ROW DECLARE U_xtfidemp1 varchar(36); u_xtempcode1 varchar(20); u_xtempcodeCount int:=0; U_xtfidempCount int:=0; u_i

  • Oracle数据库表中字段顺序的修改方法

    Oracle数据库表中字段顺序的修改方法

    前言 前段时间,有人问我, 有一个关于Oracle中表字段顺序调整的问题.问题就是当设计好表结构之后,后期如果需要往表中增加字段,默认会把该字段放到表的最后,并且字段特别多而我们又想把有关联性的字段放在一起,这时就要修改字段顺序.在修改顺序之前,我们先来看看Oracle数据库表中如何增加字段. 新增字段: 添加字段的语法:alter table tablename add (column datatype [default value][null/not null],-.); 实例 创建表结构:

  • Oracle判断表、列、主键是否存在的方法

    在编写程序时,数据库结构会经常变化,所以经常需要编写一些数据库脚本,编写完成后需发往现场执行,如果已经存在或者重复执行,有些脚本会报错,所以需要判断其是否存在,现在我就把经常用到的一些判断方法和大家分享下: 一.判断Oracle表是否存在的方法 declare tableExistedCount number; --声明变量存储要查询的表是否存在 begin select count(1) into tableExistedCount from user_tables t where t.tab

  • oracle 批量删除表数据的几种方法

    1.情景展示 情景一: 删除PRIMARY_INDEX_TEST表中,MINDEX_ID字段为空的数据 情景二: 删除VIRTUAL_CARD_TEST表中的脏数据 2.解决方案 情景一的解决方案: DELETE FROM PRIMARY_INDEX_TEST WHERE MINDEX_ID IS NULL 情景二的解决方案: 方案1:使用快速游标法(删除一次提交一次): --快速游标法 BEGIN FOR TEMP_CURSOR IN (SELECT ID FROM VIRTUAL_CARD3

  • Oracle sysaux表空间异常增长的完美解决方法

    Oracle sysaux表空间异常增长的完美解决方法

    目录 发现问题 问题原因分析: 处理方法: 总结 发现问题 某一业务系统数据库,sysaux表空间大小达到24GB+ 查询sysaux表空间中对象的大小,超过1GB的列出,发现ASH相关的表过大. SEGMENT_NAME OWNER SEGMENT_TYPE MB ------------------------------ -------------------- ------------------ ------ WRH$_ACTIVE_SESSION_HISTORY SYS TABLE

随机推荐