利用ORACLE DBMS_REDEFINITION包進行普通表到分割槽表的線上轉換

yantaicuiwei發表於2010-12-29

原文網址 : http://blog.itpub.net/21601207/viewspace-682823/

在生產系統中，由於設計上的原因，一些資料量很大的表需要轉換成分割槽表，以提高效能。而oracle自帶的DBMS_REDEFINITION包能完成普通表到分割槽表的線上重定義。與其它手工轉換的方式相比，該方法能很容易保證資料的一致性，並最大限度的減少對生產的影響。其內部實現機制是首先建立物化檢視記錄原表資料的變更，再將原表資料載入到目標表中，載入完成後，可以靈活的手工進行變更資料的同步，最後透過呼叫FINISH_REDEF_TABLE過程，對中間表和原表進行名字互換，完成轉換。需要注意的是，如果表較大，比如超過10G的表，在轉換的過程中可能會引起頻繁的redo的切換，所以在轉換前需要將中間表改為nologging,轉換過程中會佔用較大的undo表空間，如10G的表，至少需要30G的undo表空間，可以臨時建立一個較大的undo表空間設定為系統預設undo表空間，轉換完成後，將undo表空間切換回原undo表空間，待臨時undo表空間的事務完全提交後，將其刪除。
以下以某生產系統中的轉換過程為例說明，該系統中有三張普通表需要轉換為分割槽表，分割槽型別為range分割槽，一個月一個分割槽，除了主鍵以外，其它索引都建立為local.方便後續分割槽表的資料清理維護。其中一張表需要增加一列時間型別的欄位，並將varchar2型別的時間內容資料更新至該欄位。該欄位同時為分割槽關鍵字。以下是具體的實施步驟：
1.檢查執行DBMS_REDEFINITION包的使用者的許可權。
要執行DBMS_REDEFINITION，需要相關許可權，可用下面命令完成授權：
GRANT Create any table   TO MM_PPMS;
GRANT Alter any table    TO MM_PPMS;
GRANT Drop any table     TO MM_PPMS;
GRANT Lock any table     TO MM_PPMS;
GRANT Select any table   TO MM_PPMS;
2. 建立表空間indx_mm_ppms，用來分離分割槽表的索引和資料
如果表預期比較大的話，最好將每張表放到單獨的表空間，方便管理。
CREATE TABLESPACE INDX_MM_PPMS DATAFILE
  '/dev/vg_cora1/rdata_1g_258' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora1/rdata_1g_261' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora1/rdata_1g_264' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora1/rdata_1g_267' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora1/rdata_1g_270' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora1/rdata_1g_273' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora1/rdata_1g_276' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora1/rdata_1g_279' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora1/rdata_1g_282' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora1/rdata_1g_285' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora1/rdata_1g_288' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora1/rdata_1g_291' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora1/rdata_1g_297' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora1/rdata_1g_300' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora2/rdata_1g_232' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora2/rdata_1g_235' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora2/rdata_1g_244' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora2/rdata_1g_247' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora2/rdata_1g_250' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora2/rdata_1g_253' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora2/rdata_1g_256' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora2/rdata_1g_259' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora2/rdata_1g_262' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora2/rdata_1g_265' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora2/rdata_1g_268' SIZE 1023M AUTOEXTEND OFF,
  '/dev/vg_cora2/rdata_1g_271' SIZE 1023M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 2048K
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;
3.檢查資料庫該使用者的無效物件情況並作好記錄。
可以寫一個簡單的shell指令碼來完成這部分的檢查，指令碼名為：check_invalid_objects.sh，內容如下：
sqlplus "/as sysdba"<
set heading off
set echo off
set feedback off
col owner for a12
set lines 400
select 'Current check database is :'||sys_context('userenv','db_name') from dual;
select 'Invalid object number is :'||count() from dba_objects where status='INVALID' and wner='MM_PPMS';
select 'Invalid objects are  :'||owner,object_type,object_name from dba_objects where status='INVALID' and wner='MM_PPMS';
exit;
EOF
4. 建立中間分割槽表及相關索引
該分割槽表在轉換為最後一步會rename成生產表的名字，下面分別建立三張分割槽表及索引，索引參照原表的命名，以_P結尾，由於表比較大，所以需要修改原表的相關引數：CDR_INT，ONDEMAND_ORDER_HIST_INT，ONDEMAND_PUSH_INT。

