oracle-表-通过在线重定义方式(online redefinition)实现oracle大表字段varchar2转换成clob

简介:

需求

通过在线重定义方式(online redefinition)实现oracle大表字段varchar2转换成clob
表MTMP0426的SOFTVERLIST字段:varchar2-->cob
表MTMP0426:大小23.5 GB

1. 创建中间表 MTMP0426_tmp

conn user/user
Prompt Table MTMP0426_tmp;
--
-- MTMP0426_tmp  (Table) 
--
CREATE TABLE user.MTMP0426_tmp
(
  ORGID           NUMBER                        NOT NULL,
  MODELNAME       VARCHAR2(80 BYTE),
  EXTERNAME       VARCHAR2(80 BYTE),
  SOFTVER         VARCHAR2(80 BYTE),
  SOFTVERLIST   clob,                  <<<
  IMEI            VARCHAR2(80 BYTE),
  CUSER           VARCHAR2(80 BYTE),
  CDATE           NUMBER,
  CTIME           NUMBER,
  MUSER           VARCHAR2(80 BYTE),
  MDATE           NUMBER,
  MTIME           NUMBER,
  EATTRIBUTE1     VARCHAR2(240 BYTE),
  EATTRIBUTE2     VARCHAR2(240 BYTE),
  EATTRIBUTE3     VARCHAR2(240 BYTE),
  EATTRIBUTE4     VARCHAR2(240 BYTE),
  EATTRIBUTE5     VARCHAR2(240 BYTE),
  EATTRIBUTE6     VARCHAR2(240 BYTE),
  EATTRIBUTE7     VARCHAR2(240 BYTE),
  EATTRIBUTE8     VARCHAR2(240 BYTE),
  EATTRIBUTE9     VARCHAR2(240 BYTE),
  EATTRIBUTE10    VARCHAR2(240 BYTE),
  SOFTVERLISTNEW  CLOB

)
TABLESPACE USERS
LOGGING 
NOCOMPRESS 
NOCACHE
MONITORING;

ALTER SESSION force parallel dml parallel 4;
ALTER SESSION force parallel query parallel 4;
alter table vivo.MTMP0426_tmp enable row movement;

2.检查是否可以重定义

BEGIN
  dbms_redefinition.can_redef_table(uname => 'USER',
                                    tname => 'MTMP0426',
                                    options_flag => DBMS_REDEFINITION.cons_use_rowid);
END;
/

3. 开始在线重定义,同步数据

begin
  DBMS_REDEFINITION.START_REDEF_TABLE(uname        => 'USER',
                                      orig_table   => 'MTMP0426',
                                      int_table    => 'MTMP0426_tmp',
                                      col_mapping  => 
'ORGID,
MODELNAME,      
EXTERNAME,       
SOFTVER,         
to_clob(SOFTVERLIST) SOFTVERLIST,    <<<
IMEI,            
CUSER,           
CDATE,           
CTIME,           
MUSER,           
MDATE,           
MTIME,           
EATTRIBUTE1,
EATTRIBUTE2,
EATTRIBUTE3,
EATTRIBUTE4,
EATTRIBUTE5,
EATTRIBUTE6,
EATTRIBUTE7,
EATTRIBUTE8,
EATTRIBUTE9,
EATTRIBUTE10,
SOFTVERLISTNEW  ',
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
end;
/

4.复制相关约束,依赖对象

DECLARE
      num_errors PLS_INTEGER;
    BEGIN
      DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname            => 'USER',
                                              orig_table       => 'MTMP0426',
                                              int_table        => 'MTMP0426_tmp',
                                              copy_indexes     => 0,
                                              copy_triggers    => TRUE,
                                              copy_constraints => FALSE,
                                              copy_privileges  => TRUE,
                                             ignore_errors    => FALSE,
                                             num_errors       => num_errors,
                                             copy_statistics  => False);
   END;
/

5. Check for any errors

select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;

6. 建立索引


