Oracle修改pctfree、initrans参数

方式一：直接修改

--A Combination of increasing both INITRANS and PCTFREE
--1) Set INITRANS to 50 and pct_free to 20
alter table hmq_logs INITRANS 50;
--2) Re-organize the table using move (alter table <table_name> move;)
alter table hmq_logs move;
--3) Then rebuild all the indexes of the table as below
alter index HMQ_LOGS_N1  rebuild online INITRANS 50;
alter index HMQ_LOGS_N2  rebuild online INITRANS 50;
alter index HMQ_LOGS_N3  rebuild online INITRANS 50;

方式二：在线修改

--1.新建分区字段并赋值，修改字段属性为非空
ALTER TABLE cux.cux_fnd_jms_in_lob_itf ADD creation_date DATE DEFAULT SYSDATE;


UPDATE cux.cux_fnd_jms_in_lob_itf jil
   SET jil.creation_date = (SELECT jii.creation_date
                              FROM cux.cux_fnd_jms_inbound_itf jii
                             WHERE jii.jms_inbound_id = jil.jms_inbound_id);
                             
ALTER TABLE cux.cux_fnd_jms_in_lob_itf MODIFY creation_date NOT NULL;


--2.创建在线重定义模板临时表，给予分区并创建局部索引
CREATE TABLE cux.cux_fnd_jms_in_lob_itf_tmp
(
  lob_itf_id     NUMBER not null,
  jms_inbound_id NUMBER not null,
  msg_text_clob  CLOB,
  creation_date  DATE not null
)
tablespace CUX_TS_TX_DATA
  pctfree 10
  initrans 10
  maxtrans 255
  storage
  (
    initial 16K
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  )
PARTITION BY RANGE(creation_date) INTERVAL(NUMTODSINTERVAL(7, 'day'))
(
PARTITION BF201810 VALUES LESS THAN (TO_DATE('2018-10-01', 'YYYY-MM-DD'))
);


create index CUX.CUX_FND_JMS_IN_LOB_ITF_TMP_N1 on CUX.CUX_FND_JMS_IN_LOB_ITF_TMP (JMS_INBOUND_ID)
  tablespace CUX_TS_TX_IDX LOCAL;
  
create unique index CUX.CUX_FND_JMS_IN_LOB_ITF_TMP_U1 on CUX.CUX_FND_JMS_IN_LOB_ITF_TMP (LOB_ITF_ID,CREATION_DATE)
  tablespace CUX_TS_TX_IDX LOCAL;
  
  
--3.开始执行在线重定义
BEGIN
  dbms_redefinition.start_redef_table('CUX','CUX_FND_JMS_IN_LOB_ITF','CUX_FND_JMS_IN_LOB_ITF_TMP');
END;


--4.同步表上索引等依赖对象
DECLARE
  l_error_cnt NUMBER:= 0;
BEGIN
  dbms_redefinition.copy_table_dependents('CUX', 'CUX_FND_JMS_IN_LOB_ITF','CUX_FND_JMS_IN_LOB_ITF_TMP',0, TRUE, FALSE, TRUE, FALSE,l_error_cnt);
  dbms_output.put_line('errors := ' || to_char(l_error_cnt));
END;


--5.同步此期间变更数据
BEGIN
  dbms_redefinition.sync_interim_table('CUX', 'CUX_FND_JMS_IN_LOB_ITF','CUX_FND_JMS_IN_LOB_ITF_TMP');
END;


--6.结束在线重定义
BEGIN
  dbms_redefinition.finish_redef_table('CUX', 'CUX_FND_JMS_IN_LOB_ITF', 'CUX_FND_JMS_IN_LOB_ITF_TMP');
END;


--7.删除表
DROP TABLE cux.cux_fnd_jms_in_lob_itf_tmp;


--8.恢复索引名称
ALTER INDEX CUX.CUX_FND_JMS_IN_LOB_ITF_TMP_N1 RENAME TO CUX_FND_JMS_IN_LOB_ITF_N1;
ALTER INDEX CUX.CUX_FND_JMS_IN_LOB_ITF_TMP_U1 RENAME TO CUX_FND_JMS_IN_LOB_ITF_U1;


--9.更新版本化视图
BEGIN
  ad_zd_table.upgrade('CUX','CUX_FND_JMS_IN_LOB_ITF');
END;