-- Create table  CDR_INT
create table CDR_INT
(
  CDRSEQ       VARCHAR2(12) not null,
  FEEUSERID    VARCHAR2(15) not null,
  DESTUSERID   VARCHAR2(15) not null,
  OSPCODE      VARCHAR2(6) not null,
  SERVICECODE  VARCHAR2(10) not null,
  CHARGETYPE   VARCHAR2(2) not null,
  FEE          NUMBER(8) not null,
  CDRTYPE      VARCHAR2(2) not null,
  ACCESSMODEID VARCHAR2(2) not null,
  CDRTIME      VARCHAR2(14) not null,
  CONTENTCODE  VARCHAR2(10),
  CPCODE       VARCHAR2(6),
  CONTENTTIME  VARCHAR2(14),
  STATUS       NUMBER(2) default 0 not null,
  CHARGEFLAG   NUMBER(1),
  ORDERNUMBER  VARCHAR2(20),
  PAYWAY       NUMBER(4),
  PAYACCOUNTS  VARCHAR2(40),
  CREATETIME   DATE
)
tablespace DATA_PPMS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 4096K
    next 2048K
    minextents 1
    maxextents unlimited
    pctincrease 0
  )
  PARTITION BY RANGE(CREATETIME)
(
PARTITION  T_CDR_P200911   VALUES LESS THAN (to_date('20091201','yyyymmdd')),
PARTITION  T_CDR_P200912   VALUES LESS THAN (to_date('20100101','yyyymmdd')),
PARTITION  T_CDR_P201001   VALUES LESS THAN (to_date('20100201','yyyymmdd')),
PARTITION  T_CDR_P201002   VALUES LESS THAN (to_date('20100301','yyyymmdd')),
PARTITION  T_CDR_P201003   VALUES LESS THAN (to_date('20100401','yyyymmdd')),
PARTITION  T_CDR_P201004   VALUES LESS THAN (to_date('20100501','yyyymmdd')),
PARTITION  T_CDR_P201005   VALUES LESS THAN (to_date('20100601','yyyymmdd')),
PARTITION  T_CDR_P201006   VALUES LESS THAN (to_date('20100701','yyyymmdd')),
PARTITION  T_CDR_P201007   VALUES LESS THAN (to_date('20100801','yyyymmdd')),
PARTITION  T_CDR_P201008   VALUES LESS THAN (to_date('20100901','yyyymmdd')),
PARTITION  T_CDR_P201009   VALUES LESS THAN (to_date('20101001','yyyymmdd')),
PARTITION  T_CDR_P201010   VALUES LESS THAN (to_date('20101101','yyyymmdd')),
PARTITION  T_CDR_P201011   VALUES LESS THAN (to_date('20101201','yyyymmdd')),
PARTITION  T_CDR_P201012   VALUES LESS THAN (to_date('20110101','yyyymmdd')),
PARTITION  T_CDR_P201101   VALUES LESS THAN (to_date('20110201','yyyymmdd')),
PARTITION  T_CDR_P201102   VALUES LESS THAN (to_date('20110301','yyyymmdd')),
PARTITION  T_CDR_P201103   VALUES LESS THAN (to_date('20110401','yyyymmdd')),
PARTITION  T_CDR_P201104   VALUES LESS THAN (to_date('20110501','yyyymmdd')),
PARTITION  T_CDR_P201105   VALUES LESS THAN (to_date('20110601','yyyymmdd')),
PARTITION  T_CDR_P201106   VALUES LESS THAN (to_date('20110701','yyyymmdd')),
PARTITION  T_CDR_P201107   VALUES LESS THAN (to_date('20110801','yyyymmdd')),
PARTITION  T_CDR_P201108   VALUES LESS THAN (to_date('20110901','yyyymmdd')),
PARTITION  T_CDR_P201109   VALUES LESS THAN (to_date('20111001','yyyymmdd')),
PARTITION  T_CDR_P201110   VALUES LESS THAN (to_date('20111101','yyyymmdd')),
PARTITION  T_CDR_P201111   VALUES LESS THAN (to_date('20111201','yyyymmdd')),
PARTITION  T_CDR_P201112   VALUES LESS THAN (to_date('20120101','yyyymmdd')),
PARTITION  T_CDR_PMAX  VALUES LESS THAN (MAXVALUE)
);

-- Create/Recreate primary, unique and foreign key constraints
alter table CDR_INT
  add constraint PK_CDR_P primary key (CDRSEQ)
  using index
  global tablespace INDX_MM_PPMS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 4096K
    next 2048K
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Create/Recreate indexes
create index IDX_CDR_CDRSEQ_P on CDR_INT (TO_NUMBER(CDRSEQ))
  local tablespace INDX_MM_PPMS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 4096K
    next 2048K
    minextents 1
    maxextents unlimited
    pctincrease 0
  );

