Home » SQL & PL/SQL » SQL & PL/SQL » Rebuild table contain partition (Oracle database 10g)
Rebuild table contain partition [message #652434] Wed, 08 June 2016 20:35 Go to next message
dophuong_cs
Messages: 92
Registered: May 2011
Location: Viet Nam
Member

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

Re: Rebuild table contain partition [message #652436 is a reply to message #652434] Wed, 08 June 2016 22:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
You might look into using dbms_redefinition:

http://docs.oracle.com/database/121/ARPLS/d_redefi.htm#ARPLS042
Re: Rebuild table contain partition [message #652441 is a reply to message #652436] Thu, 09 June 2016 00:09 Go to previous messageGo to next message
dophuong_cs
Messages: 92
Registered: May 2011
Location: Viet Nam
Member

Thank you for your support.
However, i don't know how to do.
Please give me 1 example for more understand.
Re: Rebuild table contain partition [message #652443 is a reply to message #652441] Thu, 09 June 2016 01:01 Go to previous message
Michel Cadot
Messages: 67920
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Here

Previous Topic: How To Update Encrypted Password for all user id's using Update Query
Next Topic: Oracle Special character while dbms_output
Goto Forum:
  


Current Time: Tue Jul 27 20:22:40 CDT 2021