资讯专栏INFORMATION COLUMN

上云采购季！| 爆款云服务器低至4.6元/月，首单享0.9折

免费试用
首页/文章专栏/ORACLE普通表在线转分区表

ORACLE普通表在线转分区表

IT那活儿 / 3034人阅读
ORACLE普通表在线转分区表
点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！ 

检查权限

如果使用普通用户进行操作，需要确认当前使用用户是否具有以下权限：
grant EXECUTE_CATALOG_ROLE,CREATE ANY TABLE,ALTER ANY TABLE,DROP ANY TABLE,LOCK ANY TABLE,SELECT ANY TABLE to &用户名;
或者直接使用DBA用户。

检测当前表是否可进行重定义

测试要进行分区的表是否可以进行分区转换，使用以下语句进行测试，选项dbms_redefinition.cons_use_pk表示使用主键作为转换依据（默认值）。
begin
 dbms_redefinition.can_redef_table(uname        => &user,
                                   tname        => &table,
                                  options_flag => dbms_redefinition.CONS_USE_PK);
end;
/

如果当前表没有主键，需要将选项设置为CONS_USE_rowid（表示使用rowid作为分区依据。）

开启并行

确定可以进行分区表转换后，可以考虑增加并行度加快表转换的速度。
alter session force parallel dml parallel &并行度;
alter session force parallel query parallel &并行度;


按照预定的分区格式，创建临时表

举例：
CREATE TABLE scott.emp_tmp
  ( 
EMPNO NUMBER(4) not null,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) 
) 
 partition BY range(EMPNO) 
 ( 
 partition tab_part_2020 VALUES less than  (7700), 
 partition tab_part_2021 VALUES less than  (7900),
  partition tab_part_2022 values less than(maxvalue)
 );


开始进行数据重定义

开始进行表转换，转换过程中会占用较大空间，需要提前确认磁盘空间是否有足够余量。
col_mapping => null如果对全部列进行重定义，此处设置为null，如果只对部分列进行重定义，需要将源表和目标表的对应字段一一列出。
options_flag => dbms_redefinition.cons_use_pk该选项此处表示使用主键作为分区依据。
BEGIN
 DBMS_REDEFINITION.START_REDEF_TABLE(uname  => &用户名,
                                     orig_table  => &源表名,
                                     int_table   => &临时表名,
   col_mapping => null,
                                    options_flag => dbms_redefinition.cons_use_pk
                                     );
END;
/
如果源表中的数据有超出分区表范围的值，会报类似以下错误。
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-14400: inserted partition key does not map to any partition
处理方法：清理无效数据或设置默认分区表。然后将在线重定义的操作进行回退。
注明：此步骤在原表上创建一个物化视图日志，并用临时表命名创建一个物化视图，其基表是原表。如果转换过程失败需要删除相关物化视图和物化视图日志然后重新转化。
select * from dba_mview_logs where log_owner=SCOTT;
select MVIEW_NAME,CONTAINER_NAME,QUERY from dba_mviews where owner=SCOTT;
drop materialized view log on scott.emp1;
drop materiallized view scott.emp_tmp;


开始索引约束重定义

如果以上操作正常执行完成，则进入下一步操作，迁移索引、约束、触发器。
DECLARE
error_count PLS_INTEGER :=0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname  => &用户名,
                                       orig_table       => &源表名,
                                       int_table        => &临时表名,
                                       copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
                                       copy_triggers    => TRUE,
                                       copy_constraints => TRUE,
                                       copy_privileges  => TRUE,
                                       ignore_errors    => true,
                                       num_errors     => error_count,
                                       copy_statistics  => FALSE );
DBMS_OUTPUT.PUT_LINE(errors :=  || TO_CHAR(error_count));
END;
参数简介：
  • copy_indexes=> DBMS_REDEFINITION.CONS_ORIG_PARAMS使用源端的索引参数复制索引；设为0表示不复制索引。
  • copy_triggers  => TRUE、copy_constraints => TRUE、copy_privileges  => TRUE，复制触发器、约束、权限等表的属性；false表示不复制