create index IDX_CDR_CREATETIME_P on CDR_INT (CREATETIME)
  local tablespace INDX_MM_PPMS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 4096K
    next 2048K
    minextents 1
    maxextents unlimited
    pctincrease 0
  );

-- Create table
create table ONDEMAND_ORDER_HIST_INT
(
  HISTID         NUMBER(10) not null,
  ORDERNUMBER    VARCHAR2(20) not null,
  USERID         VARCHAR2(15) not null,
  PRODUCTID      VARCHAR2(12) not null,
  SPCODE         VARCHAR2(6) not null,
  SERVICECODE    VARCHAR2(10) not null,
  ACTIONTYPE     NUMBER(2),
  ACTIONDATE     DATE not null,
  STARTDATE      DATE not null,
  LUPDDATE       DATE,
  CHARGESTATUS   CHAR(1) not null,
  ORDERSTATUS    CHAR(1),
  CHARGEUSERID   VARCHAR2(15) not null,
  ORDERPLACE     VARCHAR2(2),
  PACKAGEID      VARCHAR2(12),
  ACCESSMODEID   VARCHAR2(2),
  PAYWAY         NUMBER(1) not null,
  PRODUCTCODE    VARCHAR2(39),
  CONTENTID      VARCHAR2(12),
  ORDERTYPE      NUMBER(1),
  EXPIRETIME     DATE,
  VALIDTIMES     NUMBER(3),
  PRICE          NUMBER(8),
  CPCODE         VARCHAR2(10),
  DOWNLOADFLAG   NUMBER(8) default 0,
  SALESCHANNELID VARCHAR2(64),
  ORDERFLAG      NUMBER(1),
  PAYACCOUNTS    VARCHAR2(40)
)
tablespace DATA_PPMS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 4096K
    next 2048K
    minextents 1
    maxextents unlimited
    pctincrease 0
  )
  PARTITION BY RANGE(ACTIONDATE)
(
PARTITION  T_ORDER_HIST_P200911    VALUES LESS THAN (to_date('20091201','yyyymmdd')),
PARTITION  T_ORDER_HIST_P200912    VALUES LESS THAN (to_date('20100101','yyyymmdd')),
PARTITION  T_ORDER_HIST_P201001    VALUES LESS THAN (to_date('20100201','yyyymmdd')),
PARTITION  T_ORDER_HIST_P201002    VALUES LESS THAN (to_date('20100301','yyyymmdd')),
PARTITION  T_ORDER_HIST_P201003    VALUES LESS THAN (to_date('20100401','yyyymmdd')),
PARTITION  T_ORDER_HIST_P201004    VALUES LESS THAN (to_date('20100501','yyyymmdd')),
PARTITION  T_ORDER_HIST_P201005    VALUES LESS THAN (to_date('20100601','yyyymmdd')),
PARTITION  T_ORDER_HIST_P201006    VALUES LESS THAN (to_date('20100701','yyyymmdd')),
PARTITION  T_ORDER_HIST_P201007    VALUES LESS THAN (to_date('20100801','yyyymmdd')),
PARTITION  T_ORDER_HIST_P201008    VALUES LESS THAN (to_date('20100901','yyyymmdd')),
PARTITION  T_ORDER_HIST_P201009    VALUES LESS THAN (to_date('20101001','yyyymmdd')),
PARTITION  T_ORDER_HIST_P201010    VALUES LESS THAN (to_date('20101101','yyyymmdd')),
PARTITION  T_ORDER_HIST_P201011    VALUES LESS THAN (to_date('20101201','yyyymmdd')),
PARTITION  T_ORDER_HIST_P201012    VALUES LESS THAN (to_date('20110101','yyyymmdd')),
PARTITION  T_ORDER_HIST_P201101    VALUES LESS THAN (to_date('20110201','yyyymmdd')),
PARTITION  T_ORDER_HIST_P201102    VALUES LESS THAN (to_date('20110301','yyyymmdd')),
PARTITION  T_ORDER_HIST_P201103    VALUES LESS THAN (to_date('20110401','yyyymmdd')),
PARTITION  T_ORDER_HIST_P201104    VALUES LESS THAN (to_date('20110501','yyyymmdd')),
PARTITION  T_ORDER_HIST_P201105    VALUES LESS THAN (to_date('20110601','yyyymmdd')),
PARTITION  T_ORDER_HIST_P201106    VALUES LESS THAN (to_date('20110701','yyyymmdd')),
PARTITION  T_ORDER_HIST_P201107    VALUES LESS THAN (to_date('20110801','yyyymmdd')),
PARTITION  T_ORDER_HIST_P201108    VALUES LESS THAN (to_date('20110901','yyyymmdd')),
PARTITION  T_ORDER_HIST_P201109    VALUES LESS THAN (to_date('20111001','yyyymmdd')),
PARTITION  T_ORDER_HIST_P201110    VALUES LESS THAN (to_date('20111101','yyyymmdd')),
PARTITION  T_ORDER_HIST_P201111    VALUES LESS THAN (to_date('20111201','yyyymmdd')),
PARTITION  T_ORDER_HIST_P201112    VALUES LESS THAN (to_date('20120101','yyyymmdd')),
PARTITION  T_ORDER_HIST_PMAX   VALUES LESS THAN (MAXVALUE)
);

