• Oracle-大表改造分区表实施步骤

    前言：

    对于业务交易繁忙的数据库，在运行了一定时间后往往会产生一些数据量较大的表，特别是对于每天新增数据较多的日志表或者流水表，单表数据量通常会达到几十G，甚至是上百G。大表对于日常的运维非常的不方便，特别是在表数据的清理、迁移，查询性能会随着数据量的增大而受到影响，因此，通常对于大表我们需要进行优化拆分，在Oracle数据库，比较常见的大表优化拆分是将大表改造为分区表，这种方式最主要的好处就是对于应用透明，应用层面的改动很少。

    本文接下来的内容将主要讲述Oracle大表改造分区表的步骤，主要有通过expdp/impdp进行非在线的改造方式以及通过dbms_redefinition进行在线的改造方式

     

    expdp/impdp方式

    首先获取表的表结构，索引DDL信息

    1. ---获取源表的表结构
    2. set longc 9999
    3. set long 99999
    4. set linesize 400
    5. set pagesize 400
    6. select dbms_metadata.get_ddl('TABLE','TAB','TEST') from dual;
    7. ------
    8.   CREATE TABLE "TEST"."TAB"
    9.    (  "BEGNDT" DATE NOT NULL ENABLE,
    10.   "OVERDT" DATE NOT NULL ENABLE,
    11.   "CORPCODE" VARCHAR2(32) NOT NULL ENABLE,
    12.   "PRODCD" VARCHAR2(32) NOT NULL ENABLE,
    13.   "SRCSYS" VARCHAR2(64) NOT NULL ENABLE,
    14.   "ACCT_SERNO" VARCHAR2(64) NOT NULL ENABLE,
    15.   "ACCT_NUM" VARCHAR2(64),
    16.   "TACCTNO" VARCHAR2(64),
    17.   "CUSTNO" VARCHAR2(64)
    18.    ) SEGMENT CREATION IMMEDIATE
    19.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
    20.  NOCOMPRESS LOGGING
    21.   STORAGE(INITIAL 41943040 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    22.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    23.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    24.   TABLESPACE "TEST"
    25. ---获取源表索引结构
    26. select owner,index_name,index_type
    27. from dba_indexes
    28. where table_owner='TEST' and table_name='TAB';
    29. OWNER             INDEX_NAME          INDEX_TYPE
    30. ------------------------------ ------------------------------ ---------------------------
    31. TEST             IDX_TAB01        NORMAL
    32. ​
    33. set longc 9999
    34. set long 99999
    35. set linesize 400
    36. set pagesize 400
    37. select dbms_metadata.get_ddl('INDEX','IDX_TAB01','TEST') from dual;
    38. ​
    39.   CREATE INDEX "TEST"."IDX_TAB01" ON "TEST"."TAB" ("ACCT_NUM")
    40.   PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
    41.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    42.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    43.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    44.   TABLESPACE "TEST"
    45. ;

    获取表的注释、授权，结构、约束、依赖对象，这些需要保证改造前后一致

    1. ---查看表信息
    2. set linesize 400
    3. select owner,table_name,NUM_ROWS,tablespace_name,degree,partitioned,temporary,row_movement,iot_type
    4. from dba_tableswhere owner='TEST' and table_name='TAB'
    5. ​
    6. OWNER             TABLE_NAME      NUM_ROWS TABLESPACE_NAME    DEGREE           PAR T ROW_MOVE IOT_TYPE
    7. ------------------------------ ------------------------------ ---------- ------------------------------ ---------------------------------------- --- - -------- ------------
    8. TEST             TAB      45583680 TEST          1         NO  N DISABLED
    9. ​
    10. ---获取表,列注释
    11. select 'comment on table '||owner||'.'||table_name||' is '''||comments||''';'
    12. from dba_tab_comments
    13. where owner='TEST' and table_name='TAB';
    14. ​
    15. select 'comment on column '||owner||'.'table_name||'.'||column_name||' is '''||comments||''';'
    16. from dba_col_comments
    17. where owner='TEST' and table_name='TAB';
    18. ​
    19. ---获取表的授权
    20. ---查看表的授权
    21. select grantor,grantee,PRIVILEGE,owner,table_name
    22. from dba_tab_privs
    23. where table_name ='TAB' and owner='TEST';
    24. ​
    25. select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';'  
    26. from dba_tab_privs
    27. where table_name ='TAB' and owner='TEST';
    28. ----查询当前对象状态数量(改造完之后状态要一致)
    29. select owner,status,count(*)
    30. from dba_objects
    31. WHERE OWNER='TEST'
    32. group by owner,status
    33. order by 1,2;
    34. ​
    35. ----查询表被依赖的对象(改造完之后需要重新编译)
    36. select a.owner,a.name,a.type,a.REFERENCED_OWNER,a.REFERENCED_NAME,b.status  from dba_DEPENDENCIES a,dba_objects b 
    37. where a.REFERENCED_OWNER='TEST' and a.REFERENCED_NAME='TAB' and a.NAME=b.object_name;
    38. ----查询表的约束(确保改造数量，状态前后一致)
    39. select owner,table_name,constraint_name,constraint_Type,status from dba_constraints  where owner='TEST' and table_name='TAB'

    确认至少有一倍剩余空间可以存放

    1. ----确认表，索引所在的表空间有一倍空间进行存放
    2. with temp_seg as (
    3. select owner,segment_name
    4. from dba_lobs
    5. where owner='TEST' and table_name='TAB'
    6. union
    7. select owner,segment_name
    8. from dba_segments
    9. where owner='TEST' and  segment_name='TAB'
    10. union
    11. select owner,index_name
    12. from dba_indexes
    13. where table_owner='TEST' and table_name='TAB'
    14. )
    15. select a.tablespace_name,sum(a.bytes)/1024/1024
    16. from dba_segments a,temp_seg b
    17. where a.owner =b.owner and a.segment_name=b.segment_name
    18. group by a.tablespace_name;

    查看源表的最大，最小日期数据

    1. alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
    2. select min(BEGNDT),max(BEGNDT) from TEST.TAB;
    3. ​
    4. MIN(BEGNDT)      MAX(BEGNDT)
    5. ------------------- -------------------
    6. 2021-05-28 00:00:00 2021-09-06 00:00:00

    创建新的分区

    1. ----创建新的分区表tab_partion
    2. ----注意初始化分区要尽可能的小于当前最早的数据日期(因为如果后面插入的数据小于初始化分区，不会新建对应区间的新分区，会直接存放在初始化的分区里面，自动新建分区只作用于大于初始化分区的数据)
    3. ----采用自动分区，按天，对于新的数据，如果所在的区间分区(当前为天)不存在,则会自动新建分区(
    4. ----如插入2021-01-02 07:00:00则会新建分区less than 2021-01-03,如果后面继续插入2020-12-24 07:00:00则会新建分区less than 2021-12-25，按天去匹配创建，不管数据插入的日期前后，只要大于初始化分区即可自动创建)
    5. ----注意，约束，索引名字要修改，避免重复，后面再rename就行
    6. create table TEST.TAB_PART
    7.    ("BEGNDT" DATE NOT NULL ENABLE,
    8.   "OVERDT" DATE NOT NULL ENABLE,
    9.   "CORPCODE" VARCHAR2(32) NOT NULL ENABLE,
    10.   "PRODCD" VARCHAR2(32) NOT NULL ENABLE,
    11.   "SRCSYS" VARCHAR2(64) NOT NULL ENABLE,
    12.   "ACCT_SERNO" VARCHAR2(64) NOT NULL ENABLE,
    13.   "ACCT_NUM" VARCHAR2(64),
    14.   "TACCTNO" VARCHAR2(64),
    15.   "CUSTNO" VARCHAR2(64)
    16.    ) SEGMENT CREATION IMMEDIATE
    17.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
    18.  NOCOMPRESS LOGGING
    19.   STORAGE(INITIAL 41943040 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    20.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    21.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    22.   TABLESPACE "TEST" 
    23. partition by range(BEGNDT)
    24. interval (numtodsinterval(1,'day')) store in (TEST)
    25. (partition p_2000 values less than (to_date('2021-01-01','yyyy-mm-dd')) tablespace TEST);

    确认应用没有访问，设置表为只读模式。

    alter table TEST.TAB read only;

    expdp导出表数据

    1. create directory part_expdp as '/home/oracle/part_backup';
    2. expdp \" /  as sysdba \" directory=part_expdp dumpfile=TEST_TAB_20210907_%u.dmp tables=TEST.TAB logfile=TEST_TAB_20210907.log CONTENT=all exclude=statistics cluster=n parallel=8

    impdp导入数据

    impdp \" /  as sysdba \" directory=part_expdp dumpfile=TEST_TAB_20210907_%u.dmp remap_table=TAB:TAB_PART logfile=imp_TEST_TAB_20210907.log CONTENT=data_only  cluster=n parallel=8

    对新分区表进行统计信息收集

    1. ---统计信息收集
    2. begin
    3.   DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST',tabname=>'TAB_PART',ESTIMATE_PERCENT=>20,method_opt=>'for all columns size 1',cascade=>true,force=>true,degree=>8,no_invalidate=>FALSE);
    4. end;
    5. /

    数据抽查检验

    1. ---查看分区数量,以及数据分布
    2. set linesize 300
    3. set pagesize 300
    4. col table_owner for a10
    5. col table_name for a30
    6. WITH PART_TEMP AS 
    7. (SELECT TABLE_OWNER,TABLE_NAME,PARTITION_POSITION
    8. FROM DBA_TAB_PARTITIONS
    9. where table_owner='TEST' and table_name='TAB_PART')
    10. select d.table_owner,d.table_name,d.partition_name,d.high_value,d.NUM_ROWS
    11. FROM DBA_TAB_PARTITIONS d,part_temp t
    12. where d.table_owner=t.table_owner and d.table_name=t.table_name and d.PARTITION_POSITION=t.PARTITION_POSITION 
    13. order by 1,2,3;
    14. ----验证对比数据
    15. select count(*) from TEST.TAB;
    16. select count(*) from TEST.TAB_PART;
    17. ----分区数据抽验
    18. alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
    19. select min(BEGNDT),max(BEGNDT) from TEST.TAB_PART partition(SYS_P18782);
    20. ----hash全数据校验(对于关键表可以进行,消耗资源较多)
    21. ----批量生成column name的拼接字句
    22. ----注意语句的列输出格式要一样，最好在一个窗口执行
    23. SET LINESIZE 400
    24. SET PAGESIZE 400
    25. SET NUMWIDTH 25
    26. with temp1 as 
    27. (select owner,table_name,listagg(column_name||'|'||'|'''||'|'''||'|'||'|') within group( order by COLUMN_ID) concat
    28. from dba_tab_columns
    29. where owner='TEST' and table_name in ('TAB_PART')
    30. group by owner,table_name)
    31. select owner,table_name,substr(concat,0,length(concat)-7) from temp1;
    32. ---将表名，以及column name的拼接字句带入
    33. select /*+ parallel(a,4)*/ 'TEST'||'.'||'TAB_PART' TABLE_NAME, 
    34.      count(1) COUNT,
    35.     nvl(sum(dbms_utility.get_hash_value(BEGNDT||'|'||OVERDT||'|'||CORPCODE||'|'||PRODCD||'|'||SRCSYS||'|'||ACCT_SERNO||'|'||ACCT_NUM||'|'||TACCTNO||'|'||CUSTNO,0,1073741824)),0) HASH_VAL 
    36. from TEST.TAB_PART a;
    37. TABLE_NAME             COUNT      HASH_VAL
    38. ------------------------------ ------------------------- -------------------------
    39. TEST.TAB_PART       45605625   24485050418392042
    40. ​
    41. select /*+ parallel(a,4)*/ 'TEST'||'.'||'TAB' TABLE_NAME, 
    42.      count(1) COUNT,
    43.     nvl(sum(dbms_utility.get_hash_value(BEGNDT||'|'||OVERDT||'|'||CORPCODE||'|'||PRODCD||'|'||SRCSYS||'|'||ACCT_SERNO||'|'||ACCT_NUM||'|'||TACCTNO||'|'||CUSTNO,0,1073741824)),0) HASH_VAL 
    44. from TEST.TAB a;
    45. ​
    46. TABLE_NAME             COUNT      HASH_VAL
    47. ------------------------------ ------------------------- -------------------------
    48. TEST.TAB        45605625   24485050418392042

    对表进行rename替换,将表正式替换为分区表

    1. alter table TEST.TAB rename to TAB_OLD20210907;
    2. alter table TEST.TAB_PART rename to TAB;

    对索引，约束进行重建

    1. ----获取旧表索引结构
    2. select owner,index_name
    3. from dba_indexes
    4. where table_owner='TEST' and table_name='TAB_OLD20210907';
    5. set longc 9999
    6. set long 99999
    7. set linesize 400
    8. set pagesize 400
    9. select dbms_metadata.get_ddl('INDEX','IDX_TAB01','TEST') from dual;
    10. ---
    11.   CREATE INDEX "TEST"."IDX_TAB01" ON "TEST"."TAB_OLD20210907" ("ACCT_NUM")
    12.   PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
    13.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    14.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    15.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    16.   TABLESPACE "TEST"
    17. ----rename索引
    18. select 'alter index '||owner||'.'||index_name||' rename to '||index_name||'_old20210907;'
    19. from dba_indexes
    20. where owner='TEST' and table_name='TAB_OLD20210907';
    21. ----rename约束名称
    22. ----排除了系统自建的约束修改SYS_C
    23. select ' alter table '||owner||'.'||table_name||' rename constraint '||constraint_name||' to '||constraint_name||'_old20210907'
    24. from dba_constraints
    25. where owner='TEST' and table_name='TAB_OLD20210907' and constraint_name not like 'SYS_C%';
    26. ----新建新表索引
    27. ----对于包含分区建的索引,创建loal本地分区，对于不包含分区键的索引，创建全局分区
    28. create index TEST.index_name on TEST.tab_partion(column_name) tablespace users parallel 4;
    29. or
    30. create index TEST.index_name on TEST.tab_partion(column_name) local tablespace users parallel 4;
    31. ​
    32.   CREATE INDEX "TEST"."IDX_TAB01" ON "TEST"."TAB" ("ACCT_NUM")
    33.   PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
    34.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    35.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    36.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    37.   TABLESPACE "TEST" PARALLEL 8;
    38. ----关闭索引并行
    39. select 'alter index '||owner||'.'||index_name||' noparallel;'
    40. from dba_indexes
    41. where degree not in (1,0) and owner='TEST';

    查看依赖对象有效性，状态是否一致

    1. ----查看依赖对象有效性
    2. select a.owner,a.name,a.type,a.REFERENCED_OWNER,a.REFERENCED_NAME,b.status  from dba_DEPENDENCIES a,dba_objects b 
    3. where a.REFERENCED_OWNER='TEST' and a.REFERENCED_NAME='TAB' and a.NAME=b.object_name;
    4. ----手动进行编译
    5. alter procedure  <schema name>.<package_name> compile;
    6. alter package <schema name>.<package_name> compile;
    7. alter package <schema name>.<package_name> compile body;
    8. alter view <schema name>.<view_name> compile;
    9. alter trigger <schema).<trigger_name> compile;
    10. ----查询当前对象状态数量(改造完之后状态要一致)
    11. select owner,status,count(*)
    12. from dba_objects
    13. WHERE OWNER='TEST'
    14. group by owner,status
    15. order by 1,2;

    检查完成之后，大表改造分区完成

     

    dbms_redefinition在线重定义方式

    创建中间表

    1. CREATE TABLE par_table (
    2. id NUMBER(10),
    3. create_date DATE,
    4. name VARCHAR2(100)
    5. )
    6. PARTITION BY RANGE (create_date)
    7. (PARTITION unpar_table_2005 VALUES LESS THAN (TO_DATE('01/01/2005', 'DD/MM/YYYY')),
    8. PARTITION unpar_table_2006 VALUES LESS THAN (TO_DATE('01/01/2006', 'DD/MM/YYYY')),
    9. PARTITION unpar_table_2007 VALUES LESS THAN (MAXVALUE));

    检查源表(非分区unpar_tab)是否具备迁移条件

    1. EXEC Dbms_Redefinition.can_redef_table(USER, 'unpar_table');
    2. ---检查源表是否具备迁移条件
    3. ---注意表需要有主键，否则会报以下错误
    4. ORA-12089: cannot online redefine table "TEST"."UNPAR_TABLE" with no primary key

    如果检查没有报错，则可以进行以下数据迁移

    1. ---此操作期间对于要迁移的数据，如果行上锁还未释放，操作会产生TX 6锁
    2. BEGIN
    3. DBMS_REDEFINITION.start_redef_table(
    4. uname => USER, 
    5. orig_table => 'unpar_table',
    6. int_table => 'par_table');
    7. END;
    8. /

    手动同步数据到新表，在创建索引之前，避免数据差异过大，即使finish_redef_table也会再一次同步数据

    1. BEGIN
    2. dbms_redefinition.sync_interim_table(
    3. uname => USER, 
    4. orig_table => 'unpar_table',
    5. int_table => 'par_table');
    6. END;
    7. /
    8. 注意:在操作期间，目标表无法进行，也不该进行人为的dml操作，会报以下错误
    9. update par_table set id='x';
    10. update par_table set id='x'
    11.                         *
    12. ERROR at line 1:
    13. ORA-01732: data manipulation operation not legal on this view

    可以通过以下视图查看在线重定义表的状态

    1. CDB_REDEFINITION_ERRORS
    2. CDB_REDEFINITION_OBJECTS
    3. CDB_REDEFINITION_STATUS
    4. DBA_REDEFINITION_ERRORS
    5. DBA_REDEFINITION_OBJECTS
    6. DBA_REDEFINITION_STATUS
    7. ​

    主要关注当前的操作，以及操作状态，如果当前没有正在进行的redefiniton操作，则视图都为空

    55a29026dfd1d9e7d71d7cbd07e4a0c4.png

    ef6aed5b5d18583ead5b95c0fa37c853.png

    在进行切换之前，对中间表创建索引以及约束​

    1. ALTER TABLE par_table ADD (
    2. CONSTRAINT unpar_table_pk2 PRIMARY KEY (id
    3. )
    4. )
    5. ;
    6. CREATE INDEX create_date_ind2 ON par_table(create_date);

    在进行切换之前，进行表统计信息收集

    EXEC DBMS_STATS.gather_table_stats(USER, 'par_table', cascade => TRUE);

    完成重定义操作

    1. ---此操作期间需要短暂获取TM 6锁，以完成表的切换
    2. SQL> BEGIN
    3. dbms_redefinition.finish_redef_table(
    4. uname => USER,
    5. orig_table => 'unpar_table',
    6. int_table => 'par_table');
    7. END;
    8. /
    9. At this point the interim table (along with its index) has become the "real" table and their names have been switched in the name dictionary. 
    10. SQL> select table_name from user_tab_partitions where table_name in ('UNPAR_TABLE','PAR_TABLE')
    11. ;
    12. TABLE_NAME
    13. ------------------------------
    14. UNPAR_TABLE
    15. UNPAR_TABLE
    16. UNPAR_TABLE
    17. SQL> select table_name,index_name from user_indexes where table_name in ('UNPAR_TABLE','PAR_TABLE');
    18. TABLE_NAME INDEX_NAME
    19. ------------------------------ ------------------------------
    20. PAR_TABLE CREATE_DATE_IND
    21. PAR_TABLE UNPAR_TABLE_PK
    22. UNPAR_TABLE UNPAR_TABLE_PK2
    23. UNPAR_TABLE CREATE_DATE_IND2

    检查完成之后，大表改造分区完成

    ​

     

     

     

     

  • 相关阅读:
    2022短视频神器历时6个月辛苦全力打造的软件短视频运用工具
    基于遗传算法的南昌周边城市旅游规划研究（Python实现）
    windows 各种inject/hook学习材料（自用）
    计算机毕业设计Java大学生就业招聘系统(源码+系统+mysql数据库+lw文档)
    javabasic
    C语言连接MySQL并执行SQL语句（hello world）
    Linux 权限相关例题练习
    odoo javascript参考（八）
    Conda的自动化魔法：一探auto_activate_base的奥秘
    已解决（Python最新xlrd库读取xlsx报错）SyntaxError: invalid syntax
  • 原文地址：https://blog.csdn.net/sinat_36757755/article/details/126821281