Rebuild table contain partition [message #652434] |
Wed, 08 June 2016 20:35 |
|
Dear all,
This is Phuong.
Now, i face 1 problem that. I have 1 table partition like below:
-- Create table
create table BODY_NO_MASTER
(
code_control VARCHAR2(1) default 'A' not null,
date_entry DATE not null,
user_entry VARCHAR2(7) not null,
date_update DATE,
user_update VARCHAR2(7),
body_no VARCHAR2(12) not null,
code_amend VARCHAR2(12),
weight NUMBER(5),
layer_pcb VARCHAR2(10),
line_no VARCHAR2(8) not null,
pallet_date VARCHAR2(10) not null,
merchandise VARCHAR2(12) not null,
code_layer_pallet VARCHAR2(5),
pallet_no VARCHAR2(3) not null,
quarter_id VARCHAR2(2) not null,
pallet_qt VARCHAR2(5),
date_input DATE,
pallet_status VARCHAR2(3),
slip_no VARCHAR2(10)
)
partition by hash (BODY_NO)
(
partition BNM1
tablespace HKPDATA1,
partition BNM2
tablespace HKPDATA1,
partition BNM3
tablespace HKPDATA1,
partition BNM4
tablespace HKPDATA1,
partition BNM5
tablespace HKPDATA1,
partition BNM6
tablespace HKPDATA2,
partition BNM7
tablespace HKPDATA2,
partition BNM8
tablespace HKPDATA2,
partition BNM9
tablespace HKPDATA2,
partition BNM10
tablespace HKPDATA2
);
-- Create/Recreate indexes
create index CODE_LAYER_PALLET_BNM_N on BODY_NO_MASTER (CODE_LAYER_PALLET)
tablespace HKPDATA1
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index LINE_NO_BNM_N on BODY_NO_MASTER (LINE_NO)
tablespace HKPDATA1
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 1M
minextents 1
maxextents unlimited
);
create index MERCHANDISE_BNM_N on BODY_NO_MASTER (MERCHANDISE)
tablespace HKPDATA1
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 1M
minextents 1
maxextents unlimited
);
create index PALLET_DATE_BNM_N on BODY_NO_MASTER (PALLET_DATE)
tablespace HKPDATA1
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 1M
minextents 1
maxextents unlimited
);
create index PALLET_NO_BNM_N on BODY_NO_MASTER (PALLET_NO)
tablespace HKPDATA1
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 1M
minextents 1
maxextents unlimited
);
create index PALLET_STATUS_BNM_N on BODY_NO_MASTER (PALLET_STATUS)
tablespace HKPDATA1
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 1M
minextents 1
maxextents unlimited
);
create index SLIP_NO_N on BODY_NO_MASTER (SLIP_NO)
tablespace HKPDATA1
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table BODY_NO_MASTER
add constraint BREGULAR_PALLET_BKL_FK primary key (BODY_NO, MERCHANDISE)
using index
tablespace HKPDATA1
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
With bold-red column is hash partition. However now i need increase length of column "Body_No" from 12 to 50 for new requirement store data. But this column is partition, can't modify and need rebuild again this table.
This table total row is very big data: 22,561,423 rows.
Please support me, how to increase column length of "Body_No" and keep 22,561,423 rows data?
[Updated on: Wed, 08 June 2016 20:50] Report message to a moderator
|
|
|
|
|
|