-- Create/Recreate primary, unique and foreign key constraints
alter table ONDEMAND_ORDER_HIST_INT
  add constraint PK_ONDEMAND_ORDER_HIST_P primary key (HISTID)
  using index
  global tablespace INDX_MM_PPMS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 4096K
    next 2048K
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Create/Recreate indexes
create index IDX_ON_ORDER_HIST_USERID_P on ONDEMAND_ORDER_HIST_INT (USERID,PRODUCTID)
  local tablespace INDX_MM_PPMS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 4096K
    next 2048K
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
create index IDX_OOH_CHARGEUSERID_P on ONDEMAND_ORDER_HIST_INT (CHARGEUSERID)
local tablespace INDX_MM_PPMS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 4096K
    next 2048K
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
create index IDX_OOH_ORDERNUMBER_P on ONDEMAND_ORDER_HIST_INT (ORDERNUMBER)
local tablespace INDX_MM_PPMS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 4096K
    next 2048K
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
create index IDX_OOH_ACTIONDATE_P on ONDEMAND_ORDER_HIST_INT (ACTIONDATE)
local tablespace INDX_MM_PPMS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 4096K
    next 2048K
    minextents 1
    maxextents unlimited
    pctincrease 0
  );

-- Create table
create table ONDEMAND_PUSH_INT
(
  ORDERNUMBER   VARCHAR2(20) not null,
  USERID        VARCHAR2(15) not null,
  PRODUCTID     VARCHAR2(12) not null,
  ENDTIME       DATE,
  UA            VARCHAR2(200),
  STARTTIME     DATE not null,
  PARAMS        VARCHAR2(512),
  PUSHID        VARCHAR2(30) not null,
  REPORTSTATUS  VARCHAR2(4),
  ERRORDESC     VARCHAR2(100),
  CONTENTID     VARCHAR2(12),
  FEEMSISDN     VARCHAR2(15),
  DOWNLOADTIMES NUMBER(8),
  CPCODE        VARCHAR2(10),
  VALIDRPTTIMES NUMBER(8) default 1,
  DOWNCHANNELID VARCHAR2(2) default '10'
)
tablespace DATA_PPMS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 4096K
    next 2048K
    minextents 1
    maxextents unlimited
    pctincrease 0
  )
  PARTITION BY RANGE(STARTTIME)