注：上述过程可能出现的错误如下所示：
ERROR at line 1:
ORA-01442: column to be modified to NOT NULL is already NOT NULL
出现上述错误，是因为创建临时表时设置了多个not null 的约束。
查看重定义过程中是否出错。
select * from DBA_REDEFINITION_ERRORS;
如果not null约束未迁移成功，采用以下步骤进行处理。
--找出NOT VALIDATED的约束：
SELECT CONSTRAINT_NAME, VALIDATED FROM USER_CONSTRAINTS WHERE TABLE_NAME = &表名;
--上面查出的约束：
ALTER TABLE &表名 ENABLE VALIDATE CONSTRAINT &约束名称;
--验证约束是否生效：
SELECT CONSTRAINT_NAME, VALIDATED FROM USER_CONSTRAINTS WHERE TABLE_NAME = &表名;

同步新产生的数据

同步从数据重定义转换开始至当前产生的新数据，这个操作的目的是为了缩短执行完成过程中锁定表的时间。
BEGIN
 DBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname      => &用户名,
                                      orig_table => &源表名,
                                      int_table  => &临时表名
                                      );
END;

完成在线重定义

执行DBMS_REDEFINITION.FINISH_REDEF_TABLE过程完成表的重定义。这个过程中，原始表会被独占模式锁定一小段时间，具体锁定时间和表的数据量有关。
BEGIN
 DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname      => &用户名,
                                      orig_table => &源表名,
                                      int_table  => &临时表名
                                      );
END;

收集统计信息

EXEC DBMS_STATS.gather_table_stats(&用户名, &表名, cascade => TRUE,no_invalidate => FALSE);

no_invalidate参数决定了新统计量生成之后，如何处理此时已经生成的执行计划，no_invalidate取值true，新的执行计划不会立即生效；no_invalidate取值false，新的执行计划会立即生效，取值auto_invalidate则数据库自行判断何时生效（默认值）。

收尾工作

删除临时表
确认最后一个分区数据是否有变化，如果数据量未发生变化，查看alert.log是否报错，如果日志中出现“Some indexes or index [sub]partitions of table ORABPEL.AUDIT_TRAIL have been marked unusable”的错误，错误处理方法如下：
SELECT ALTER INDEX  || INDEX_OWNER || . || INDEX_NAME ||
      REBUILD PARTITION  || PARTITION_NAME ||  NOLOGGING online;
 FROM DBA_IND_PARTITIONS
WHERE INDEX_OWNER NOT IN (SYS, SYSTEM, PUBLIC)
  AND STATUS = UNUSABLE
UNION ALL
SELECT alter index  || OWNER || . || A.INDEX_NAME ||
       REBUILD online nologging;
 FROM DBA_INDEXES A
WHERE OWNER NOT IN (SYS, SYSTEM, PUBLIC)
  AND STATUS = UNUSABLE;
确认在线重定义是否引起存储过程、触发器等对象失效。
select * from dba_objects where status<>VALID and owner=&用户名;
确认无误后，删除临时表。
truncate table &临时表名;
drop table &临时表名;
根据实际情况判断是否创建本地分区索引。

本文作者：张连坤（上海新炬王翦团队）

本文来源：“IT那活儿”公众号

私有云 海外云主机 oracle分区表 Oracle表 oracle 表空间 oracle创建表

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129302.html

相关文章

  • ORACLE 历史数据管理策略--数据清理

    摘要：背景由于性能数据每天导入量，数据库表空间每天增长很快，且不需要太长的保存周期，为避免爆表，因此需要定制定期清理计划。数据的清理可以有多种方案，根据场景的不同可以分为离线，在线。 背景 由于性能数据每天导入量，数据库表空间每天增长很快，且不需要太长的保存周期，为避免爆表，因此需要定制定期清理计划。数据的清理可以有多种方案，根据场景的不同可以分为离线，在线。后续又在可以细分。这里仅考虑在线...

    willin 评论0 收藏0

  • 数据库收集 - 收藏集 - 掘金

    摘要：前言在使用加载数据数据库常见的优化操作后端掘金一索引将放第一位，不用说，这种优化方式我们一直都在悄悄使用，那便是主键索引。 Redis 内存压缩实战 - 后端 - 掘金在讨论Redis内存压缩的时候，我们需要了解一下几个Redis的相关知识。 压缩列表 ziplist Redis的ziplist是用一段连续的内存来存储列表数据的一个数据结构，它的结构示例如下图 zlbytes: 记录整...

    Little_XM 评论0 收藏0

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师
我要私信

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<