Prompt Index IDX_MTMP0426_11;
--
-- IDX_MTMP0426_11  (Index) 
--
CREATE INDEX user.IDX_MTMP0426_11 ON user.MTMP0426_tmp
(MDATE)
LOGGING
TABLESPACE USERS PARALLEL 4 ;
ALTER INDEX user.IDX_MTMP0426_11 NOPARALLEL;
Prompt Index IDX_MTMP0426_21;
--
-- IDX_MTMP0426_21  (Index) 
--
CREATE INDEX user.IDX_MTMP0426_21 ON user.MTMP0426_tmp
(IMEI)
LOGGING
TABLESPACE USERS PARALLEL 4 ;
ALTER INDEX user.IDX_MTMP0426_21 NOPARALLEL;

Prompt Index IDX_MTMP0426_31;
--
-- IDX_MTMP0426_31  (Index) 
--
CREATE INDEX user.IDX_MTMP0426_31 ON user.MTMP0426_tmp
(EATTRIBUTE1)
LOGGING
TABLESPACE USERS PARALLEL 4 ;
ALTER INDEX user.IDX_MTMP0426_31 NOPARALLEL;

7. 同步中间数据,减少finish_redef_table时间

BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname      => 'USER',
                                       orig_table => 'MTMP0426',
                                       int_table  => 'MTMP0426_tmp');
END;
/

8. 完成在线重定义

BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname      => 'USER',
                                       orig_table => 'MTMP0426',
                                       int_table  => 'MTMP0426_tmp');
END;
/

9. 删除中间表

drop table user.MTMP0426_tmp cascade constraints;

alter table user.MTMP0426 drop unused columns;  ---如果是大表,这一步会锁表很长时间,可以考虑不drop unsed columns

10.若在完成重定义(执行finish_redef_table)之前希望中断在线重定义表,则需要使用DBMS_REDEFINITION.ABORT_REDEF_TABLE明确手动中断abort,如:

begin
dbms_redefinition.abort_redef_table(uname => 'USER',
orig_table => 'MTMP0426',
int_table  => 'MTMP0426_tmp');
end;
/

11. 查看在线重定义中的表

set line 300
col OBJECT_OWNER for a50
col INTERIM_OBJECT_OWNER for a30
col OBJECT_OWNER for a30
select * from DBA_REDEFINITION_OBJECTS ;

OBJECT_TYPE  OBJECT_OWNER                   OBJECT_NAME                    BASE_TABLE_OWNER               BASE_TABLE_NAME                INTERIM_OBJECT_OWNER           INTERIM_OBJECT_NAME            EDITION_NAME                  
------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
TABLE        USER                            MTMP0426                      USER                            MTMP0426                      USER                            MTMP0426_TMP                                                
已选择 1 行。

12.收集表统计信息

begin
dbms_stats.gather_table_stats (
ownname => 'table_owner',
tabname => 'table_name',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'FOR ALL INDEXED COLUMNS  SIZE AUTO',
degree => 8,
cascade => TRUE,
granularity=> 'ALL',
no_invalidate => false);
end;
/

转换后表大小 50.6 GB

参考:https://blog.csdn.net/jwocnimabi/article/details/107299767
合理使用Blob/Clob:https://blog.51cto.com/supercharles888/1163402
介绍:

虽然数据库原生支持Blob(二进制大对象)和Clob(字符串大对象),但是从性能的考虑,我们把这些内容放在数据库里面不合适的。比如说我有1个100多M的图片(Blob),或者说我有一大段文本(Clob),这种东西是千万杜绝放在数据库中的。原因有3点。
(1)加大了数据库维护和备份的成本。因为这些大数据的存在,使得我们要很多额外空间来进行数据库备份。
(2)是因为数据库是一种读快写慢的系统,这就意味着如果我们要变更这些内容,会非常的不方便。比如我们要在大段文本中加或者改写内容,我们必须把这段Clob删除后再重新添加。非常费时间。
(3)在现在的新系统架构中我们一般都用了分层的思想,比如说,数据库层,应用层,文件存储层,浏览器等。而在考虑到系统整体的性能的时候,往往跨越层级的访问是最大因素。而我们如果吧这些图片,大段文本都放在数据库的话,那么最简单的访问都必须要横跨多层。

解决方法:

一般我们为了处理这种Blob,Clob的数据,我们常用的解决方法是,我们可以建立一个文件服务器,然后把相应的图片,或者文本(放在某文件中),然后把这些图片和文本放在文件服务器上,而在数据库中保存的是这些图片资源或者文本资源的路径。
这样的好处有3点。
(1)读写速度提升。因为 文件服务器上的内容是扁平内容,而数据库系统中的内容是结构化的数据,所以从读写效率上来说,对于文件服务器内容的读写速度要远远高于对于数据库系统的读写速度.
(2)可维护性提升。如果我们把这些图片,文本放在文件服务器上,那么我们非常容易维护。因为我们如果要替换图片,或者替换文本,我们只要把新的文件取代原来位置的图片和文本就可以了,而数据库中因为保存的是引用的图片或者文本的位置,所以我们根本不需要去改那个路径。这也是一种弱耦合关系。而前面也说了,如果这些内容以LOB的形式放在数据库中的话,就算要做非常微量的改动,也必须删除原来对象然后创建新对象,这个是非常浪费时间的。
(3)无需海量的备份开销。因为数据库备份很麻烦,不仅仅要备份内容,还要备份索引等。而文件系统的备份则无疑是最简单的硬盘内容的复制,所以开销很小。

目录
相关文章
|
SQL Oracle 关系型数据库
java往oracle存clob类型的值时,字符长度过长怎么办?
java往oracle存clob类型的值时,字符长度过长怎么办?
790 1
|
分布式计算 Oracle 关系型数据库
实时计算 Flink版产品使用问题之获取Oracle的数据时无法获取clob类型的数据,该怎么办
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
Oracle 关系型数据库 MySQL
实时计算 Flink版产品使用问题之在online模式下增量抓取Oracle数据时,在archive_log切换时,出现数据丢失的情况,是什么原因
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
Oracle 关系型数据库 数据库
Flink Sink to Oracle 存在字段CLOB类型,如何处理错误”ORA-01461: 仅能绑定要插入LONG的LONG值“
做Flink CDC同步数据过程中,目标是Oracle数据库,其中某个字段较大被设置为CLOB类型,其中会遇到异常,”ORA-01461: 仅能绑定要插入LONG的LONG值“
|
Oracle 关系型数据库 数据库
oracle varchar2 只能存数字,不能存英文和字母
oracle varchar2 只能存数字,不能存英文和字母
139 0
|
Oracle 关系型数据库 数据处理
通过Oracle识别字符串中的中文or字母or数字来介绍全角半角转换函数(to_multi_byte/to_single_byte)在varchar/clob中的使用案例
在日常处理数据的过程中,大家肯定会遇到很多奇奇怪怪的字符,然后还要对这些字符处理,比如***你有个需求:识别字符串中的中文或是识别字母或是识别数字,甚至都识别出来然后剔除or保留某些字符汉字或数字***。 你去百度了一下相关问题,然后得到的结果大都是用正则 &#39;\4E00&#39; and &#39;\9FA5&#39;来识别中文范围用a-zA-z或0-9或[:digit:][:alpha:]来识别字母或数字。但是如果你的字符串中包含全角字符,那这样是识别不全的!!!那怎么做才能够正确的识别中文、字母、数字呢???那就要考虑先做全半
通过Oracle识别字符串中的中文or字母or数字来介绍全角半角转换函数(to_multi_byte/to_single_byte)在varchar/clob中的使用案例
|
Oracle 关系型数据库 Java
java处理oracle的Clob字段类型的坑和2种解决办法
java处理oracle的Clob字段类型的坑和2种解决办法
|
存储 Oracle 关系型数据库
oracle blob和 varchar2互相转换,mybatis 读取blob乱码等相关问题总结
oracle blob和 varchar2互相转换,mybatis 读取blob乱码等相关问题总结
968 0
oracle blob和 varchar2互相转换,mybatis 读取blob乱码等相关问题总结
|
SQL Oracle 关系型数据库
Oracle中如何备份控制文件?备份控制文件的方式有哪几种?
Oracle中如何备份控制文件?备份控制文件的方式有哪几种?
503 0