(
PARTITION  T_OND_PUSH_P200908  VALUES LESS THAN (to_date('20090901','yyyymmdd')),
PARTITION  T_OND_PUSH_P200909  VALUES LESS THAN (to_date('20091001','yyyymmdd')),
PARTITION  T_OND_PUSH_P200910  VALUES LESS THAN (to_date('20091101','yyyymmdd')),
PARTITION  T_OND_PUSH_P200911  VALUES LESS THAN (to_date('20091201','yyyymmdd')),
PARTITION  T_OND_PUSH_P200912  VALUES LESS THAN (to_date('20100101','yyyymmdd')),
PARTITION  T_OND_PUSH_P201001  VALUES LESS THAN (to_date('20100201','yyyymmdd')),
PARTITION  T_OND_PUSH_P201002  VALUES LESS THAN (to_date('20100301','yyyymmdd')),
PARTITION  T_OND_PUSH_P201003  VALUES LESS THAN (to_date('20100401','yyyymmdd')),
PARTITION  T_OND_PUSH_P201004  VALUES LESS THAN (to_date('20100501','yyyymmdd')),
PARTITION  T_OND_PUSH_P201005  VALUES LESS THAN (to_date('20100601','yyyymmdd')),
PARTITION  T_OND_PUSH_P201006  VALUES LESS THAN (to_date('20100701','yyyymmdd')),
PARTITION  T_OND_PUSH_P201007  VALUES LESS THAN (to_date('20100801','yyyymmdd')),
PARTITION  T_OND_PUSH_P201008  VALUES LESS THAN (to_date('20100901','yyyymmdd')),
PARTITION  T_OND_PUSH_P201009  VALUES LESS THAN (to_date('20101001','yyyymmdd')),
PARTITION  T_OND_PUSH_P201010  VALUES LESS THAN (to_date('20101101','yyyymmdd')),
PARTITION  T_OND_PUSH_P201011  VALUES LESS THAN (to_date('20101201','yyyymmdd')),
PARTITION  T_OND_PUSH_P201012  VALUES LESS THAN (to_date('20110101','yyyymmdd')),
PARTITION  T_OND_PUSH_P201101  VALUES LESS THAN (to_date('20110201','yyyymmdd')),
PARTITION  T_OND_PUSH_P201102  VALUES LESS THAN (to_date('20110301','yyyymmdd')),
PARTITION  T_OND_PUSH_P201103  VALUES LESS THAN (to_date('20110401','yyyymmdd')),
PARTITION  T_OND_PUSH_P201104  VALUES LESS THAN (to_date('20110501','yyyymmdd')),
PARTITION  T_OND_PUSH_P201105  VALUES LESS THAN (to_date('20110601','yyyymmdd')),
PARTITION  T_OND_PUSH_P201106  VALUES LESS THAN (to_date('20110701','yyyymmdd')),
PARTITION  T_OND_PUSH_P201107  VALUES LESS THAN (to_date('20110801','yyyymmdd')),
PARTITION  T_OND_PUSH_P201108  VALUES LESS THAN (to_date('20110901','yyyymmdd')),
PARTITION  T_OND_PUSH_P201109  VALUES LESS THAN (to_date('20111001','yyyymmdd')),
PARTITION  T_OND_PUSH_P201110  VALUES LESS THAN (to_date('20111101','yyyymmdd')),
PARTITION  T_OND_PUSH_P201111  VALUES LESS THAN (to_date('20111201','yyyymmdd')),
PARTITION  T_OND_PUSH_P201112  VALUES LESS THAN (to_date('20120101','yyyymmdd')),
PARTITION  T_OND_PUSH_PMAX VALUES LESS THAN (MAXVALUE)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table ONDEMAND_PUSH_INT
  add constraint PK_ONDEMAND_PUSH_P primary key (PUSHID)
   using index
global tablespace INDX_MM_PPMS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 4096K
    next 2048K
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Create/Recreate indexes
create index INX_OP_ORDERNUMBER_P on ONDEMAND_PUSH_INT (ORDERNUMBER)
  local tablespace INDX_MM_PPMS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 4096K
    next 2048K
    minextents 1
    maxextents unlimited
    pctincrease 0
  );

create index INX_OP_STARTTIME_P on ONDEMAND_PUSH_INT (STARTTIME)
  local tablespace INDX_MM_PPMS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 4096K
    next 2048K
    minextents 1
    maxextents unlimited
    pctincrease 0
  );

5.將分割槽表的表及索引改為nologging，減少redo的切換頻率
這一步也可以在建立索引和表的時候直接指定。
alter index INX_OP_ORDERNUMBER_P           nologging;
alter index INX_OP_STARTTIME_P             nologging;
alter index PK_ONDEMAND_PUSH_P             nologging;
alter index ONDEMAND_ORDER_HIST_INT        nologging;
alter index IDX_ON_ORDER_HIST_USERID_P     nologging;
alter index IDX_OOH_CHARGEUSERID_P         nologging;
alter index IDX_OOH_ORDERNUMBER_P          nologging;
alter index IDX_OOH_ACTIONDATE_P           nologging;

alter table mm_ppms.ONDEMAND_PUSH_INT nologging;
alter table mm_mmpms.ONDEMAND_ORDER_HIST_INT nologging;
alter table mm_mmps.CDR_INT nologging;
6.由指令碼call.sh呼叫start_redef_table.sql開始轉換分割槽。
由於分割槽轉換的時間可能比較長，所以最好由shell指令碼呼叫sql指令碼，透過nohup的方式後臺執行。在分割槽轉換的過程中，要密切監視undo表空間的使用情況。
start_redef_table.sql指令碼的內容如下：

EXEC DBMS_REDEFINITION.START_REDEF_TABLE('MM_PPMS','CDR', 'CDR_INT');
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('MM_PPMS','ONDEMAND_ORDER_HIST', 'ONDEMAND_ORDER_HIST_INT');
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('MM_PPMS','ONDEMAND_PUSH', 'ONDEMAND_PUSH_INT');

檢視過程PROCEDURE START_REDEF_TABLE的引數情況如下，我們只需要指定引數UNAME，ORIG_TABLE，INT_TABLE即可。
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
UNAME                          VARCHAR2                IN
ORIG_TABLE                     VARCHAR2                IN
INT_TABLE                      VARCHAR2                IN
COL_MAPPING                    VARCHAR2                IN     DEFAULT
OPTIONS_FLAG                   BINARY_INTEGER          IN     DEFAULT

一個簡單的call.sh的指令碼如下：
#!/bin/ksh
# $Header: call.sh 6-aug-2010.11:56 zhao,lehuan $
# call.sh
# Copyright (c) Aspire. All rights reserved.
#    NAME
#      call.sh -
#    DESCRIPTION
#
#    MODIFIED   (MM/DD/YY)

#set -x
if [ $# -lt 1 ]
then
  echo "Usage:$0 "
  exit 1
fi
SCRIPT_NAME=$1
DB_ID=mmcore
.  ~oracle/.profile
##############################################
#Set work dir
WORKPATH=/oracle/utils/partition
##############################################
#Set DATE format
NOWDATE=`date '+%Y%m%d'`
NOWTIME=`date '+%Y-%m-%d-%H:%M'`
################################################
#Set mail recipients and subject
MAILLIST="zhaolehuan@aspire-tech.com"
MAILSUBJECT="call_script_$SCRIPT_NAME_$NOWTIME"
###################################

##############################################
#Load passwd file
#/oracle/utils/orapwdtab format:
#$DB_ID:$USERNAME:$PASSWD
PASSWDFILE=${WORKPATH}/.orapwdtab
if [ ! -f ${PASSWDFILE} ]
   then
   cp /oracle/utils/orapwdtab ${WORKPATH}/.orapwdtab
   chmod 600 ${WORKPATH}/.orapwdtab
fi
if [ ! -f ${PASSWDFILE} ]
   then
     echo "Can not find password file ${PASSWDFILE}"
     exit
fi
USERNAME=mm_ppms
PASSWD=`grep -v "^[#]" ${PASSWDFILE}|grep -i ${DB_ID}|grep -i ${USERNAME}| cut -d: -f3`
cd ${WORKPATH}
###########################################################################

sqlplus -s ${USERNAME}/${PASSWD}@${DB_ID} <
set echo off
set feedback on
set echo on
set trimspool on
set heading on
set timing on
set time on
spool ${WORKPATH}/${SCRIPT_NAME}.file
/ add sql script/
@ SCRIPT_NAME
spool off
exit;
eof
cat ${WORKPATH}/${SCRIPT_NAME}.file
mailx -m -s ${MAILSUBJECT} -r oracle@`hostname`.com $MAILLIST
exit
7.透過呼叫指令碼sync_interim_table.sql來進行同步資料
如果開始轉換和結束轉換之間的時間比較長，要麼，在這期間可以透過呼叫上述指令碼來進行，SYNC_INTERIM_TABLE這個程式的引數呼叫與start的呼叫是一樣的。
sync_interim_table.sql指令碼的內容如下：
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('MM_PPMS','CDR', 'CDR_INT');
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('MM_PPMS','ONDEMAND_ORDER_HIST', 'ONDEMAND_ORDER_HIST_INT');
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('MM_PPMS','ONDEMAND_PUSH', 'ONDEMAND_PUSH_INT');
8. 由指令碼call.sh呼叫finish_redef_table.sql結束分割槽轉換。
同步完成後，便可以結束分割槽轉換，一般來說，這個過程很快就結束，轉換結束後，原普通表的表名事實上對應新建立的分割槽表，分割槽表的表名對應原普通表。
finish_redef_table.sql指令碼的內容如下，呼叫引數參見以上說明。

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('MM_PPMS','CDR', 'CDR_INT');
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('MM_PPMS','ONDEMAND_ORDER_HIST', 'ONDEMAND_ORDER_HIST_INT');
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('MM_PPMS','ONDEMAND_PUSH', 'ONDEMAND_PUSH_INT');
9.對轉換完畢的分割槽表進行表分析，收集統計資訊
由於是生產系統，可以採用較低的取樣比迅速完成分析，然後再在系統非高峰期的時候採用恰當的取樣比對上述表進行一次表分析。
分析指令碼內容如下：
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'MM_PPMS',TABNAME=>'CDR',estimate_percent =>5,CASCADE=>TRUE,DEGREE=>8);
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'MM_PPMS',TABNAME=>'ONDEMAND_ORDER_HIST',estimate_percent =>5,CASCADE=>TRUE,DEGREE=>8);
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'MM_PPMS',TABNAME=>'ONDEMAND_PUSH',estimate_percent =>5,CASCADE=>TRUE,DEGREE=>8);

10. 將三張表的表及索引改為logging的狀態
logging;
alter index PK_ONDEMAND_PUSH_P             logging;
alter index ONDEMAND_ORDER_HIST_INT        logging;
alter index IDX_ON_ORDER_HIST_USERID_P     logging;
alter index IDX_OOH_CHARGEUSERID_P         logging;
alter index IDX_OOH_ORDERNUMBER_P          logging;
alter index IDX_OOH_ACTIONDATE_P           logging;
alter table ONDEMAND_PUSH    logging;
alter table ONDEMAND_ORDER_HIST logging;
alter table CDR logging;

11.檢查資料庫無效物件，並進行編譯，參考如下的指令碼進行編譯：
sqlplus /nolog <
conn / as sysdba
---recompile all
--@$ORACLE_HOME/rdbms/admin/utlrp.sql;
---compile mocsacct
EXEC DBMS_UTILITY.COMPILE_SCHEMA('MM_PPMS',TRUE);
exit;
EOF
12.後續臨時表清理
資料驗證無誤後，將三張普通表（ONDEMAND_ORDER_HIST_INT，CDR_INT，ONDEMAND_PUSH_INT
）從資料庫中刪除。
以上的操作過程中，開始轉換分割槽三張表一共花了2個小時左右，這三張表的資料量總數在20G左右，為了減少對undo表空間的爭用，三張表依次進行轉換，同步的過程很快，在10分鐘內就同步完成，結束轉換的操作不到兩分鐘就完成了。而整個操作對生產系統基本無影響，從業務上感覺不到整個轉換的過程。
附：CDR表新加列的操作
在CDR表的轉換之前，由於分割槽鍵createtime原表上是沒有的，需要建立新的一列，並將cdrtime列的內容轉換成日期格式後更新到新的列中，由於cdr表的資料量在3000多萬條，所以只能將此更新分割為較小的事務進行操作，該更新透過編寫一個procedure來完成，整個操作步驟如下所示：
整個操作還是由call.sh呼叫add_newcolumn_on_cdr.sql來完成，add_newcolumn_on_cdr.sql的內容如下：

alter table mm_ppms.cdr add(createtime date);
alter table mm_ppms.cdr modify(createtime date default sysdate);

exec zlh_update_cdr(100000);

ALTER TABLE MM_PPMS.CDR MODIFY CREATETIME  NOT NULL;

上述指令碼中的過程exec zlh_update_cdr的功能為更新cdrtime的內容到createtime列，引數為提交的頻率，如上即為10W行記錄一次提交。程式碼如下：

create or replace procedure mm_ppms.zlh_update_cdr( interval number)
is
v_maxvalue number;
v_minvalue number;
v_range    number;
v_interval number;
v_date     varchar2(30);
v_looptime number;
v_l_cdrseq number;
v_h_cdrseq number;
errid number;
/
create table mm_ppms.zlh_cdr_log(
label varchar2(30),
description varchar2(500),
logtime date
)
/
cursor cur_cdr is  select min(TO_NUMBER(CDRSEQ)) minv,
       max(TO_NUMBER(CDRSEQ)) maxv,
       max(TO_NUMBER(CDRSEQ))- min(TO_NUMBER(CDRSEQ)) rangev
       from mm_ppms.cdr;
begin
    /
    execute immediate 'alter session set  nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
      /
select to_char(sysdate,'yyyymmddhh24miss') into v_date from dual;
v_interval:= interval;
for rec in cur_cdr LOOP
    begin
    v_minvalue:=rec.minv;
    v_maxvalue:=rec.maxv;
    v_range:=rec.rangev;
  end;
end loop;

v_looptime:=trunc(v_range/interval,0)+1;

/begin update table cdr */
  INSERT INTO zlh_cdr_log
    VALUES(v_date,'begin update cdr !',sysdate);
    v_l_cdrseq:=v_minvalue;
    v_h_cdrseq:=v_minvalue+v_interval;
    for i in 1 .. v_looptime loop

    update mm_ppms.cdr set createtime=to_date(cdrtime,'yyyymmddhh24miss')
    where TO_NUMBER(CDRSEQ) between v_l_cdrseq and v_h_cdrseq;
    commit;
    insert into zlh_cdr_log values(v_date,'update '||v_interval||' rows of table cdr:looptime:'||i||',current cdrseq range is:'||v_h_cdrseq||',mixvalue:'||v_minvalue||'. maxvalue:'||v_maxvalue||'.',sysdate);
    commit;
    v_l_cdrseq:=v_h_cdrseq+1;
    if v_l_cdrseq+v_interval
    v_h_cdrseq:=v_l_cdrseq+v_interval;
    else
     v_h_cdrseq:=v_maxvalue;
  end if;
    end loop;
  EXCEPTION
  WHEN OTHERS THEN
   errid:=SQLCODE;
INSERT INTO zlh_cdr_log
    VALUES(v_date,'error:ERRID ='||TO_CHAR(ERRID),sysdate);
        commit;
  INSERT INTO zlh_cdr_log
    VALUES(v_date,'update cdr completed!',sysdate);
    commit;
end;
/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21601207/viewspace-682823/，如需轉載，請註明出處，否則將追究法律責任。

相關文章

LightDB 22.4 新特性之相容Oracle線上轉換普通表為分割槽表
2022-12-27
Oracle
PG的非分割槽表線上轉分割槽表
2022-07-12
oracle 普通表-分割槽表改造流程
2023-12-29
Oracle
oracle 線上重新定義，普通表改變分割槽表，分割槽表可以更改型別、分割槽欄位等
2020-08-19
Oracle型別
線上重定義與普通表改為分割槽表
2018-10-14
Oracle 12.2之後ALTER TABLE .. MODIFY轉換非分割槽表為分割槽表
2020-04-14
Oracle
【ASK_ORACLE】使用insert語句將普通錶轉換成分割槽表
2022-11-30
Oracle
線上重定義方式將普通表修改為分割槽表
2021-01-12
指令碼：線上重定義，從普通表到分割槽表，redefinition_table.sh
2018-03-09
指令碼
非分割槽錶轉換成分割槽表
2022-07-09
Oracle 12C新特性-線上把非分割槽錶轉為分割槽表
2020-02-12
Oracle
線上重定義的操作步驟__普通錶轉換為分割槽表的實驗案例
2018-06-14
MySQL線上轉分割槽表（以及TiDB）
2022-07-11
MySqlTiDB
oracle分割槽表和分割槽表exchange
2024-03-15
Oracle
oracle分割槽表和非分割槽表exchange
2024-03-15
Oracle
mysql 進行表分割槽
2019-01-04
MySql
oracle 分割槽表move和包含分割槽表的lob move
2019-03-11
Oracle
[oracle] expdp 匯出分割槽表的分割槽
2021-01-28
Oracle
非分割槽錶轉換成分割槽表以及注意事項
2022-07-05
oracle將表配置為分割槽表
2023-10-24
Oracle
ORACLE分割槽表梳理系列
2019-04-17
Oracle
Oracle分割槽表基礎運維-04列表分割槽
2020-05-15
Oracle運維
Oracle分割槽表基礎運維-07增加分割槽(3列表分割槽)
2020-05-18
Oracle運維
Oracle分割槽表基礎運維-06分割槽表索引
2020-05-18
Oracle運維索引
對oracle分割槽表的理解整理
2018-05-07
Oracle
Oracle分割槽表基礎運維-01分割槽表分類
2020-05-14
Oracle運維
Oracle分割槽表基礎運維-09刪除分割槽
2020-05-30
Oracle運維
Oracle分割槽表基礎運維-05組合分割槽
2020-05-15
Oracle運維
Oracle分割槽表基礎運維-02範圍分割槽
2020-05-14
Oracle運維
Oracle分割槽表基礎運維-03HASH分割槽
2020-05-14
Oracle運維
Oracle SQL調優之分割槽表
2019-05-18
OracleSQL
Oracle分割槽表基礎運維-07增加分割槽(2 HASH分割槽)
2020-05-18
Oracle運維
對Oracle分割槽表進行表空間遷移並處理ORA-14511問題
2018-11-21
Oracle
移動分割槽表和分割槽索引的表空間
2018-05-23
索引
oracle分割槽表的分類及測試
2019-01-22
Oracle
Oracle分割槽表基礎運維-07增加分割槽(1範圍分割槽)
2020-05-18
Oracle運維
ORACLE刪除-表分割槽和資料
2020-05-06
Oracle
【MYSQL】分割槽表
2021-09-09
MySql
ORACLE線上切換undo表空間
2020-10-26
Oracle